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 ].
