|
RIGHT & MID & LEFT
I have a spreadsheet with all names converted to Last Name, First Name.
Some, not all, of the names have a middle name as well. I have searched postings and Microsoft help and cannot figure out how to return "Smith, John Michael" if I have the name "John Michael Smith". Thank you in advance for any help. |
I am assuming that you are starting with names like:
John Michael Smith Victor Laszo First use Data Text to Columns to split the field into three or two separate cells. Say the name are in column A, and the first name is in column B, column C will either contain the middle or last name. Column D will contain the last name if there is a middle name. Use the IF function to see if the cell in D is empty. if D is empty then use =C1 & ", " & B1 If D is not empty then use =D1 & ", " & B1 & " " & C1 -- Gary's Student "Melissa" wrote: I have a spreadsheet with all names converted to Last Name, First Name. Some, not all, of the names have a middle name as well. I have searched postings and Microsoft help and cannot figure out how to return "Smith, John Michael" if I have the name "John Michael Smith". Thank you in advance for any help. |
I currently have the following formula for those names without a middle
name/initial. Is there not a way I can just add "MID" to it? =RIGHT(A19,LEN(A19)-FIND(" ",A19))&", "&LEFT(A19,FIND(" ",A19)-1) "Gary's Student" wrote: I am assuming that you are starting with names like: John Michael Smith Victor Laszo First use Data Text to Columns to split the field into three or two separate cells. Say the name are in column A, and the first name is in column B, column C will either contain the middle or last name. Column D will contain the last name if there is a middle name. Use the IF function to see if the cell in D is empty. if D is empty then use =C1 & ", " & B1 If D is not empty then use =D1 & ", " & B1 & " " & C1 -- Gary's Student "Melissa" wrote: I have a spreadsheet with all names converted to Last Name, First Name. Some, not all, of the names have a middle name as well. I have searched postings and Microsoft help and cannot figure out how to return "Smith, John Michael" if I have the name "John Michael Smith". Thank you in advance for any help. |
=IF(LEN(A19)-LEN(SUBSTITUTE(A19," ",""))1,RIGHT(A19,LEN(A19)-FIND("
",A19,FIND(" ",A19)+1))&", "&LEFT(A19,FIND(" ",A19,FIND(" ",A19)+1)-1),RIGHT(A19,LEN(A19)-FIND(" ",A19))&", "&LEFT(A19,FIND(" ",A19)-1)) -- HTH RP (remove nothere from the email address if mailing direct) "Melissa" wrote in message ... I currently have the following formula for those names without a middle name/initial. Is there not a way I can just add "MID" to it? =RIGHT(A19,LEN(A19)-FIND(" ",A19))&", "&LEFT(A19,FIND(" ",A19)-1) "Gary's Student" wrote: I am assuming that you are starting with names like: John Michael Smith Victor Laszo First use Data Text to Columns to split the field into three or two separate cells. Say the name are in column A, and the first name is in column B, column C will either contain the middle or last name. Column D will contain the last name if there is a middle name. Use the IF function to see if the cell in D is empty. if D is empty then use =C1 & ", " & B1 If D is not empty then use =D1 & ", " & B1 & " " & C1 -- Gary's Student "Melissa" wrote: I have a spreadsheet with all names converted to Last Name, First Name. Some, not all, of the names have a middle name as well. I have searched postings and Microsoft help and cannot figure out how to return "Smith, John Michael" if I have the name "John Michael Smith". Thank you in advance for any help. |
Thank you.
When I tried this formula, and changed the "A19" to the correct cell, I received a #VALUE error. I'm not familiar with many of the formulas in excel. Any suggestions? "Bob Phillips" wrote: =IF(LEN(A19)-LEN(SUBSTITUTE(A19," ",""))1,RIGHT(A19,LEN(A19)-FIND(" ",A19,FIND(" ",A19)+1))&", "&LEFT(A19,FIND(" ",A19,FIND(" ",A19)+1)-1),RIGHT(A19,LEN(A19)-FIND(" ",A19))&", "&LEFT(A19,FIND(" ",A19)-1)) -- HTH RP (remove nothere from the email address if mailing direct) "Melissa" wrote in message ... I currently have the following formula for those names without a middle name/initial. Is there not a way I can just add "MID" to it? =RIGHT(A19,LEN(A19)-FIND(" ",A19))&", "&LEFT(A19,FIND(" ",A19)-1) "Gary's Student" wrote: I am assuming that you are starting with names like: John Michael Smith Victor Laszo First use Data Text to Columns to split the field into three or two separate cells. Say the name are in column A, and the first name is in column B, column C will either contain the middle or last name. Column D will contain the last name if there is a middle name. Use the IF function to see if the cell in D is empty. if D is empty then use =C1 & ", " & B1 If D is not empty then use =D1 & ", " & B1 & " " & C1 -- Gary's Student "Melissa" wrote: I have a spreadsheet with all names converted to Last Name, First Name. Some, not all, of the names have a middle name as well. I have searched postings and Microsoft help and cannot figure out how to return "Smith, John Michael" if I have the name "John Michael Smith". Thank you in advance for any help. |
You need to be careful because it is so long. Copy it and paste it into the
formula bar, not the cell, and amend it there. -- HTH RP (remove nothere from the email address if mailing direct) "Melissa" wrote in message ... Thank you. When I tried this formula, and changed the "A19" to the correct cell, I received a #VALUE error. I'm not familiar with many of the formulas in excel. Any suggestions? "Bob Phillips" wrote: =IF(LEN(A19)-LEN(SUBSTITUTE(A19," ",""))1,RIGHT(A19,LEN(A19)-FIND(" ",A19,FIND(" ",A19)+1))&", "&LEFT(A19,FIND(" ",A19,FIND(" ",A19)+1)-1),RIGHT(A19,LEN(A19)-FIND(" ",A19))&", "&LEFT(A19,FIND(" ",A19)-1)) -- HTH RP (remove nothere from the email address if mailing direct) "Melissa" wrote in message ... I currently have the following formula for those names without a middle name/initial. Is there not a way I can just add "MID" to it? =RIGHT(A19,LEN(A19)-FIND(" ",A19))&", "&LEFT(A19,FIND(" ",A19)-1) "Gary's Student" wrote: I am assuming that you are starting with names like: John Michael Smith Victor Laszo First use Data Text to Columns to split the field into three or two separate cells. Say the name are in column A, and the first name is in column B, column C will either contain the middle or last name. Column D will contain the last name if there is a middle name. Use the IF function to see if the cell in D is empty. if D is empty then use =C1 & ", " & B1 If D is not empty then use =D1 & ", " & B1 & " " & C1 -- Gary's Student "Melissa" wrote: I have a spreadsheet with all names converted to Last Name, First Name. Some, not all, of the names have a middle name as well. I have searched postings and Microsoft help and cannot figure out how to return "Smith, John Michael" if I have the name "John Michael Smith". Thank you in advance for any help. |
| All times are GMT +1. The time now is 11:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com