Site icon ExcelNotes

How to Extract Text after a Special Character

To extract text after a special character, you need to find the location of the special character in the text, then use Right function.

Formula:

Copy the formula and replace "A1" with the cell name that contains the text you would like to extract.

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

Example:

To extract characters after the special character "." in cell A1 "How to Extract Text after. a Special Character".

The result returns the text "a Special Character".

Explanations:

Step 1: To find the location of the special charater

Formula=FIND(".",A1)
Result26

Step 2: To find the length of the text string

Formula=LEN(A1)
Result46

Step 3: To find the number of letters after special character

Formula=LEN(A1)-FIND(".",A1)
Result20

Step 4: To extract the letters after special character

Formula =RIGHT(A1, LEN(A1)-FIND(".",A1))
Result a Special Character

What if not all cells have the special character?

If only part of your data contain the special character, you may use the IFERROR function to have all the text for those do not have the special characters.

=IFERROR(RIGHT(A1,LEN(A1)-FIND(".",A1)), A1)

Or make the cells without the special character blank with the following formula.

=IFERROR(RIGHT(A1,LEN(A1)-FIND(".",A1)), "")

What if the special character is different?

Please change the special character in the formula if you need to extract text with another character. For example, please change "." to "" in the formula if you need to extract text after character "-".

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

Please combine with Trim function to get rid of the extra spaces.

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

Download Example

Exit mobile version