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

This formula is great! My question is if I have a list of names and some of them have special characters and some don't, how can I write this to work for both scenarios?

Thank you visiting us, i think you split into group and for those do not have special ones, you might try to use the spaces between, for example: to extract

first word: https://excelnotes.com/how-to-extract-the-first-word/

first two words: https://excelnotes.com/how-to-extract-the-first-two-words/

first three words: https://excelnotes.com/how-to-extract-the-first-three-words/

first four words: https://excelnotes.com/how-to-extract-the-first-four-words/

Thank you!

how do I extract a text before the @ in a mailaddress (not knowing the length of the text beforehand).

ex. mail linda@gmail.com .. and I only want to see in the excel field "linda" .. but not all mails in the list have the same amount of characters.

Can you please try the formula: =LEFT(A1,FIND("@",A1)-1), where A1 is the first cell with the email address. Thank you!

Hi!!!

I have an email address, for example hello.world123@e-mail.com and I want to extract first 3 characters and 3 characters before symbol @, for example I should get hel123.

Second exampe: m2atos.doga55@usa.com - the result should be: m2aa55

The first part is easy - =CONCATENATE(LEFT(A1;3)) - I get first 3 characters.

The second part - I can get all the characters before the symbol @ - =LEFT(A1; FIND("@";A1)-1)

But I cannot combine them or modify just to get first 3 characters and 3 characters before symbol @.

You almost got it:)

Pick up the right 3 from the second part, which is =right(left(A1,FIND("@",A1)-1),3), then combine with the first left 3 characters =left(A1,3)

You can use CONCAT/CONCATENATE, or &

=CONCAT(left(A1,3),right(left(A1,FIND("@",A1)-1),3))

=CONCATENATE(left(A1,3),right(left(A1,FIND("@",A1)-1),3))

=left(A1,3)&right(left(A1,FIND("@",A1)-1),3)

CONCAT function only for newer version, hope this helps, thank you!

use a formula to find the Product code. It is the first 3 capital letters preceding the dash.

ASADIHXGJ-11053wNIeXuYGl9h354

How will XGJ will appear?

yes, please try the formula =RIGHT(LEFT(A1,FIND("-",A1)-1),3), where A1 is the first cell with the codes. Thank you!

Thanks you very much for the help.. it works