Excel "text to format" question [Closed]

Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
Hello,

My question seems like it would be quite common, but I can't find the answer anywhere:

So, if I have a list of names on Excel (that's over 200 people long), and the full name is all in one cell, and I want to separate the names (like you do with "text to column"), BUT the glitch is that some of the names are 3 words long and some are 2 words long. If I use the space as a delimiter, then the second column will be all messed up--because it will show a mix of both last and middle names together!
I need like an 'advanced' delimiter option--to specify that I only want to separate the LAST space--so that only the LAST names appear in the second column.

I ran into the same problem as well when I was separating addresses by column: For example:
Address #1: Orlando FL
-versus-
Address #2: Ft. Lauderdale FL

You can't delimit with a space, or else it cuts address #2 off after "Ft.".

Seems like a common enough problem--but I can't find any answers to it.

Thanks in advance!

3 replies

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Hello Joseph

try this , just idea only
if(countif(cell," ")>2,delimit at 2nd space, delimit at 1st space)
Hmm... I'm not quite sure I understand...
Do I put that into a new cell as a formula? Anyone have more suggestions?
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Try this
=IF(ISERROR(FIND(" ",E13,FIND(" ",E13,1)+1)),LEFT(E13,FIND(" ",E13)),LEFT(E13,FIND(" ",E13,FIND(" ",E13,1)+1)))