In a cell I have a string of text that contains both a Contact Name and Company Name (example in cell A2: John X. Doe, XYZ Inc.). I have successfully created a formula that uses the "_" (underscore character) that connects Company Name with ending Company Name Suffixes (i.e., _Inc., _Corporation, _LLC, etc.). This method connects the Company Name and Company Suffix together and treats the Company as one whole or complete word.
Example 1: Cell A2: John X. Doe, XYZ Inc.
Example 2: Cell B2: John X. Doe, XYZ_Inc.
Trying to achieve the following:
Results 1: Cell C2: John X. Doe
Results 2: Cell D2: XYZ_Inc.
I would like to be able to extract (XYZ_Inc.) from the text string located in B2, using a wildcard expression (i.e. "*"_"*") to extract/remove the Company Name located on the right from the Contact Name located on the left. As you many know Company Names may vary and not consistent, thus the use of an ID character _ and wildcard (asterisk *) may be appropriate if possible to identify and treat the connected Company Name as one word and remove.
Please try, in C2:
=SUBSTITUTE(LEFT(SUBSTITUTE(B2," ","|",(LEN(B2)-LEN(SUBSTITUTE(B2," ","")))),FIND("|",SUBSTITUTE(B2," ","|",(LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))-1),",","")
in D2:
=MID(SUBSTITUTE(B2," ","|",(LEN(B2)-LEN(SUBSTITUTE(B2," ","")))),FIND("|",SUBSTITUTE(B2," ","|",(LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))+1,LEN(B2))