How to do string search in Excel using functions

This article is continuation from the last article published here
In this article, we will split string from the left using a user defined character as a delimiter. Lets get started.

The Excel function below get the first set of characters (from cell A1) before the first user defined delimiter; for this example it is space (” “).

=LEFT(A1, SEARCH(" ",A1,1))

You can also use the function FIND instead of SEARCH which essentially does the same thing.

=LEFT(A1,FIND(" ",A1)-1)

Replace the space with the character you want to search. Also, ensure that source cell (shown as A1 in the formula above) is modified accordingly.

The function example below output string after the first space from the back.

The Excel function below get the remaining set of characters (from cell A1) after the first user defined delimiter; for this example it is space (” “).

=RIGHT(A2,LEN(A1)-SEARCH(" ",A1,1))

You can also use the function FIND instead of SEARCH which essentially does the same thing.

=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

Replace the space with the character you want to search. Also, ensure that source cell (shown as A1 in the formula above) is modified accordingly.

The function example below output string after the first space from the back.

Here is another example.