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

Yes, it works but the data is not in any standard format. That spreads the email address and phone numbers across 20+ columns

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.

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

Try this: On the Tool Bar,

Data

Text to ColumnsWhen the Wizard window pops up,

select: Fixed Width

Click NextIn 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

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.

OK, then try this: First Search & Replace the word

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

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

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

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.

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.

Yeah, but it's a one step, one column extraction of the email address. It was

almostfun to build.

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History