Copying email address from excel data

June 24, 2009 at 15:34:41
Specs: Windows Vista
I have excel lists of membership but the phone numbers and email addresses are in the same column. Is there a way to extract the email address and move it to a new column

See More: Copying email address from excel data

Report •


#1
June 24, 2009 at 15:42:08
Try using Excel's Text to columns

Report •

#2
June 24, 2009 at 18:03:14
Yes, it works but the data is not in any standard format. That spreads the email address and phone numbers across 20+ columns

Report •

#3
June 24, 2009 at 18:56:24
You might be using the wrong delimiters or maybe you could try Fixed Width.

Other than that, there might be functions that can do it for you.

Post some examples of what you've got and we'll see if we can help.


Report •

Related Solutions

#4
June 25, 2009 at 04:52:45
H: (111) 222-3333 W: (111) 222-3333 Cel: (111) 222-3333 W Fax: (111) 222-3333 Email: abcd@edfg.com
-----------------------------------------------------------------------------------
H: (444) 555-6666 W: (444) 555-6666 Email: hijk@lmno.com W Fax: (444) 555-6666


Example = 1 column and 2 rows of data and I am trying to extract the email address and place it into a single column without cut and paste 5000 times. The text to columns feature worked but spread the addresses haphazardly across 20+ columns. That leads to a new question, I want the email in column A and they are spread out B-Z. How do I get them all in column A without C&P. I tried "=B1-Z1" in A1 and got "#VALUE".


Report •

#5
June 25, 2009 at 07:46:10
Try this:

On the Tool Bar,
Data
Text to Columns

When the Wizard window pops up,
select: Fixed Width
Click Next

In the next window, the lines with the little arrowheads, which are separating each section, can be moved right or left to any position you like.
Just place your mouse pointer on the line, left click & hold, and drag the line to where you want it.

MIKE

http://www.skeptic.com/


Report •

#6
June 25, 2009 at 08:15:05
Thanks Mike - that works for 1 row at a time. My email addresses are variedly mixed with phone numbers (in the beginning, middle and end of the data) and there are 5000 of them, so the text to columns will spread them out over 20+ columns. Don't care what happens to the phone numbers but I need the email addresses to all end up in a single column regardless of where they were in the original column.

Report •

#7
June 25, 2009 at 09:08:43
OK, then try this:

First Search & Replace the word Email: with a slash character.

Then Search & Replace the word .com with .com/
(.com with a slash)

Then use this formula to cut out what's between the two slash marks.

=MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)

MIKE

http://www.skeptic.com/


Report •

#8
June 25, 2009 at 09:13:04
Minor modification.
When you replace the word email, there may be a remaining space between the slash and the beginning of the actual email address so use =TRIM to remove it:

=TRIM(MID(A3,FIND("/",A3)+1,FIND("/",A3,FIND("/",A3)+1)-FIND("/",A3)-1))

MIKE

http://www.skeptic.com/


Report •

#9
June 25, 2009 at 09:37:35
Mike - you are my new hero. That works perfectly once adjusted for all the .net, .org, .us. You saved me a lot of time and long nights. Thanks everyone for all the help.

Report •

#10
June 25, 2009 at 09:59:07
Glad I could help.

MIKE

http://www.skeptic.com/


Report •

#11
June 25, 2009 at 14:43:54
I know I'm jumping in late here, but if the OP is still around, try this:

=IF(ISERROR(MID(A1,FIND(" ",A1,FIND("E",A1))+1,FIND(" ",A1,FIND("@",A1))-FIND(" ",A1,FIND("E",A1)))),MID(A1,FIND(" ",A1,FIND("E",A1))+1,256),MID(A1,FIND(" ",A1,FIND("E",A1))+1,FIND(" ",A1,FIND("@",A1))-FIND(" ",A1,FIND("E",A1))))

It seemed to work for the examples given as well as when I moved the email addresses to various locations in the cell. I also tried some .us and .au addresses and it seemed to work.

I'd like to know if it works against 5000 different pieces of data.


Report •

#12
June 25, 2009 at 16:25:15
Just looking at that formula makes my head hurt. :-)

MIKE

http://www.skeptic.com/


Report •

#13
June 25, 2009 at 18:42:24
Yeah, but it's a one step, one column extraction of the email address.

It was almost fun to build.


Report •


Ask Question