Excel: Separate parts of name from one cell into separate cells 2

[ech]

I have one cell with fname mi lname. I need it separated into three cells - one each for first, middle and last. I know there's a way to do it using the text functions, but I've forgotten the specifics since the names are of different lengths.

 

[bluedragon2]

Why not do a text to columns using a space as the delimiter...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Rofeu]

Hi,

you can do this with respectively LEFT, MID and RIGHT. The start position, end position and length can be determined using FIND or SEARCH:

fname: =LEFT(A1,FIND(" ",A1)-1)
mi: =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))
lname: =RIGHT(A1,FIND(" ",A1,FIND(" ",A1)))

Cheers,

Roel

 

[adalger]

If A1 is fname mi lname, you can split it into B1 as first, C1 as mi, D1 as last by using these formulas:

B1: =LEFT(A1,FIND(" ",A1,1)-1)
C1: =MID(A1,FIND(" ",A1,1)+1,1)
D1: =RIGHT(A1,LEN(A1)-(LEN(B1)+3))

If you don't want to split it into cells, you'll need to play around with the last formula. Also, if mi includes a period, you'll need to alter C1 and D1 to reflect this.


TMTOWDI - it's not just for Perl any more

 

[bluedragon2]

Roel,

What about names with four names or Jr, Sr, or III?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

Also troublesome:
-Two middle initials
-Last names with spaces (Van Wagner)

ech: It is the exceptions that you have to look out for.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[adalger]

Rofeu's and my formulas handle last names with spaces fine, but the others are indeed complications. Unfortunately, the simplest way to deal with the complication of two middle initials makes last names with spaces an issue. *sigh*

Can we depend on periods after initials? Can we depend on commas before qualifiers (e.g., Jr., III, etc.)?


TMTOWDI - it's not just for Perl any more

 

[xlhelp]

Chip Pearson's UDF takes care of that stuff.

http://www.cpearson.com/excel/FirstLast.htm

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

Thanks, help! I hadn't seen that one before. -->

Gotta love Chip Pearson....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[ech]

Thanks to everyone! Got it done... those with goodies after their last name, I just typed in.

Great forum!

 

[xlhelp]

Yup, Chip is my hero.


Member- AAAA Association Against Acronym Abusers

 

[Rofeu]

Hi,

I agree my formulae are not fullproof, but then, with names, you can not accomodate for everything.

As long as you have a delimiter between first and last name parts, you're mostly ok (in an international environment, that list of suffices can be veeeerrry long)
, but when that's missing even Chip's formulae and code won't work.

Not to mention compensating for multiple middle initials

Cheers,

Roel