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