Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParsingFullName
I am using the formulas below to parse the fullname into a FirstName
MiddleInitial and Lastname field. This works great for the fullnames that have a middle initial, but errors on those names without a middle initial. How could the formulas be altered to allow for a name without a middle initial. Thanks to those of you who replyed to my posting yesterday. I should have been more clear about the input name. TIA LastName, John Q (Comma): =FIND(",",A2) (First Name): =MID(A2,B2+1,FIND(" ",A2, B2)-B2-1) (Middle Name): =RIGHT(A2,1) & "." (Last Name): =LEFT(A2,B2-1) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParsingFullName
(Comma): =FIND(",",A2)
(First Name): =IF(ISERROR(FIND(" ",A2, B2+2)-B2-1),MID(A2,FIND(", ",A2)+1,99),MID(A2,B2+2,FIND(" ",A2, B2+2)-B2-1)) (Middle Name): =IF(ISERROR(FIND(" ",A2, B2+2)-B2-1),"",RIGHT(A2,1) & ".") (Last Name): =LEFT(A2,B2-1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "gh" wrote in message ... I am using the formulas below to parse the fullname into a FirstName MiddleInitial and Lastname field. This works great for the fullnames that have a middle initial, but errors on those names without a middle initial. How could the formulas be altered to allow for a name without a middle initial. Thanks to those of you who replyed to my posting yesterday. I should have been more clear about the input name. TIA LastName, John Q (Comma): =FIND(",",A2) (First Name): =MID(A2,B2+1,FIND(" ",A2, B2)-B2-1) (Middle Name): =RIGHT(A2,1) & "." (Last Name): =LEFT(A2,B2-1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|