Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / March 2008

Tip: Looking for answers? Try searching our database.

Text to Columns Question

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
ben - 15 Mar 2008 18:40 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.