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 - 15 Mar 2008 19:38 GMT
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.
> I have text in a column that is similar to this:
> "Last Name <two spaces> firstname<Space>Initial" and possibly a spouse name
[quoted text clipped - 8 lines]
> I'd like to be able to do this without complicated formulas but am really
> looking for any solutions availible. Thanks for reading, Ben
Mike H - 15 Mar 2008 19:42 GMT
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
> I have text in a column that is similar to this:
> "Last Name <two spaces> firstname<Space>Initial" and possibly a spouse name
[quoted text clipped - 8 lines]
> I'd like to be able to do this without complicated formulas but am really
> looking for any solutions availible. Thanks for reading, Ben
ben - 16 Mar 2008 17:59 GMT
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