Forum Discussion
Split string at particular word. (space+word+space)
Hello all:
I have a column in Excel that contains person's title and company name separated by " at ". I tried to split with text to column with left most and right most occurrence but could not succeed as title or company name may also have at in their word. And I also checked " at " (giving space before and after at) but didn't work.
| State Head at Sanofi International |
| Head Real Estate at United Builders |
| Business Unit Head at Lilly |
Could you please help me out.
Appreciate your time.
Regards
G
Guarav, in your texts there is symbol CHAR(160) instead of space. You may use
=IFERROR(LEFT(A31,SEARCH(CHAR(160)&"at"&CHAR(160),A31)),A31)
for the left part, for the right one the formula is the same, attached.
Alternatively you may SUBSTITUTE that symbol on spaces.
5 Replies
Hi Gaurav,
The idea is here https://exceljet.net/formula/split-text-with-delimiter. However, if you have only one or no " at " you may use formulas like
=IFERROR(LEFT(A1,SEARCH(" at ",A1)),A1)for the left part and
=IFERROR(RIGHT(A1, LEN(A1)-LEN(B1)-3),"")
for the right part. See attached.
Another option is to use Power Query.
Thanks Sergei for replying. Somehow when I am using your formula for other records, it's not working. I've attached the file for your reference.
Best,
G
Guarav, in your texts there is symbol CHAR(160) instead of space. You may use
=IFERROR(LEFT(A31,SEARCH(CHAR(160)&"at"&CHAR(160),A31)),A31)
for the left part, for the right one the formula is the same, attached.
Alternatively you may SUBSTITUTE that symbol on spaces.
