Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string manipulation, pulling out last name from column
Thank you SO much. We are good to go.
AP |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string manipulation, pulling out last name from column
You're welcome!
" wrote: Thank you SO much. We are good to go. AP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string manipulation, pulling out last name from column
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text string manipulation... | Excel Worksheet Functions | |||
Pulling Year from a text string | Excel Worksheet Functions | |||
String Manipulation within VBA | Excel Discussion (Misc queries) | |||
String Manipulation | Excel Discussion (Misc queries) | |||
pulling characters out of a string | Excel Worksheet Functions |