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) |
|---|---|
| Result | 26 |
Step 2: To find the length of the text string
| Formula | =LEN(A1) |
|---|---|
| Result | 46 |
Step 3: To find the number of letters after special character
| Formula | =LEN(A1)-FIND(".",A1) |
|---|---|
| Result | 20 |
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)))