How to Extract Text before a Special Character

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)
Result27

Step 2: Pick up the letters before the special character

Formula=LEFT(A1, FIND(".",A1)-1)
ResultHow 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)

Download Example

9 Comments

  1. Robin M August 6, 2020
  2. David August 6, 2020
  3. Linda September 14, 2020
  4. David September 14, 2020
  5. Alfred June 15, 2021
    • David June 15, 2021
  6. Angelo August 24, 2021
    • David August 24, 2021
  7. Angelo August 24, 2021

Leave a Reply