Data Menu > Text to columns

[Guest]

Can you use a formula to split delimited data when number of characters in
each cell varies (rather than using Text to columns from Data menu).

For example:
Column A
1 Smith Sue
2 Wilson Jane
3 Vincent Michael

I want to split this data delimited by a space but can't use LEFT or RIGHT
functions as number of characters varies in each cell.

Please advise.

Thank you for your help!

 

[Don Guillett]

Look in the help index for FIND or SEARCH and then incorporate into a
LEFT MID or RIGHT formula
Don't know why you can't use text to columns>delimited>space??

 

[Ragdyer]

In B1 enter:
=LEFT(A1,FIND(" ",A1)-1)

In C1 enter:
=RIGHT(A1,LEN(A1)-LEN(B1)-1)

Select *both* B1 and C1, and copy down as needed.

 

[David Biddulph]

I don't see why you say you can't use LEFT or RIGHT.

What's wrong with
=LEFT(A7,FIND(" ",A7)-1) and
=RIGHT(A7,LEN(A7)-FIND(" ",A7)) ?

 

[L. Howard Kittle]

For the left name =LEFT(A2,FIND(" ",A2)-1)
For the right name =MID(A2,FIND(" ",A2)+1,10)

Does have some limitations, if there is a middle initial things will go
nuts, but for the vanilla examples you offered this should work. If the
right name is longer than 10 characters you will need to change the 10 to
whatever.

HTH
Regards,
Howard

 

[Guest]

Look in the help index for FIND or SEARCH and then incorporate into a
LEFT MID or RIGHT formula
Don't know why you can't use text to columns>delimited>space??

--
Don Guillett
SalesAid Software
(e-mail address removed)



Thanks so much for your quick response!

 

[Guest]

In B1 enter:
=LEFT(A1,FIND(" ",A1)-1)

In C1 enter:
=RIGHT(A1,LEN(A1)-LEN(B1)-1)

Select *both* B1 and C1, and copy down as needed.

 

[Guest]

For the left name =LEFT(A2,FIND(" ",A2)-1)
For the right name =MID(A2,FIND(" ",A2)+1,10)

Does have some limitations, if there is a middle initial things will go
nuts, but for the vanilla examples you offered this should work. If the
right name is longer than 10 characters you will need to change the 10 to
whatever.

HTH
Regards,
Howard




Thanks for your quick response and the extra info!

 

[Guest]

I don't see why you say you can't use LEFT or RIGHT.

What's wrong with
=LEFT(A7,FIND(" ",A7)-1) and
=RIGHT(A7,LEN(A7)-FIND(" ",A7)) ?
--
David Biddulph




Thanks for your qick response!

 

[Ragdyer]

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

- !
-

perfectly! I didn't know about the "FIND" function before. Thanks again.