Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]

Posted on January 19th, 2010 in Learn Excel - 87 comments

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:

• How would you extract the domain out of email ID? (Hint: there is a right formula for everything)

 Best Sales Dashboards, as Voted by You [Visualization Challenge #2 – Winners] Dr. Scroll-bar Mortgage Calculator or: Why you should not be borning and use form controls
 Written by Chandoo Tags: email, Excel 101, excel basics, find, left(), Microsoft Excel Formulas, screencasts, spreadsheets, text processing Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

87 Responses to “Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]”

1. Loranga says:

I'll just cheat and use text to columns instead π

2. Loranga says:

I forgot to post my non cheating attemt:
=RIGHT(B3;LEN(B3)-FIND("@";B3;1))

3. Ubique72 says:

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

4. John says:

=RIGHT(A1,FIND("@",A1)-2)

5. Elias says:

One more option to extract the domain name.

=REPLACE(B3,1,FIND("@",B3),"")

Regards

• Ray Sparks says:

I found this solution to be the most accurate.

6. Dale says:

You might also want to add TRIM to the formula so that embedded get cleaned up.

7. ericlind says:

=RIGHT(A1,LEN(A1)-FIND("@",A1))

8. Drew says:

=RIGHT(A1,FIND("@",A1)-1)

Same as John's except I don't think the -2 is necessary vs. -1.

9. Rick Rothstein (MVP - Excel) says:

Here is how I would extract the domain name...

=MID(A1,FIND("@",A1)+1,999)

• Suraj Singh says:

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

10. Rick Rothstein (MVP - Excel) says:

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.

• Cory says:

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!

11. Scaffdog845 says:

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.

12. Elias says:

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.

• Cory says:

Thank you Elias. What if there is more than one email address, they are different domains, and I want to extract each individual domain?

13. Chandoo says:

@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...

14. Anurag G says:

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

15. Rick Rothstein (MVP - Excel) says:

@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.

16. Anurag G says:

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

17. Victor says:

I did just like Loranga.
Which way is the best?

Thanks Chandoo!

18. [...] 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 [...]

19. RZX says:

Hi All. I need help to add 1st name, last name with @ domain. Please help.

20. Rick Rothstein (MVP - Excel) says:

@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.

21. Chandoo says:

@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

22. dumbee1018 says:

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?

23. Chandoo says:

@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?

• sdf says:

chandoo the formula which you give is just a waste of time π
we know this .

24. Hui... says:

@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

25. Niyi Adeyinka says:

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

26. daya says:

how to get only last name, when the id is firstname.lastname@domain.com

27. Vijay Sharma says:

@Daya

=TRIM(MID(A1,FIND(".",A1,1)+1,FIND("@",A1,1)-FIND(".",A1)-1))

~Vijay

28. Rahul says:

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?

29. Hrishit says:

Awsum tuts man..it really help me a lot. Work Simplified !!!
Gr8 going.

30. Alice says:

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!

31. Glen says:

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

32. Suave says:

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.

• Chandoo says:

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)

• Toxygene says:

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
Β

34. Ca says:

Thanks!Β  I've been looking all over for this fix.

35. Azaz says:

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.

36. gene says:

chando,
How do i separate firstname and last name into b2 and c3 if my data is in A1 as firstname.lastname@mydomain.com

• Hui... says:

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

• gene says:

Hi Hui, Thanks for the reply i get an error when i run the second formula for last name.

• Hui... says:

@Gene
Can you email me the file?
Email at bottom of: http://chandoo.org/wp/about-hui/

• Colette says:

I am having the same issue with the last name formula for emails in the same format. What's the solution?

Thanks!

37. gene says:

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

38. Jason says:

Brilliant and clear post - saved me hours and thanks from afar! Legend

39. Nancy says:

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??

• Amathya says:

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.

40. Joana Pereira says:

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.

• Chandoo says:

@Joana... that sounds clever. Please share.

41. 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. π

• Chandoo says:

It is one very long formula. Kudos for coming up with this. I am sure you are a Jedi mind Yoda in previous life.

• Ron Wallace says:

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 π

42. […] you may know, we have an article on how to extract names from email addresses. 2 days ago, Joana commented on it […]

43. Gary says:

I wouldn't use a formula, i'd use vba
Split(Range("A1"), "@")(0)

and to remove the "_"
Replace(Split(Range("A1"), "@")(0), "_", " ")

44. Ron Wallace says:

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?

• Ron Wallace says:

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"," ")))

45. subburaj says:

=RIGHT(A3,LEN(A3)-FIND("@",A3))

46. Avnesh Chaudhary says:

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

47. Steve says:

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

48. Paul says:

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".

49. KC says:

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

50. Mohammed Sufian says:

Thanks very much chandoo, was quick andhelpful

51. Bharath says:

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.

52. PANKAJ says:

I WANT AFTER '@' CHARACTER

53. flying says:

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!

• Hui... says:

@Flying
=SUBSTITUTE(A1,"-","")

• Venkat says:

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

54. Prasanna says:

+RIGHT(A8,FIND("@",A8)-2)

55. Venkat says:

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

56. BSingh says:

Alison.Mackinnon Hw cane remove the (.Mackinnon)
i want only first name in sheet, so please help and suggest formula
thanks
Bsingh

• Hui... says:

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

57. krishna says:

how can i can remove dot at the end of the mail id there are mutliple
exmpl: krishna.er23@gmail.com.

58. Abdul says:

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

• Hui... says:

@Abdul
=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

• abdul says:

This is perfect!!! Thank you.
If we want the output "Dis".

• Hui... says:

=RIGHT(A1,LEN(A1)-FIND(CHAR(135),SUBSTITUTE(A1,"-",CHAR(135),4)))

• abdul says:

Superb!!! The formula is working.. Thanks alot...

59. Hitesh says:

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

60. Atul Bihari says:

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.

61. website design says:

Hi, all is going sound here and ofcourse every one is sharing data,
that's in fact excellent, keep up writing.

62. Y2 Online says:

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

 Best Sales Dashboards, as Voted by You [Visualization Challenge #2 – Winners] Dr. Scroll-bar Mortgage Calculator or: Why you should not be borning and use form controls