MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Parsing words from a string


Posted by Mike Absher on December 14, 2001 10:10 AM

Can someone give me the code to parse out:

Jack

from Jack A. Winters

where Jack A. Winters is in Cell A1


Posted by Aladin Akyurek on December 14, 2001 10:15 AM

Mike --

Your example appears to suggest that you want to extract the first word delimited by a space from a string.

You can use for that:

=LEFT(A1,SEARCH(" ",A1)-1)

Aladin

========

Posted by Mike Abshder on December 14, 2001 10:21 AM

Yes: Can I assume RIGHT in place of LEFT for the last name? And what about the middle initial?

Posted by Aladin Akyurek on December 14, 2001 10:33 AM

Yep. Here a solution using just LEFT and SUBSTUTUTE:

In B1 enter: =LEFT(A1,SEARCH(" ",A1)-1) [ the earlier formula ]
In C1 enter: =LEFT(SUBSTITUTE(A1,B1&" ",""),SEARCH(" ",SUBSTITUTE(A1,B1&" ",""))-1)
In D1 enter: =SUBSTITUTE(A1,B1&" "&C1&" ","")

Aladin

==========

Posted by IML on December 14, 2001 3:18 PM

Just because I'm board, I'd like to broaden Aladin's fine work. This all started because I thought there were some extra spaces in the name.
You could use
b1 for first
=LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-1)
c1 for middle (if any)
=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))=1,"",LEFT(SUBSTITUTE(TRIM(A1),B1&" ",""),SEARCH(" ",SUBSTITUTE(TRIM(A1),B1&" ",""))-1))
and d1 for last
=RIGHT(A1,LEN(TRIM(A1))-LEN(B1)-LEN(C1)-1)

Posted by Mark W. on December 14, 2001 3:28 PM

You could also insert 3 empty columns to the right
of column A (if necessary), select column A:A, and
choose the Data | Text to Columns... menu command.
Pick "Delimited" at Step 1 of 3 and press [ Next> ].
Check "Space" at Step 2 of 3 and press [ Finish ].