Forum Discussion
Split string at particular word. (space+word+space)
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.
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.
- Thanks, Sergei!
You are welcome