In this article, I’ll present how you can extract or remove the text before and after a character. In the later part, there are examples of how to remove the nth occurrence of the character you choose.
This is the example we are going to use:
Extract text before a character
To extract text before certain characters, you can use the following formula:
1 |
=LEFT(A2,FIND(" ",A2)-1) |
In our example, all text before the first space is displayed. In other words, we’ve just extracted names.
In this case, the FIND function returns the position of space in the string. The LEFT function returns the text from the beginning to that position. At the end of the formula, we subtract one because we don’t need a space at the end.
Extract text after a character
To extract text after the character, we are going to use this formula:
1 |
=RIGHT(A2,LEN(A2)-FIND(" ",A2)) |
The RIGHT function returns the number of characters from the end of a text string. The LEN function returns the number of characters inside the text. From this number, we subtract the position of the first occurrence of space.
The formula in cell C2 can also be written as:
1 |
=RIGHT(A2,10-6) |
That means: get 4 characters, starting from the right side.
Extract text before the nth occurrence
So far it was easy. But what if you want to extract text before the second or third occurrence of the character? In this case, the SUBSTITUTE function comes in handy.
1 |
=SUBSTITUTE(B2," ","_",2) |
Here is how it works in our case:
Take the text and replace ” ” with “_” at the second occurrence.
It’s important to use the replacement character that is not available inside the string.
1 |
=LEFT(B2,FIND("_",SUBSTITUTE(B2," ","_",2))-1) |
This formula will return text from the beginning to the second space.
Extract text after the nth occurrence
Similarly, you can extract text after the nth occurrence.
Now, when you have an underscore inside, you can use the RIGHT function as you did before.
1 |
=RIGHT(B2,LEN(B2)-FIND("_",SUBSTITUTE(B2," ","_",2))) |
This formula returns string after the second space.