Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse from the Right
One of our Administrative Assistants set up a worksheet with Name in one
cell. Unfortunately, she has some with first and last name only, and some include a middle name. I can parse the last name with the formula =MID(A1,FIND(" ",A1,1),30) for those cells that contain only first and last name, but, if a cell contains a middle name, this formula yields the middle and last name because it starts from the left. I need only the last name. I believe I need a formula that starts from the right, finds the first space from the right, and then returns all characters to the left of that space. I cant figure out how to do that. Any suggestions will be appreciated. Paul |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse from the Right
Sorry, to correct the end of my post, the line "I believe I need a formula
that starts from the right, finds the first space from the right, and then returns all characters to the left of that space" should be to the RIGHT of that space. In summary, I need to find the first space from the right, and then return all the characters to RIGHT of that space. Sorry, dumb mistake. "PA" wrote: One of our Administrative Assistants set up a worksheet with Name in one cell. Unfortunately, she has some with first and last name only, and some include a middle name. I can parse the last name with the formula =MID(A1,FIND(" ",A1,1),30) for those cells that contain only first and last name, but, if a cell contains a middle name, this formula yields the middle and last name because it starts from the left. I need only the last name. I believe I need a formula that starts from the right, finds the first space from the right, and then returns all characters to the left of that space. I cant figure out how to do that. Any suggestions will be appreciated. Paul |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse from the Right
If you're going to do this with formulas, I'd use =RIGHT(A1,LEN(A1)-FIND("
",A1)) for the first name, last name case and =RIGHT(A1,LEN(A1)-(FIND(" ",A1,FIND(" ",A1)+1))) where you have first, middle, last. Rather than parsing from the right, I'm just looking for the second space character. If you would consider NOT using formulas, check Data Text To Columns. Excel can do the splits for you, based on your choice of delimiters (and can also handle repeated delimiters, such as two spaces where you're only expecting one). "PA" wrote: One of our Administrative Assistants set up a worksheet with Name in one cell. Unfortunately, she has some with first and last name only, and some include a middle name. I can parse the last name with the formula =MID(A1,FIND(" ",A1,1),30) for those cells that contain only first and last name, but, if a cell contains a middle name, this formula yields the middle and last name because it starts from the left. I need only the last name. I believe I need a formula that starts from the right, finds the first space from the right, and then returns all characters to the left of that space. I cant figure out how to do that. Any suggestions will be appreciated. Paul |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse from the Right
On Sun, 11 Jun 2006 05:24:02 -0700, PA wrote:
One of our Administrative Assistants set up a worksheet with Name in one cell. Unfortunately, she has some with first and last name only, and some include a middle name. I can parse the last name with the formula =MID(A1,FIND(" ",A1,1),30) for those cells that contain only first and last name, but, if a cell contains a middle name, this formula yields the middle and last name because it starts from the left. I need only the last name. I believe I need a formula that starts from the right, finds the first space from the right, and then returns all characters to the left of that space. I cant figure out how to do that. Any suggestions will be appreciated. Paul This formula will give you the last word in the string, so long as there are at least two words. =MID(A10,FIND(CHAR(1),SUBSTITUTE(A10," ",CHAR(1), LEN(A10)-LEN(SUBSTITUTE(A10," ",""))))+1,255) --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse from the Right
Two possible problems with your solutions,
Unless I am doing something incorrect with the Text to Colums, middle names get mixed up with last names, and the last names of those with middle names get pushed out an additional column to the right. The formula solution you propose works, except that I need to examine the data in each row and then put in the appropriate formula. "bpeltzer" wrote: If you're going to do this with formulas, I'd use =RIGHT(A1,LEN(A1)-FIND(" ",A1)) for the first name, last name case and =RIGHT(A1,LEN(A1)-(FIND(" ",A1,FIND(" ",A1)+1))) where you have first, middle, last. Rather than parsing from the right, I'm just looking for the second space character. If you would consider NOT using formulas, check Data Text To Columns. Excel can do the splits for you, based on your choice of delimiters (and can also handle repeated delimiters, such as two spaces where you're only expecting one). "PA" wrote: One of our Administrative Assistants set up a worksheet with Name in one cell. Unfortunately, she has some with first and last name only, and some include a middle name. I can parse the last name with the formula =MID(A1,FIND(" ",A1,1),30) for those cells that contain only first and last name, but, if a cell contains a middle name, this formula yields the middle and last name because it starts from the left. I need only the last name. I believe I need a formula that starts from the right, finds the first space from the right, and then returns all characters to the left of that space. I cant figure out how to do that. Any suggestions will be appreciated. Paul |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse from the Right
Thanks Ron, that does it for us. It even works if there are four name.
"Ron Rosenfeld" wrote: On Sun, 11 Jun 2006 05:24:02 -0700, PA wrote: One of our Administrative Assistants set up a worksheet with Name in one cell. Unfortunately, she has some with first and last name only, and some include a middle name. I can parse the last name with the formula =MID(A1,FIND(" ",A1,1),30) for those cells that contain only first and last name, but, if a cell contains a middle name, this formula yields the middle and last name because it starts from the left. I need only the last name. I believe I need a formula that starts from the right, finds the first space from the right, and then returns all characters to the left of that space. I cant figure out how to do that. Any suggestions will be appreciated. Paul This formula will give you the last word in the string, so long as there are at least two words. =MID(A10,FIND(CHAR(1),SUBSTITUTE(A10," ",CHAR(1), LEN(A10)-LEN(SUBSTITUTE(A10," ",""))))+1,255) --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse from the Right
On Sun, 11 Jun 2006 06:24:01 -0700, PA wrote:
Thanks Ron, that does it for us. It even works if there are four name. You're welcome. Thanks for the feedback. Yes, it will always return the characters after the last <space, no matter how many words/names there are. It will give an error if there is one or zero words. Also, if there are any trailing spaces, the formula will return a <blank. In order to eliminate both of those problems, one could change the formula to: =IF(ISERR(FIND(" ",TRIM(A1))),"",MID(TRIM(A1),FIND( CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN( TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,255)) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parse data where break is a first uppercase character in a string? | Excel Worksheet Functions | |||
Parse contents of cell | Excel Discussion (Misc queries) | |||
Parse ST-ZIP Cell (15,427 times...) | Excel Discussion (Misc queries) | |||
Parse data with uneven lengths and different delimiters | Excel Worksheet Functions | |||
Parse a Workbook-Newbie | Excel Worksheet Functions |