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 |
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) |