Menu

string manipulation, pulling out last name from column

Search this Thread

Prev

Previous Post

Next Post

Next

#8

Report Post

Posted to microsoft.public.excel.worksheet.functions

Ron Rosenfeld Ron Rosenfeld is offline

Ron Rosenfeld is offline

external usenet poster

Posts: 5,651

Default

string manipulation, pulling out last name from column

On Sat, 3 May 2008 07:35:09 -0700 (PDT), wrote:

Hello all,
I have an Excel spreadsheet with a list of wedding invitees, but the
names are in the improper format:

"Lastname, Husbandfirstname and Wifefirstname"

I need to extract out the "Lastname, " into a new column, and then
clean up that column by deleting the ending comma and space (which is
", ").

I think that is clear. I really appreciate your help. I did some
basic searching to try to find an answer, and I learned some things
about string manipulation, but wasn't successful tweaking the formula
to do what I want. Also, I must say I am not clear as to how to APPLY
the formula to an existing column.

Thank you for your time.
AP

You could use the Data/Text to Column wizard specifying <comma as the
separator.

But " husbandfirstname and wifefirstname" will remain in the target cell with a
leading space. So you can "clean that up" by using the function, in somecell,
of =TRIM(A2).

For example, with data in A2:A100
Select the Range
Select Data/Text to Columns
Delimited
<NEXT
` Select Comma
<FINISH
Then
C2: =TRIM(B2)
Fill down to C100

Select C2:C100
Edit/Copy
Edit/Paste Special/Values

You can then delete column B

--ron

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Text string manipulation...	NWO	Excel Worksheet Functions	4	August 3rd 07 03:31 AM
Pulling Year from a text string	Elkar	Excel Worksheet Functions	0	February 20th 07 06:33 PM
String Manipulation within VBA	BillCPA	Excel Discussion (Misc queries)	2	December 6th 06 05:29 PM
String Manipulation	String Manipulation	Excel Discussion (Misc queries)	3	November 30th 05 11:51 PM
pulling characters out of a string	Patrick	Excel Worksheet Functions	3	November 12th 04 06:58 PM

All times are GMT +1. The time now is 06:01 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement