string manipulation, pulling out last name from column
 

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

string manipulation, pulling out last name from column
 

This formula should work for you...

=LEFT(A1,FIND(",",A1)-1)

Rick


wrote in message
...
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

string manipulation, pulling out last name from column
 

=REPLACE(A1,FIND(",",A1),99,"")


" 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

string manipulation, pulling out last name from column
 

Thank you so much for your help! My last question is, how do I now
delete the lastname and the comma and space from the column? For
example, you've helped me succeed in parsing out the last name into
its own column...
How do I then take:
"Lastname, Husbandfirstname and Wifefirstname"

And delete the "Lastname, " from within that column so that only
"Husbandfirstname and Wifefirstname" remain?

Again - I am very grateful for your time.
AP

string manipulation, pulling out last name from column
 

=MID(A1,FIND(" ",A1)+1,99)

" wrote:

Thank you so much for your help! My last question is, how do I now
delete the lastname and the comma and space from the column? For
example, you've helped me succeed in parsing out the last name into
its own column...
How do I then take:
"Lastname, Husbandfirstname and Wifefirstname"

And delete the "Lastname, " from within that column so that only
"Husbandfirstname and Wifefirstname" remain?

Again - I am very grateful for your time.
AP

string manipulation, pulling out last name from column
 

Thank you SO much. We are good to go.
AP

string manipulation, pulling out last name from column
 

You're welcome!


" wrote:

Thank you SO much. We are good to go.
AP

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