![Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]](https://chandoo.org/img/f/username-from-email-id-excel-formula.png)
Today we will learn to use Excel’s LEFT and FIND formulas. But what fun it is to learn a new formula on a Tuesday?
So, we will actually learn to use these formulas to solve the problem: “extract the username from an email ID”
How is an email ID structured?
Any email ID contains 2 parts – user name and domain name.
For eg. in my email id – chandoo.d@gmail.com – chandoo.d is user name and gmail.com is domain.
So how do we get the user name out?
As you can see, username always starts at left and goes up to the symbol “@”. So, If we write a formula to fetch all the characters up to “@” symbol, it will get us the user name.
This is where LEFT() and FIND() formulas enter the scene.
What does Excel LEFT formula do?
Excel LEFT formula will let you cut a portion of text from left. For eg. =LEFT("Long",2) will give you Lo. (syntax and examples)
So, to get the email username, we need to get all the letters in the left of email ID up to the location of “@” symbol. And how do we find the position of a symbol in a text?
We use FIND formula.
FIND formula gives the location of one text in another. For eg. =FIND("do", "chandoo") will give us 5 (the location of “do” in “chandoo”).
FIND will throw an error (#VALUE!) if the text you are trying to find is not available. For eg. =FIND("peace", "world") will throw #VALUE!
Armed with these 2 formulas, now let us get that user name out of email ID
Assuming cell A1 has the email id, the formula for getting user name is =LEFT(A1,FIND("@",A1)-1)
We have to use -1 as find actually tells the position of “@” and we need all the letters up to “@”, but not “@”.
This is how it works:
Your homework:
- How would you extract the domain out of email ID? (Hint: there is a right formula for everything)
Use comments to write your answers. Don’t cheat.
Learn more excel formulas:
- 51 Excel Formulas in Plain English – Syntax, Examples and Explanation
- Excel Formulas & Working with Text
- Excel Formula Examples & Tutorials
- Learn Excel Formulas using my e-book – it is in a fun format & easy to understand
10 Responses to “Multiple Find Replace with Power Query List.Accumulate()”
Note: The text-formula above miss a -1. The video is correct.
😀 sorry, I made the exact same mistake as you did - initially - in the video. {0..3} is one thing, and Table.RowCount(replacements) -->3 items ={0,1,2} is another thing.
1st question : you've created a new column to put the replacements in. how ca we replace in the original column without creating a new one ?
2nd question : how can we replace the value in the entire cell and not only the text (Using ReplaceValue instead of text.Replace) ?
Thanks you in advance
Chandoo, I would be very interested to have your answers in both Yassine's questions!
Thank you for sharing,
Vassilis
Thank you for this! I was just doing an assignment where I was having to replace words with other words in my Excel sheet. We are starting with the basics in my class, so I know I don't have a huge list that I would need to find and replace, but this is something that could be useful down the road for me!
Hi
This is nearly perfect for my needs thank you, however I would like just the "replace" to be the result if possible please, I have tried in vain adjusting the formula without success.
Thank you in advance
Thank you! Awesome tip, and very flexible, too.
My find/replace values were in non-adjacent columns of a table containing a bunch of other data. Worked prefectly and I am now a tiny bit less clueless.
Hi!!!
I have the same question that Yassine did.
I need to replace the values in the same column. I don't want to create a new column e then have to remove the old column.
How could I could that?
That's a real fun article. It inspired me to delve deeper into the topic of List.Accumulate. I can see how the function works, but it takes an additional step to imagine how one can use it for more complex applications.
Your example also made it into my article, together with some other use-cases. Would be great to get your opinion on which other areas you would include in the article.
Let me drop the link to the page so others can too find it for further reading: https://gorilla.bi/power-query/list-accumulate/
Expression.Error: The name 'replacements' wasn't recognized. Make sure it's spelled correctly.