Excel cannot determine whether a string starts with last name, first name - company name however, it can determine the pattern if its constructed to find within a string.
What I have found from the context of the OP, and comments posted by OP, that it follows a certain pattern, and the pattern is like as below:
Last Name, First Name - Company Name
So, if we take the comma space and dash or hyphen space as the pattern between last name and first name and first name and company name, we can actually reach at a point of achieving the desired output.
• Formula used in cell E2
=LET(
α, {", "," - "},
AND(TEXTSPLIT(ClaimTbl[@Summary],
TEXTSPLIT(ClaimTbl[@Summary],α),,1)=α))
- First , split by the delimiters accounting here as the pattern check, using
TEXTSPLIT() function
- Then use again
TEXTSPLIT() function to split data the actual data using the above.
- Finally, compare it with the pattern to get
TRUE and FALSE. I will explain and show in screenshots.
For reading and understandability one can write as :
=LET(
_Delim, {", "," - "},
_SplitByDelim, TEXTSPLIT(ClaimTbl[@Summary],_Delim),
_SplitNotByDelim, TEXTSPLIT(ClaimTbl[@Summary],_SplitByDelim,,1),
_Compare, AND(_SplitNotByDelim={", "," - "}),
IF(_Compare, "FOUND","NA"))
CAVEAT: Again, to remind that the formula above doesn't determine whether the string starts with last name or first name or company, it only tries to find a specific pattern. Also it has been assumed that there will be comma between last name and first name, while a dash or hyphen will be between first name and company, usually it will stick to the pattern of Last Name, First Name - Company Name beyond any is not found.
