![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
6 Responses to “Make VBA String Comparisons Case In-sensitive [Quick Tip]”
Another way to test if Target.Value equal a string constant without regard to letter casing is to use the StrCmp function...
If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
That's a cool way to compare. i just converted my values to strings and used the above code to compare. worked nicely
Thanks!
In case that option just needs to be used for a single comparison, you could use
If InStr(1, "yes", Target.Value, vbTextCompare) Then
'do something
End If
as well.
Nice tip, thanks! I never even thought to think there might be an easier way.
Regarding Chronology of VB in general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).
Today StrComp() and InStr() function offers a more local way to compare, fully object, thus more consistent with object programming (even if VB is still interpreted).
My only question here is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This will lead to a real nightmare and probably a big nasty mess to debug.
By the way, congrats for you Millions/month visits 🙂
This is nice article.
I used these examples to help my understanding. Even Instr is similar to Find but it can be case sensitive and also case insensitive.
Hope the examples below help.
Public Sub CaseSensitive2()
If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub CaseSensitive()
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub NotCaseSensitive()
'doing alot of case insensitive searching and whatnot, you can put Option Compare Text
If InStr(1, "Look in this string", "look", vbTextCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub