Text to Columns Question

[ben]

I have text in a column that is similar to this:
"Last Name <two spaces> firstname<Space>Initial" and possibly a spouse name
and initial on the end of that.
My goal is to get the last name in a separate column. I want to use the
text to columns features but I can't do fixed width because names are
different lengths and if I use the <space> delimiter it separates everything
(last, first, initial, spouses name, Initial). What would work is if I could
set the delimiter to a double space but as far as I know excel only excepts
one character delimiters.

I'd like to be able to do this without complicated formulas but am really
looking for any solutions availible. Thanks for reading, Ben

 

[Sean Timmons]

You could do a text to columns with space delimiter, then in, say, column D,
type =B2&" "&c2

Which would put the first name and middle initial back together.

Then, just copy column D/paste special/values

Delete columns B and C.

 

[Mike H]

Hi,

In an adjacent coulmn use
=LEFT(A1,FIND(" ",A1,1)-1)
to extract the surname
and if you want the rest in another column use
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1)-1)

Mike

Mike

 

[ben]

Hi, Thanks for the replies. I was looking to find my post and found another
solution but really appreciate everyone taking the time. I don't know why I
didn't stumble upon it yesterday.

I haven't tried this yet but it sounds reasonable.

Select the column, do a find and replace for (two spaces) replaced with (
and then do a text to columns with a ( delimiter.

Again, thanks