Solved

# Extract First and Last name from a string in a cell.  Hope there is a function in EXCEL to do this

Posted on 2014-09-04
Medium Priority
190 Views
I have a string value in a cell from Active Directory.  In a recent question I was able to extract the full name from this string

CN=John Doe,CN=Users,DC=mydomain,DC=int     Using this function.  =MID(A1,4,FIND(",",A1)-4).   And it works great.

Now I would like to extract the first name and last name, each in its own cell.  In other words, "John" in the B1cell and "Doe" in the C1 cell.  This way I can import to a database table.  Any help is greatly appreciated.
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3

LVL 27

Accepted Solution

Glenn Ray earned 2000 total points
ID: 40304932
This will work if there are only two names (no middle names or initials):
B1: =LEFT(MID(A1,4,FIND(",",A1)-4),FIND(" ",MID(A1,4,FIND(",",A1)-4))-1)
C1: =MID(MID(A1,4,FIND(",",A1)-4),FIND(" ",MID(A1,4,FIND(",",A1)-4))+1,100)

-Glenn
0

LVL 49

Expert Comment

ID: 40304933
What if the name is "John Burdon Sanderson Haldane" (a real person)?
0

LVL 27

Expert Comment

ID: 40304937
^Yup!  Or "John Jacob Jingleheimer Schmidt"
0

LVL 27

Assisted Solution

Glenn Ray earned 2000 total points
ID: 40304946
In the case of more than one name:
C1:
=MID(SUBSTITUTE(A1," ","|",LEN(LEFT(A1,FIND(",",A1)-1))-LEN(SUBSTITUTE(LEFT(A1,FIND(",",A1)-1)," ",""))),FIND("|",SUBSTITUTE(A1," ","|",LEN(LEFT(A1,FIND(",",A1)-1))-LEN(SUBSTITUTE(LEFT(A1,FIND(",",A1)-1)," ",""))))+1,FIND(",",SUBSTITUTE(A1," ","|",LEN(LEFT(A1,FIND(",",A1)-1))-LEN(SUBSTITUTE(LEFT(A1,FIND(",",A1)-1)," ",""))))-FIND("|",SUBSTITUTE(A1," ","|",LEN(LEFT(A1,FIND(",",A1)-1))-LEN(SUBSTITUTE(LEFT(A1,FIND(",",A1)-1)," ",""))))-1)

Unfortunately, won't work for suffixes like "Sr." or "Jr." or "MD"

-Glenn
0

LVL 4

Author Comment

ID: 40304948
No middle or multiple names I am aware of.
But Good God if that happens I will kill somebody!...  Good thing I am the Active Directory person.  If it happens it would definitely be my fault. Bad enough the women who get divorced and want new email boxes AND username signons AND display name changes!
lol,,, Now you guys are going to make me check if someone did do that!  But for now, I think I can figure out that exception to the rule.  So much appreciated.  Function works great!  and the "what if" question.
0

LVL 4

Author Comment

ID: 40304950
WOW Glenn you are brilliant.  Especially on the last post.  I will use that for C1 (Just In Case).  How you know that huge function is beyond me, but wow.  You are truly the expert!!!!...   You saved me big time!
0

LVL 4

Author Closing Comment

ID: 40304952
Exceptional Expert!
0

LVL 27

Expert Comment

ID: 40304955
Thanks.  It looks gawdawful, but the trick is to break the function up into smaller parts.  I've attached an example workbook that shows that for future reference.

-Glenn
EE-ExtractName.xlsx
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month8 days, 9 hours left to enroll