I have text in a column that is similar to this:
"Last Name <two spaces firstname<SpaceInitial" 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

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.


"ben" wrote:

I have text in a column that is similar to this:
"Last Name <two spaces firstname<SpaceInitial" 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

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" wrote:

I have text in a column that is similar to this:
"Last Name <two spaces firstname<SpaceInitial" 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

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