Extracting e-mail addresses from Excel cells

Microsoft Office excel 2007 - upgrade
October 14, 2010 at 21:01:04
Specs: Windows XP, AMD 4 GB
This is a slight variation on a problem I saw solved on this board about a year ago. I tried to add to that thread, but it's too old.

I have a 2300+ row x 1 column Excel spreadsheet of e-mail addresses. Most of them have a text name and the e-mail address, ie: firsname lastname [username@domain.com]. I need to strip out everything but the e-mail addresses themselves. Most of the e-mail addresses have [brackets] around them, so I just need to either extract the e-mail from within the brackets or delete the brackets and everything outside of them. I don't know what command does that in Excel. Any guidance would be much appreciated. Thanks!

See More: Extracting e-mail addresses from Excel cells

Report •

October 15, 2010 at 05:17:35
How about this:

=MID(A1, FIND("[",A1)+1, FIND("]",A1)-FIND("[",A1)-1)

MID(text, start_num, num_chars)

The start_num is determined by FIND("[",A1)+1 which returns the location of the first character after the [

num_chars is determine by FIND("]",A1)-FIND("[",A1)-1 which returns the number of characters between the [ ] based on the location of the [ ] in the referenced cell.

You could then do an Edit...Copy...PasteSpecial values.

Another option is to do a Data...Text to columns...Delimited on the [

This will return username@domain.com]

The do an Edit...Replace ] with nothing, Replace all.

Either way, it's a 2 step process.

Report •

October 15, 2010 at 11:29:37
Version two worked great!! Thanks!

Report •

Related Solutions

Ask Question