Text Functions,

[Lowell]

Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials>, Middle, Last, and
a sufix <Sr. Jr. IV, etc.>). What I have been trying to do is to separate
the names into two columns,

A (Separate to) B
c

A1 A Francisco Madrigal A Francisco Madrigal
A2 A J Benning A J Benning
A3 Aaron Isaac Laine Aaron Isaac Laine
A4 Abraham A. Abreu Sr Abraham A. Abreu Sr

I have tried making several formulas but so far I have not been able to get
the separated into the two columms.

 

[Peter]

You can try =Left(x,x) and =right. These split out the left hand or right
hand characters. You can try different combos of these to get the columns
split. A bit of manual tidying may be required to finish off.

 

[Lowell]

Thanks for the advise, I tried what you suggested and I cannot get it to work.

 

[Ashish Mathur]

Hi,

Try this.

If the name is entered in cell A6, then enter the following array formula
(Ctrl+Shift+Enter) in cell B6 to get the first name

MID(A6,MATCH(TRUE,EXACT(MID(A6,ROW($1:$14),1),PROPER(MID(A6,ROW($1:$14),1))),0),MATCH(TRUE,EXACT(MID(A6,ROW($2:$14),1),PROPER(MID(A6,ROW($2:$14),1))),0))

Enter the following array formula (Ctrl+Shift+Enter) in cell C6 to get the
second and last name

MID(A6,MATCH(TRUE,EXACT(MID(A6,ROW($2:$14),1),PROPER(MID(A6,ROW($2:$14),1))),0)+1,255)

Regards,

Ashish Mathur

 

[Biff]

Hi!

Those formulas are just separating everything to the left of the first space
and everything to the right of the first space. These formulas will do the
same thing:

=LEFT(A6,FIND(" ",A6)-1)

=MID(A6,FIND(" ",A6)+1,255)

There's really no good way to do this! Too many variables to consider.

Billy Bob Michael Van Patton Sr.
Bill Gates

Where does the first name end and the middle name begin in the first
example? Where does the middle name end and the last name begin? It's easy
to "see" but try writing a formula to do it and cover all the possibilities.

Biff

 

[Peter]

Happen to come across this today...
http://www.add-ins.com/name_splitter.htm
Is it what you're looking for?

 

[Peter]

Happen to come across this today...
http://www.add-ins.com/name_splitter.htm
Is it what you're looking for?

 

[Lowell]

Peter,

Thanks. This is exactly what I am looking for. What part of London are you
from? my wife and I used to live in Chelmsford.

Regards,

Lowell

 

[Peter]

The other side of town - Ealing. Glad to have helped. Nice to work with you!