Faulty memory - converting text to numbers and back

B

Blair

Hi Everyone,

Some time ago I was playing with some Goldmine data and the major
obstruction to my migration was that it stored the name data in the
format: Last Name and Full Name - no first name.

So I put the data into an Excel spreadsheet, then I did something
clever that escapes me now. I think I may have used the LEN command to
change the text strings to a numeric value, then subtract the last
name value from full name value then convert what I had left back to
text - which gave me the first name.

I can't seem to get it back into text, if I did use the LEN command at
all.

Can anyone help jog my memory?

Thanks

Blair
 
K

Klemen25

If you have full name in A1 put in B1 formula:
=LEFT(A1;FIND(" ";A1;1))

You will get the first name.

Best regards
 
P

Pete_UK

So, if you have "Smith" in A1 and "John Smith" in B1 and you want to
get "John" in C1 (all without the quotes), then you can do that by
putting this formula in C1:

=LEFT(B1,LEN(B1)-LEN(A1)-1)

Alternatively, you can put this formula in C1:

=SUBSTITUTE(B1," "&A1,"")

Copy the formula down as required.

Hope this helps.

Pete
 
Top