To extract text before a special character, you need to find the location of the special character in the text, then use Left Function.
Formula:
=LEFT(A1, FIND(".",A1)-1)
Copy the formula and replace "A1" with the cell name with the text you would like to extract.
Example:
To extract characters before special character "." in cell A1 "How to Extract Text before. a Special Character".
The result returns the text "How to Extract Text before".
Explanations:
Step 1: To find the location of the special character
| Formula | =FIND(".",A1) |
|---|---|
| Result | 27 |
Step 2: Pick up the letters before the special character
| Formula | =LEFT(A1, FIND(".",A1)-1) |
|---|---|
| Result | How to Extract Text before |
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(LEFT(A1, FIND(".",A1)-1), A1)
Or make the cells without the special character blank with the following formula.
=IFERROR(LEFT(A1, FIND(".",A1)-1), "")
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 before the character "-".
=LEFT(A1, FIND("-",A1)-1)