0

Need to parse the strings in excel with a single formula which has different patterns and rules listed below

Any . or space within a name was considered to be a delimiter. For example the full employee name of ‘A.B.C Bobby’ was considered to have 4 separate components (‘A.’, ‘B.’, ‘C’, and ‘Bobby’)

The last component of the parsed employee name always went into the Last Name field

If there were two component for the parsed employee name, the first part went into the First Name field, the last part went into Last Name

If there were three or more components for the parsed employee name, the first part went into First Name, the last part went into Last Name. Everything else went into Middle Name, with a single space in between any multiple parts of middle name components

Trials:

I am trying to use below formula but it does not work for all needs especially when there is a value like A.B.C.D.BOBBY where A. should be parsed as first name, B.C.D. as middle name and Bobby as Lastname

=IFERROR( MID(A2,SEARCH(".",A2)+1,SEARCH(".",A2,SEARCH(".",A2)+1)-SEARCH(".",A2)),IFERROR(MID(A2,SEARCH(".",A2)+1,SEARCH(" ",A2,SEARCH(".",A2)+1)-SEARCH(".",A2)-1),""))

msv
  • 15
  • 3
  • First you say `Any . or space within a name was considered to be a delimiter` then `Everything else went into Middle Name, with a single space in between any multiple parts` but then show `B.C.D.` as the output, why not `B C D`? – Scott Craner Mar 09 '21 at 17:31
  • B.C.D can be the output so as B C D as well, depends on the string value – msv Mar 09 '21 at 18:15
  • use a mixture of TEXJOIN and FILTERXML. See: https://stackoverflow.com/questions/61918806/returning-substring-after-using-filterxml – Scott Craner Mar 09 '21 at 18:17
  • @ScottCraner thanks for your reply. Can you just state a example using both functions? – msv Mar 09 '21 at 20:50

0 Answers0