Forum Discussion
Solved
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 ...
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.