Text to Columns Question

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

SeanTimmons
Sat Mar 15 13:38:01 CDT 2008

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

MikeH
Sat Mar 15 13:42:00 CDT 2008

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

ben
Sun Mar 16 11:59:02 CDT 2008

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