How to Extract Text After and Before a Character in Excel

In Excel, you can write formulas to extract text after or before a specific character. And in this tutorial, we will learn to write these formulas.

Extract Text After a Character

We need to use TRIM, RIGHT, SUBSTITUTE, REPT, and LEN in this formula. And In the below example, we have values two words in the values and character in between.

=TRIM(RIGHT(SUBSTITUTE(A1,B1,REPT(" ",LEN(A1))),LEN(A1)))
=TRIM(RIGHT(SUBSTITUTE(original_text,character,REPT(" ",LEN(original_text))),LEN(original_text)))

To understand this formula, you need to split it into parts:

In the first part, we have SUBSTITUTE(A1,B1,REPT(" ",LEN(A1))). It’s part of the formula that replaces the character (,) with the number of spaces equivalent to the length of the character.

You can see you have space between “Excel Champs” instead of the comma.

In the second part, =TRIM(RIGHT("Excel            Champs",LEN(A1))) which extracts the text from the right side of the text and then trims it to remove the extra space.

Different Instances of Character?

While working with data, you might have multiple instances of the character, and the above formula might not help to deal with that. In this case, you need to use the below formula.

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))<=1,"",MID(SUBSTITUTE(A1,B1,"^^",C1),FIND("^^",SUBSTITUTE(A1,B1,"^^",C1))+2,LEN(A1)))

With this formula, you need to specify the character to extract the text, and then the instance of the character starts from.

The above formula is complex but works like a wonder. The code for this formula is the SUBSTITUTE function that helps you to get the second or Nth instant of the character, and then you can replace that instant with a “^^” which further helps to extract text.

But you can also use the new function TEXTAFTER (OFFICE 365), which has all the options to get a text after a character without getting into a complex formula like the above.

You need to specify the character and the instance in the function.

Extract Text Before a Character

You can use the formula below to get the text before a character.

It is the exact formula we used while getting the text after a character. Instead of using the RIGHT, you need to use the LEFT. To get the text from the left (before).

=TRIM(LEFT(SUBSTITUTE(A2,B2,REPT(" ",LEN(A2))),LEN(A2)))

And this formula works exactly like that formula. Please refer to it to make it understandable.

Different Instances of Character

There are two ways to to get the text before (or after) a character.

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,B2,""))<=1,"",LEFT(SUBSTITUTE(A2,B2,"^^",C2),FIND("^^",SUBSTITUTE(A2,B2,"^^",C2))-1))

It can be used in any Excel version; you can specify the characters and the instance to get the text.

But if you use Excel (Office 365), you don’t need to use this complex formula. Instead, you can use TEXTBEFORE function.