![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
89 Responses to “Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]”
I'll just cheat and use text to columns instead 😉
I forgot to post my non cheating attemt:
=RIGHT(B3;LEN(B3)-FIND("@";B3;1))
Hi Chandoo,
Nice post - I had a question - how do you create the visuals? i.e. how do you get the Excel sheet and the drag and drop etc? What tools did you use to achieve this?
Ubique
=RIGHT(A1,FIND("@",A1)-2)
One more option to extract the domain name.
=REPLACE(B3,1,FIND("@",B3),"")
Regards
I found this solution to be the most accurate.
You might also want to add TRIM to the formula so that embedded get cleaned up.
=RIGHT(A1,LEN(A1)-FIND("@",A1))
=RIGHT(A1,FIND("@",A1)-1)
Same as John's except I don't think the -2 is necessary vs. -1.
Here is how I would extract the domain name...
=MID(A1,FIND("@",A1)+1,999)
Rick Rothstein
Thank you so much your Formula is worked,
How can i get contact us web pages, Could you please tell me.
Thanks Again
Suraj
By the way, for those who might be interested, you can isolate the username from the email address when it is embedded in other text (this also works for the stand-alone situation too) using this formula...
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1)," ",REPT(" ",99)),99))
You can isolate the domain name embedded in other text (this also works for the stand-alone situation too) using this formula...
=MID(A1,FIND("@",A1)+1,FIND(" ",A1&" ",FIND("@",A1))-FIND("@",A1)-1)
If there are more than one email address in the text, the above formulas return the username and domain name from the first one.
Hey Rick, thank you for this post.
I have tried tinkering with both yours and Elias' formulas in order to pull out multiple domains from a single cell but cant seem to figure it out. Is there formula to pull multiple domains from a single cell?
For instance I have bob@dcl.com, roger@gmail.com, and ryan@qq.com. How can I exclude the username while pulling out the 3 separate domains?
Thank you!
As with any solution to any problem there is always more than one way. I haven't check all the solutions posted prior to mine but in looking over some of them, I think the posters did not check the solution either.
I went with a combination of RIGHT and LEN to find out the total numbers of characters up to and including the "@" symbol then subtract that form the total number of characters in the cell. That result is used to determine the number of charcaters from the right to display as the qualified domain name.
=RIGHT(A1,LEN(A1)-LEN(RIGHT(A1,FIND("@",A1))))
This is based on the cell only containing a standalone email address. If the text was part of a larger text string or sentence, then a solution like Rick Rothstein's should be considered.
If there are more than one email address in the text and you want to extract the 2nd instance these formulas do the job.
To get the 2nd username
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(CHAR(160),SUBSTITUTE(A1,"@",CHAR(160),2))-1)," ",REPT(" ",99)),99))
To get the 2nd domain
=LEFT(REPLACE(A1,1,FIND(CHAR(160),SUBSTITUTE(A1,"@",CHAR(160),2)),""),FIND(" ",REPLACE(A1,1,FIND(CHAR(160),SUBSTITUTE(A1,"@",CHAR(160),2)),"")&" ")-1)
Regards.
Thank you Elias. What if there is more than one email address, they are different domains, and I want to extract each individual domain?
@Ubique72: Thanks, I use Camtasia recorder to make screencasts like above. It is a fine software. You can get a free trail download from here: http://www.techsmith.com/download/camtasiatrialthx.asp
@John & Drew: There is an error with your formulas. FIND() actually tells the position of a first text in next one. So using FIND alone with RIGHT wouldnt work for us. you need to combine this information with the length (total number of characters in the email address) to extract the domain.
One correct way to do this is like this =RIGHT(B3,LEN(B3)-FIND("@",B3))
@Scaffdog845... You can simplify the formula by using just one RIGHT, LEN and FIND like this: =RIGHT(B3,LEN(B3)-FIND("@",B3))
@Loranga, Eric, Rick & Elias: Good solutions...
Hi !
a manupulation of the above technique -For splitting the name/text(alphanumeric) (two words with a space between) in two separate coloumns:
Illustration:
Column A Column B Column C
anurag gupta anurag gupta
Formula in Coloumn B
=IFERROR(LEFT(A6,FIND(" ",A6)-1)," ")
Formula in Colomun C
=IFERROR(TRIM(MID(A6,FIND(" ",A6),50))," ")
Above is for 50 characters on right side of space , which can be customized.
Thanks a ton ! 2 chandoo ... I was looking for this for any no. of characters either side of space..
Anurag
@Anurag,
A couple of questions...
1) In both your formulas... are you sure you want to return a space character if the ISERROR function evaluates to an error condition? Most people would return the empty string (""... note, no space between the quote marks) for that condition.
2) For this formula...
=IFERROR(TRIM(MID(A6,FIND(" ",A6),50))," ")
why are you using the TRIM function to remove the space the FIND function stopped on at the beginning of the returned text? You can eliminate the function call (in general, the less function calls, the more efficient the formula) by just adding a value of one to the FIND value...
=IFERROR(MID(A6,FIND(" ",A6)+1,50),"")
3. In the same formula... I think you are aware of this, but just to make sure... you do know that you can use any large number in place of the 50, right? The general rule is to use a number that you know will be larger than the longest text string you plan to have returned to you... the MID function will not be confused if you specify a length in the 3rd argument that is longer than the actual text string.
Hi Rick !
Thanks for observation and suggestion !!!! nothing was intensional in both formula ! was just a try ...your suggestions well taken ....
Am novice and still learning ...
Thanks
I did just like Loranga.
Which way is the best?
Thanks Chandoo!
[...] and powerful. I hope this is useful to someone – the inspiration for this post came from this post at Chandoo.org. Pointy Haird Dilbert is easily my favorite as well as one of the most useful and entertaining [...]
Hi All. I need help to add 1st name, last name with @ domain. Please help.
@RZX... I think you will need to provide some more information in order for someone to be able to help you. Where are your first, last and domain names at? What do you want the final result to look like? If you can give an example or two for these questions, that might help us narrow down a solution for you.
@RZX... if the first name, last name and domain are in cells A1, B1 and C1 then write
=a1&"."&b1&"@"&c1 to generate email id like
firstname.lastname@domain
Hi, I am trying to extract the first name from the email id which is in this format: firstname.lastname@domain.com, and I used your formula to show =left(A2,find(".",A2)-1)
and ended up with no value. What am I doing wrong here?
@Dumbee1018: Welcome to Chandoo.org and thanks for your comments. The formula works alright for me. Can you give me some sample data so that I can verify?
chandoo the formula which you give is just a waste of time 🙁
we know this .
@Dumbee1018
When I copied the above I got an error
I retypes the Quote Symbols " manually and all was fixed
The Quote symbols neeed to be the vertical ones Shift ' not the angled ones
Try this to extract domain name "=TRIM(MID($A9,SEARCH("@",$A9,1)+1,500))" . I assume the domain name cannot be more than 500 characters long.
This should do for the user name "=TRIM(MID($A9,1,SEARCH("@",$A9,1)-1))"
how to get only last name, when the id is firstname.lastname@domain.com
@Daya
=TRIM(MID(A1,FIND(".",A1,1)+1,FIND("@",A1,1)-FIND(".",A1)-1))
~Vijay
Dear Gurus,
I have a problem, i have a specific word "XYZ" in a between a string of characters in different rows. Corresponding to these string rows i have numerical values. I have to add the numerical values on the occurrence of the specific word anywhere between the string. How do i do this addition? Any suggestions?
Awsum tuts man..it really help me a lot. Work Simplified !!!
Gr8 going.
I have a long list of emails that I need to add: @xxxxxxx.com to. How can I do this without merging cells? I know there's a way!
Alice,
If you have a list of usernames, use the &"@xxxxxxx.com". Assuming your list begins in A1 and contains "johndoe", use =A1&"@xxxxxxx.com" in B1. The result of the formula would be johndoe@xxxxxxxx.com. Using the fill handle on B1, drag down to the bottom of your list of usernames. If the list of usernames is contiguous, double-click on the fill handle to quickly do the same job. Select and copy the cells in column B, and do a special paste starting in C1 to convert the cells to values (Alt,E,S,V).
Is there a way to extract the user name to 2 other cells? For example John.Doe@company.com is in A1 and I need to have John populate in B1 (which is all first names) and Doe in C1(which is all last names). Another issue is my list has middle initials too, for instance Jane.G.Doe@company.com I just want Jane G om B1 but Doe in C1.
Hi Suave,
Welcome to chandoo.org and thanks for commenting.
To get the first name, in B1 write =LEFT(A1,FIND(".",A1, IF(SUBSTITUTE(A1,".","",3)=A1,1,FIND(".",A1)+1))-1)
To get last name, in C1 write =MID(A1,LEN(B1)+2,FIND("@",A1)-LEN(B1)-2)
This assumes that company.com has only one . (ex: company.com works, but not company.com.au needs a fix - change 3 to 4)
Hello Chando,
I'm using your forumula plus a slight modification for proper names but either way I use it, I still get a period before the middle initial:
Firstname.M.Lastname@somewhere.com
=PROPER(LEFT(E2,FIND(".",E2,IF(SUBSTITUTE(E2,".","",3)=E2,1,FIND(".",E2)+1))-1))
It will output: Firstname.M
I am a novice and I'm not sure what I need to change or adjust to make the period into a space. I would have thought Substitute would have done that.
Hi all,
i have a address list which contains centre name, email ids & telephonic contact in a cell, i want to delete email ids & telephonic contact in a cell..
kindly help me out
Thanks! I've been looking all over for this fix.
Hi Chandoo
First of all let me thank you for all the help that I get from your website.
Its really helpfull, specially for beginners like me.
I have one question..how would I extract text from cell with special characters. I know if all the cell has same special character that I can use the above formula but what if one cell contains "." other contains ":" so here I can't use the same formula and I don't want to manually go and change the formula as per the cells requirement in each cell.
Could you please email me the solution?
Thanks a lot.
chando,
How do i separate firstname and last name into b2 and c3 if my data is in A1 as firstname.lastname@mydomain.com
@Gene
Assuming your text is in B1
First name: =LEFT(B1,FIND(".",B1)-1)
Last name: =MID(B1,FIND(".",B1)+1,FIND("@",B1)-FIND(".",B1)-1)
Hi Hui, Thanks for the reply i get an error when i run the second formula for last name.
@Gene
Can you email me the file?
Email at bottom of: http://chandoo.org/wp/about-hui/
I am having the same issue with the last name formula for emails in the same format. What's the solution?
Thanks!
I have a colum of firstnames and last names , i need to make email addresses out of them in the following format , firstname.lastname@mydomain.com Can anyone help me with the best solution to accomplish this
Brilliant and clear post - saved me hours and thanks from afar! Legend
First time poster, here! I hope this makes sense to someone.
I'm baffled by my latest problem. I'm using almost the exact same formula in this article, but my delimiter is a comma. I'm trying to pull the last name only from a series of cells with lastname, firstname. The formula I'm using is LEFT(A1,(FIND(",",A1)-1)).
The formula works fine for that cell, but when I drag the formula down, the result in ALL cells is the same as the first one! If A1 has Smith, John, A2 has Doe, John, and A3 has Jackson, Don, all three will show Smith in the cells B1-B3!! I checked the formula in each cell and they are correct -- meaning the cell number changes to A2, A3 as it's dragged.
I restarted my PC and did it again hoping that would help, but got the same results. What's going on here!?! Can anyone help??
Hi Nancy,
It seems your Workbook has Calculation Options set to Manual. Please reset it to Automatic by going to Formulas Tab and then Selecting Automatic Under Calculation Options dropdown.
Hope this helps.
Not to brag but I created a [complex] formula that extracts the names from emails in a much more interesting way. See the examples:
john_doe@email.com >> my extract >> John Doe
john.doe@email.com >> my extract >> John Doe
john321doe@email.com >> my extract >> John Doe
Clever right? 😉
I'll be glad to share.
@Joana... that sounds clever. Please share.
Ok, here's my formula:
SUBSTITUTE(CONCATENATE(IF(ISERROR(SEARCH("-";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;".";"-");"_";"-");"0";"-");"9";"-");"8";"-");"7";"-");"6";"-");"5";"-");"4";"-");"3";"-");"2";"-");"1";"-"));0)=0;"";IF(ISERROR(SEARCH("-";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;".";"-");"_";"-");"0";"-");"9";"-");"8";"-");"7";"-");"6";"-");"5";"-");"4";"-");"3";"-");"2";"-");"1";"-"));0)=1;"";ESQUERDA(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;".";"-");"_";"-");"0";"-");"9";"-");"8";"-");"7";"-");"6";"-");"5";"-");"4";"-");"3";"-");"2";"-");"1";"-");ISERROR(SEARCH("-";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;".";"-");"_";"-");"0";"-");"9";"-");"8";"-");"7";"-");"6";"-");"5";"-");"4";"-");"3";"-");"2";"-");"1";"-"));0)-1)));" ";IF(ISERROR(SEARCH("-";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;".";"-");"_";"-");"0";"-");"9";"-");"8";"-");"7";"-");"6";"-");"5";"-");"4";"-");"3";"-");"2";"-");"1";"-"));0)=0;"";IF(ISERROR(SEARCH("-";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;".";"-");"_";"-");"0";"-");"9";"-");"8";"-");"7";"-");"6";"-");"5";"-");"4";"-");"3";"-");"2";"-");"1";"-"));0)=1;"";DIREITA(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;".";"-");"_";"-");"0";"-");"9";"-");"8";"-");"7";"-");"6";"-");"5";"-");"4";"-");"3";"-");"2";"-");"1";"-");NÚM.CARACT(C2)-ISERROR(SEARCH("-";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;".";"-");"_";"-");"0";"-");"9";"-");"8";"-");"7";"-");"6";"-");"5";"-");"4";"-");"3";"-");"2";"-");"1";"-"));0)))));"-";"")
It may look dauting, but it's quite simple. All it does is locate "_","." or any numbers and break the string in two.
Remember you still have to break the email in two strings like in the article, then you apply this formula.
If you want to go even farther you can capitalize the first letter of the names. 🙂
It is one very long formula. Kudos for coming up with this. I am sure you are a Jedi mind Yoda in previous life.
Here's a simplified version of Joana's technique (in English).
Given the following address in cell B3:
alex_1234567890.gomez@email.com
Use the following formula:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B3,FIND("@",B3)-1),"_"," "),"."," "),"1"," "),"2"," "),"3"," "),"4"," "),"5"," "),"6"," "),"7"," "),"8"," "),"9"," "),"0"," "))
with the result of alex gomez. From here it's a simple matter to isolate the first and last names if desired.
I don't really like having to use all the SUBSTITUTE()'s and having to specify which characters to replace with spaces. I'm still looking for a way to just replace all character codes except the ranges 65-90 and 97-127 with spaces and then trim without using VBA. Maybe could use an array structure?
Most of it was copy and paste into a single formula, but don't tell anyone 🙂
[…] you may know, we have an article on how to extract names from email addresses. 2 days ago, Joana commented on it […]
I wouldn't use a formula, i'd use vba
Split(Range("A1"), "@")(0)
and to remove the "_"
Replace(Split(Range("A1"), "@")(0), "_", " ")
This is a redo to fit the formula into your text box.
Here’s a simplified version of Joana’s technique (in English).
Given the following address in cell B3:
alex_1234567890.gomez@email.com
Use the following formula:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B3,FIND(“@”,B3)-1)
,”_”,” “),”.”,” “),”1?,” “),”2?,” “),”3?,” “),”4?,” “),”5?,” “),”6?,” “)
,”7?,” “),”8?,” “),”9?,” “),”0?,” “))
with the result of alex gomez. From here it’s a simple matter to isolate the first and last names if desired.
I don’t really like having to use all the SUBSTITUTE()’s and having to specify which characters to replace with spaces. I’m still looking for a way to just replace all character codes except the ranges 65-90 and 97-127 with spaces and then trim without using VBA. Maybe could use an array structure?
PS
As a trivial addition, if you want the names capitalized, for
alex_1234567890.gomez@email.com
just use
=PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT($B9,FIND("@",$B9)-1)
,"_"," "),"."," "),"1"," "),"2"," "),"3"," "),"4"," "),"5"," "),"6"," "),"7"," "),"8"," "),"9"," "),"0"," ")))
=RIGHT(A3,LEN(A3)-FIND("@",A3))
plz tell me how to split first and last name if they are combined with any character other than letters. E.G.Avnesh.chaudhary
vishal1mittal
akash#sharma
Prateek_bansal
plz help me..plz
This works nicely for me. Thanks for all the input that led me to this point.
It takes an email address in the firstname.lastname@domain.com format and returns it as Firstname Lastname
=PROPER(TRIM((SUBSTITUTE(LEFT(A1,FIND("@",A1)-1),"."," "))))
If you need to remove other characters, just add additional SUBSTITUTE conditions
I have to tell you, because it was driving me crazy, "eg" is latin for exempli gratia, which mean "for the sake of example" or "for example". There is no need to put "for eg" because that's like saying "for for example".
Hi,
Excellent post here. I was extracting first and last names from email addresses with the format "first.last@domain.com"
I altered your formula a bit to extract "first.last", copy and pasted those results as values, did a text to columns with the "." as the delimited character, used the "=Proper" function to fix the lowercase first and last names, copy and pasted those results as values again, and then brought them back to my main due diligence document with minimal fixes needed. Saved me a lot of time!
I was hoping to extract "first" in a column, then using an alteration of the formula to extract "last" in the next column, using "=Proper" to fix lowercase names, and finally copying and pasting as values. Any suggestions for this?
In relentless pursuit of Excel efficiency,
KC
Thanks very much chandoo, was quick andhelpful
Hi Everyone,
Nice Day !
All medical representative send their reports in 2 excel sheets,
1.sales Visit - with date,product,doctors list
2.Office Word
now i have to compare these 2 lists and make a new report that whether they have send report or not according to names and date.
if they send 01 mar, then i put yes
if they not send 02 mar, then i put no
can anyone tell me which formula can use to compare these 2 list and make it automatically "yes" and "no" according to medical rep names and dates.
Advanced Thanks for reply
I WANT AFTER '@' CHARACTER
Need help to extract the characters out without the dashes "-" in between. Sample cell value can be as below
MA-02-02
MA-02-03A
MA-11-03A
How do I extract them to show as below respectively?
MA0202
MA0203A
MA1103A
TQIA!
@Flying
=SUBSTITUTE(A1,"-","")
Dear Flying !
u can use =substitute("A1","-","") where a1 is the cell where u have the original text and this formula is to be placed in b1 to get the desired result...
Bye Venkat
+RIGHT(A8,FIND("@",A8)-2)
Dear Friends !
Can anybody explain the below formula for extracting email address from a text in a cell..step by step ...
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",LEN(A1))),LEN(A1)))
Thanks & Regards
Venkat
Alison.Mackinnon Hw cane remove the (.Mackinnon)
i want only first name in sheet, so please help and suggest formula
thanks
Bsingh
BSingh
=LEFT(A1,FIND(".",A1)-1)
Or if you have a table with many similar structured entries you could use Data, Text to Column, choose delimitered and set a . as the delimiter
to separate the two fields
how can i can remove dot at the end of the mail id there are mutliple
exmpl: krishna.er23@gmail.com.
@Krishna
=left(A1,Len(A1)-1)
Hi Chando,
Please find below my data. I am looking to extract "Dis" (the length of Dis may vary). Please help
ABC-Status-42311-Yes-Dis
@Abdul
How about:
=LEFT(A1,FIND(CHAR(135),SUBSTITUTE(A1,"-",CHAR(135),4))-1) Ctrl+Shift+Enter
Refer to : http://chandoo.org/wp/2012/05/17/formula-forensic-no-021/
for help with how that works
This is perfect!!! Thank you.
If we want the output "Dis".
=RIGHT(A1,LEN(A1)-FIND(CHAR(135),SUBSTITUTE(A1,"-",CHAR(135),4)))
Superb!!! The formula is working.. Thanks alot...
I am trying to replace email address with the word email. For Ex:
Cell A1 - I can be reached at bhatiahitesh@gmail.com
Result - I can be reached at email
I have to got the below results in the given form :
SHAILENDRA SINGH SHEKHAWAT
Jeewan Chandra Pandey
Desired form:
shailendra.shekhawat@xerionretail.com
jeewan.pandey@xerionretail.com
Please suggest me generic formula so that i can use to separate these.
Hi, all is going sound here and ofcourse every one is sharing data,
that's in fact excellent, keep up writing.
I have fun with, result in I found just what I used to be looking
for. You have ended my 4 day long hunt! God Bless you man. Have
a nice day. Bye
Need Custom Formula for same in power query which will work same as LEFT and FIND combo in excel
4.47 M
3.55 M
9.55 M
I need to extract 4.47 3.55 9.55 to use in Nested IF
Text.Start is an alternate of LEFT whats the alternate of FIND
=right(A1,len(A1)-find("@",A1))