string manipulation, pulling out last name from column

[ap]

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

 

[Rick Rothstein \(MVP - VB\)]

This formula should work for you...

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

Rick

 

[Teethless mama]

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

 

[ap]

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

 

[Teethless mama]

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

 

[ap]

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

 

[Teethless mama]

You're welcome!

 

[Ron Rosenfeld]

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