| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last Name
Hi,
I have hundreds of cell containing full names starting with their first name. I need a formula to display Last Name, First name and Middle Initial. Ex: Johnny K. Walker should be Walker, Johnny K. Thxs, |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last Name
Look he
http://www.cpearson.com/excel/FirstLast.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Talladega" wrote in message ... | Hi, | | I have hundreds of cell containing full names starting with their first | name. I need a formula to display Last Name, First name and Middle Initial. | Ex: Johnny K. Walker should be Walker, Johnny K. | | Thxs, |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last Name
Thx for the reply, but the link did not help me with my question. It require
the info to already be by last name. I need a formula that would show last name first, first name and middle from a cell that begin with the first to last name. "Niek Otten" wrote: Look he http://www.cpearson.com/excel/FirstLast.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Talladega" wrote in message ... | Hi, | | I have hundreds of cell containing full names starting with their first | name. I need a formula to display Last Name, First name and Middle Initial. | Ex: Johnny K. Walker should be Walker, Johnny K. | | Thxs, |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last Name
What's the difference? It's not like any formula can distinguish between a
first and a last name. All the formulas do is to reverse the order of the text strings and that is what you want to do, right? Also you should really post an example how the names look like, do you for instance have commas or just spaces, if the latter you can replace "," with " " -- Regards, Peo Sjoblom "Talladega" wrote in message ... Thx for the reply, but the link did not help me with my question. It require the info to already be by last name. I need a formula that would show last name first, first name and middle from a cell that begin with the first to last name. "Niek Otten" wrote: Look he http://www.cpearson.com/excel/FirstLast.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Talladega" wrote in message ... | Hi, | | I have hundreds of cell containing full names starting with their first | name. I need a formula to display Last Name, First name and Middle Initial. | Ex: Johnny K. Walker should be Walker, Johnny K. | | Thxs, |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last Name
Assuming full names always included middle initial, then try this:
=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))&", "&LEFT(A1,FIND(" ",A1))&" "&MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)) "Talladega" wrote: Hi, I have hundreds of cell containing full names starting with their first name. I need a formula to display Last Name, First name and Middle Initial. Ex: Johnny K. Walker should be Walker, Johnny K. Thxs, |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last Name
Thx for the reply, full name does not always have the middle initial.
"Teethless mama" wrote: Assuming full names always included middle initial, then try this: =RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))&", "&LEFT(A1,FIND(" ",A1))&" "&MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)) "Talladega" wrote: Hi, I have hundreds of cell containing full names starting with their first name. I need a formula to display Last Name, First name and Middle Initial. Ex: Johnny K. Walker should be Walker, Johnny K. Thxs, |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last Name
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND("
",A1)+1))&", "&LEFT(A1,FIND(" ",A1))&" "&MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)) "Talladega" wrote: Thx for the reply, full name does not always have the middle initial. "Teethless mama" wrote: Assuming full names always included middle initial, then try this: =RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))&", "&LEFT(A1,FIND(" ",A1))&" "&MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)) "Talladega" wrote: Hi, I have hundreds of cell containing full names starting with their first name. I need a formula to display Last Name, First name and Middle Initial. Ex: Johnny K. Walker should be Walker, Johnny K. Thxs, |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Last Name
Some of your results end with char(32). Try this, it's shorter:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2, MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,255) &", "&LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1) MID(A1&", "&A1,FIND(" ",A1)+1,LEN(A1)+1)) If you have something like this it will be ok: Oscar de la Hoya (boxer) But, if you have something like this: Oscar T. de la Hoya (boxer) Martin St. Louis (NHL hockey player) Good luck! There are just *too many* possibilities to try to account for. Biff "Teethless mama" wrote in message ... =IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))&", "&LEFT(A1,FIND(" ",A1))&" "&MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)) "Talladega" wrote: Thx for the reply, full name does not always have the middle initial. "Teethless mama" wrote: Assuming full names always included middle initial, then try this: =RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))&", "&LEFT(A1,FIND(" ",A1))&" "&MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)) "Talladega" wrote: Hi, I have hundreds of cell containing full names starting with their first name. I need a formula to display Last Name, First name and Middle Initial. Ex: Johnny K. Walker should be Walker, Johnny K. Thxs, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
