Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
Hello,
I have a list of names; some of which contain the fistname, middle initial, and last name. The other names in the list are only first and last names. For example: John T. Doe and John Doe. I need to extract the middle intial and the period after the MI so that I end up with a list of names with just the first name and last name. I have the formula below that will work successfully to remove the "MI.": =LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7)) But when I try to wrap the above formula in an IF statement so that it will state; "if the name in the cell contains a period then remove it and return just the FN & LN; if there is no period then just return the name in the cell. Here is the formula that I have tried to use to accomplish this: =IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5) Unfortunately I get a #VALUE error with this formula. I feel like I am close but at this point am stumped on how to get past this error. Any help or suggestions on how to accomplish my task will be greatly appreciated. Thank you. Dave |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
=IF(FIND(".",B5)0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5)
You had the first FIND parameters reversed! "Dave Y" wrote: Hello, I have a list of names; some of which contain the fistname, middle initial, and last name. The other names in the list are only first and last names. For example: John T. Doe and John Doe. I need to extract the middle intial and the period after the MI so that I end up with a list of names with just the first name and last name. I have the formula below that will work successfully to remove the "MI.": =LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7)) But when I try to wrap the above formula in an IF statement so that it will state; "if the name in the cell contains a period then remove it and return just the FN & LN; if there is no period then just return the name in the cell. Here is the formula that I have tried to use to accomplish this: =IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5) Unfortunately I get a #VALUE error with this formula. I feel like I am close but at this point am stumped on how to get past this error. Any help or suggestions on how to accomplish my task will be greatly appreciated. Thank you. Dave |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
FIND returns #VALUE if the text you are looking for is not contained
within the cell. To get round this try this approach: =IF(ISERROR(FIND(B5,".")),B5,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)- FIND(".",B5))) Hope this helps. Pete On Jul 11, 2:56 pm, Dave Y wrote: Hello, I have a list of names; some of which contain the fistname, middle initial, and last name. The other names in the list are only first and last names. For example: John T. Doe and John Doe. I need to extract the middle intial and the period after the MI so that I end up with a list of names with just the first name and last name. I have the formula below that will work successfully to remove the "MI.": =LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7)) But when I try to wrap the above formula in an IF statement so that it will state; "if the name in the cell contains a period then remove it and return just the FN & LN; if there is no period then just return the name in the cell. Here is the formula that I have tried to use to accomplish this: =IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5) Unfortunately I get a #VALUE error with this formula. I feel like I am close but at this point am stumped on how to get past this error. Any help or suggestions on how to accomplish my task will be greatly appreciated. Thank you. Dave |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
Extract the first and last names irrespective of whether there's a middle
name or not:- =LEFT(A1,FIND(" ",A1)-1)&" "&IF(ISERR(FIND(" ",A1,1+FIND(" ",A1))),MID(A1,FIND(" ",A1)+1,LEN(A1)),MID(A1,1+FIND(" ",A1,1+FIND(" ",A1)),LEN(A1))) Mike "Dave Y" wrote: Hello, I have a list of names; some of which contain the fistname, middle initial, and last name. The other names in the list are only first and last names. For example: John T. Doe and John Doe. I need to extract the middle intial and the period after the MI so that I end up with a list of names with just the first name and last name. I have the formula below that will work successfully to remove the "MI.": =LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7)) But when I try to wrap the above formula in an IF statement so that it will state; "if the name in the cell contains a period then remove it and return just the FN & LN; if there is no period then just return the name in the cell. Here is the formula that I have tried to use to accomplish this: =IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5) Unfortunately I get a #VALUE error with this formula. I feel like I am close but at this point am stumped on how to get past this error. Any help or suggestions on how to accomplish my task will be greatly appreciated. Thank you. Dave |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
=IF(ISNUMBER(FIND(".",B5)),REPLACE(B5,FIND(".",B5)-2,3,""),B5)
"Dave Y" wrote: Hello, I have a list of names; some of which contain the fistname, middle initial, and last name. The other names in the list are only first and last names. For example: John T. Doe and John Doe. I need to extract the middle intial and the period after the MI so that I end up with a list of names with just the first name and last name. I have the formula below that will work successfully to remove the "MI.": =LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7)) But when I try to wrap the above formula in an IF statement so that it will state; "if the name in the cell contains a period then remove it and return just the FN & LN; if there is no period then just return the name in the cell. Here is the formula that I have tried to use to accomplish this: =IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5) Unfortunately I get a #VALUE error with this formula. I feel like I am close but at this point am stumped on how to get past this error. Any help or suggestions on how to accomplish my task will be greatly appreciated. Thank you. Dave |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
Hello everyone,
I really appreciate your quick responses. I now have the formula working. You guys are the best! Thank you very much. "Dave Y" wrote: Hello, I have a list of names; some of which contain the fistname, middle initial, and last name. The other names in the list are only first and last names. For example: John T. Doe and John Doe. I need to extract the middle intial and the period after the MI so that I end up with a list of names with just the first name and last name. I have the formula below that will work successfully to remove the "MI.": =LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7)) But when I try to wrap the above formula in an IF statement so that it will state; "if the name in the cell contains a period then remove it and return just the FN & LN; if there is no period then just return the name in the cell. Here is the formula that I have tried to use to accomplish this: =IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5) Unfortunately I get a #VALUE error with this formula. I feel like I am close but at this point am stumped on how to get past this error. Any help or suggestions on how to accomplish my task will be greatly appreciated. Thank you. Dave |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
Extract the first and last names irrespective of whether there's a middle
name or not:- =LEFT(A1,FIND(" ",A1)-1)&" "&IF(ISERR(FIND(" ",A1,1+FIND(" ",A1))),MID(A1,FIND(" ",A1)+1,LEN(A1)),MID(A1,1+FIND(" ",A1,1+FIND(" ",A1)),LEN(A1))) A slightly shorter formula to do the same thing... =LEFT(A1,FIND(" ",A1))&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))1,MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,255),"") Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
"Rick Rothstein \(MVP - VB\)" wrote...
.... A slightly shorter formula to do the same thing... =LEFT(A1,FIND(" ",A1))&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))1, MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,255),"") If A1 contains John Smith your formula returns "John ". Methinks it should return the last name too. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
Dave Y wrote...
I have a list of names; some of which contain the fistname, middle initial, and last name. The other names in the list are only first and last names. For example: John T. Doe and John Doe. I need to extract the middle intial and the period after the MI so that I end up with a list of names with just the first name and last name. .... As someone who goes by his middle name, I have to question the 'logic' (or lack of same) for doing this. If you did this to my name, my father's and my grandfather's, we'd all wind up with the same name. None of us use generational qualifiers since all of us have different middle names, so there'd be no other way to distinguish between our names. You really want to lose the ability to distinguish between possibly (likely?) different individuals? If so, try =IF(COUNT(FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)), LEFT(TRIM(A1),FIND(" ",TRIM(A1)))&MID(TRIM(A1), LOOKUP(2,1/(MID(TRIM(A1),seq,1)=" "),seq)+1,255),TRIM(A1)) which uses the defined name seq referring to the formula =ROW(INDEX(Sheet2!$1:$65536,1,1):INDEX(Sheet2!$1:$ 65536,256,1)) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
A slightly shorter formula to do the same thing...
=LEFT(A1,FIND(" ",A1))&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))1, MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,255),"") If A1 contains John Smith your formula returns "John ". Methinks it should return the last name too Thanks for spotting that... I copied the wrong formula from my test sheet. The correct one is.. =LEFT(A1,FIND(" ",A1))&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))1,MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,255),MID(A1,FIND(" ",A1)+1,255)) which, while still shorter than Mike's, it's not as dramatically shorter than my mis-copying led me to believe it would be. I still think there is a shorter formula available (just a feeling). Rick |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Middle Initial from Name
On Wed, 11 Jul 2007 06:56:06 -0700, Dave Y
wrote: Hello, I have a list of names; some of which contain the fistname, middle initial, and last name. The other names in the list are only first and last names. For example: John T. Doe and John Doe. I need to extract the middle intial and the period after the MI so that I end up with a list of names with just the first name and last name. I have the formula below that will work successfully to remove the "MI.": =LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7)) But when I try to wrap the above formula in an IF statement so that it will state; "if the name in the cell contains a period then remove it and return just the FN & LN; if there is no period then just return the name in the cell. Here is the formula that I have tried to use to accomplish this: =IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5) Unfortunately I get a #VALUE error with this formula. I feel like I am close but at this point am stumped on how to get past this error. Any help or suggestions on how to accomplish my task will be greatly appreciated. Thank you. Dave This will remove the first instance in the string of a Letter followed by a Dot. =IF(ISERR(FIND(".",A1)),A1,TRIM(SUBSTITUTE( A1,MID(A1,SEARCH("?.",A1),2),""))) If the individual is using an initial for his first name, and then a full middle name (e.g. X. John Doe), it will remove the X. However, with a name like Jesse James Jr., it will return Jesse James J If these are issues, a UDF would be the simplest way to solve the problem. If not, perhaps the formula above will work. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate Middle Initial From First Name | Excel Discussion (Misc queries) | |||
Remove middle initial from "first name middle initial" | Excel Discussion (Misc queries) | |||
First name, Middle Initial | Excel Discussion (Misc queries) | |||
Stripping Middle Initial from a Name | Excel Worksheet Functions | |||
Extract middle initial | Excel Discussion (Misc queries) |