2 Nesting questions

[Starchaser]

I am trying to help someone with some converting of data in Excel files
so they can be used in a db. She has long columns of names with first,
int., last, jr, and even (maiden names) - all in one cell. She has
Office 2000. I'm new to Excel, and it's been 8 years since I did any
macro writing in Lotus even, so I would appreciate any help.

When I write a formula, I find it is easier to do it in little pieces,
one formulae at a time. I want to be able to have one column with a
formulae in it and the next column or a separate window showing that
same formulae in text format. For example: =LEFT(A8,FIND(" ",A8)) in
one cell as a formula
and the same LEFT(A8,FIND(" ",A8)) in text, so that I can see both how
the actual formula works and how it is written.
I have searched here, but nothing comes up.

Second question: I have tried to paste one formula into another and
Excel 2000 isn't letting me. What am I not doing or doing wrong?

FYI,yYears ago, I used a nesting macro that allowed me to build up a
complex formula by putting each individual formulae on a separate line,
thus making sure that the individual formulae gave me the result that I
was looking for. Then the macro nested the formula into one another.
It was fast and easy to build VERY complex formula with it.
Unfortunately, it was in Lotus, and I have not been able to translate
it into Excel. Here's an example of one of those formula in one cell:
60*(@INT(Input Sheet:U12)+((@INT(((Input Sheet:U12-@INT(Input
Sheet:U12))*100))+((((Input Sheet:U12-@INT(Input
Sheet:U12))*100)-@INT(((Input Sheet:U12-@INT(Input
Sheet:U12))*100)))/0.6))/60)+(30*(@IF(Input Sheet:V12=1,12,Input
Sheet:V12-1)))) (In case you are wondering, it is part of a macro to
determine the new position of a star and in those days we had to
convert time input by the user into individual hours minutes and
seconds so they can be added and subtracted in a 24 hour clock.)

Anyway, thanks for your help

 

[darkwood]

can't you just do text to columns using space delimitation?

Data-> Text to Columns -> Delimited -> space

 

[Kleev]

I frequently copy a formula to another cell so that I can see just the
formula. What I do is (usually in the formula bar) highlight the entire
formula except for the = sign. Then I hit cntl-c to copy (and you have to do
something like either hit enter to accept the formula or escape to get out of
it before moving to the cell you want to copy it to. I frequently forget this
step and excel thinks I want to make the cell I am trying to copy to part of
the current formula.)

Then of course, select the cell you want the text part of the formula in and
paste (I just hit cntl-v)

As for pasting part of a formula into another formula, that does work, but
sometimes I have problems with that also. What I tried just now that seems
to work most consistently for me is (and I am using Excell 2000 also,) copy
the formula into the Office Clipboard (for instance by hitting cntl-c twice.)
Then go to the formula bar of the formula that you want to copy this formula
into, position your cursor, and then choose the formula from the office
clipboard (by pointing to it and left-clicking)

If anyone else knows a better or more reliable way to consistently copy a
formula into another formula, I too would appreciate knowing what it is.

 

[Pete]

I've found that method unreliable - better just to put an apostrophe in
front of the = to turn it into text. Then it can be copied into another
cell. To build up some complex and longer formulae I often join smaller
(text) ones together with a formula like =A1&A2&A3, then fix the values
of this formula, and maybe add some more to it in another cell.

Hope this helps.

Pete

 

[Kleev]

Interesting. Not what I would prefer in a perfect world, but I will keep
this in mind. Thanks.

 

[Pete]

Here's one that I built up the other day for another posting:

=IF(LEN(A1)<>7,"",IF(VALUE(MID(A1,2,1))>2,"0"&A1,IF(LEFT(A1,2)="10",LEFT(A1,2)&"0"&RIGHT(A1,5),IF(VALUE(LEFT(A1,1))>1,"0"&A1,"check"))))

You can see that there are 4 IF functions and quite a few string
functions, and this is one which I actually built by adding text
representations of the actual formulae, plus a bit of manual adjustment
to the composite strings. Works for me in cases like this.

Pete

 

[Starchaser]

Darkwood - Here is an actual entry that needs to be converted from one
cell to 6: Beaulieu Laurette A. (Bryant) "Laurie" i.e. Last name, first
name, middle name, maiden name, and nickname. However some of the
entries are men and thus have no maiden name, lots have no initial
and/or nickname, and sometimes the order of maiden and nick names are
reversed. Thus a simple transfer using delimiters is not possible, and
I have more than 1500 entries to do.

Kleev - I too have used that method of copying and Pete's method of the
apostrophe to see the formulae written out. It is time consuming, but
if the only way in Excel.... Here is an instance of swearing at MS.
In Lotus, one can have a formulae in cell A1 and "+A1" in cell B1 which
is formatted for Text. One only has to input the formulae into A1 and
it appears in Text form in B2 - a wonderful feature when doing this
kind of work as it requires no steps.

Pete - I have used the ampersand method of combining text, but it
doesn't allow for embedding one formulae INTO the arguments of another
formulae. For that I guess I will have to use the copy-paste as you
say.

One of the things that the Nesting macro I used in Lotus did was to
grab the formula in one cell and replace that address in the arguments
of another formula. Example: In cell A1 =find(), and in cell b1
=left(c3,a1) Then the nesting macro would create this =left(c3,find()).
Unfortunately, I don't know the commands and formulae terms in Excel
well enough to just substitute at the moment or I would rewrite it for
Excel. And, swears he, Excel does not translate Lotus well. If anyone
does know the commands and formulae well, I'd be glad to send you a
copy of the Lotus macro.

Anyway, you have both given me good ideas and I will plug along.
Thanks

 

[Pete]

I think we can all point to nice features we liked in other software we
have used - I used Quattro Pro for many years, and there are many
things which I wish could be replicated in Excel. However, you have to
learn the limitations of the software you are currently using and then
either live within those limitations or learn ways of pushing the
boundaries a bit further. Subscribing to news groups like this one will
help.

Pete