Solved Extracting email addresses from multiple cells

September 30, 2015 at 07:21:19
Specs: Macintosh
I am trying to extract email addresses only from one column that contains various types of contact data, including email addresses, and put them into a column. I can't come up with the formula to extra them. There are no commas or spaces or any type of conformity that would allow me to use

See More: Extracting email addresses from multiple cells

Report •

✔ Best Answer
October 2, 2015 at 16:23:03
Where are the E-Mail addresses?
All I see are Company Names & Company Address

Let's try it this way.
If ALL your Data looks like this:

                 A
1) John Doe Company
2) 1442 Wanwright Rd, Denver, Co 80201
3) www.johndoe.com
4) John Smith Company
5) 122 Wright Rd, Aurora, Co 80012
6) www.JohnSmith.com
7) John Williams Company
8) 442 Main St, Sterling, Co 80751
9) www.JohnWilliams.com

Note there is one line with company name,
one line with company address and
one line with an E-Mail
Then a Formula solution should be possible to break apart the pieces, because everything is uniform and predictable.

BUT, if your Data looks like this:

                 A
1) John Doe Company
2) 1442 Wanwright Rd, Denver, Co 80201
3) www.johndoe.com
4) John Smith Company
5) 122 Wright Rd, Aurora, Co 80012
6) John Williams Company    <<< No E-Mail Address
7) 442 Main St, Sterling, Co 80751
8) www.JohnWilliams.com

Note there is one line for Company Name
one line for Company Address
BUT some do not have an e-mail.
Then a Formula solution will probably not work and you will need a VBA Macro to try and sort out which line contains a Company Name or Address and which line contains an E-Mail.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
September 30, 2015 at 07:27:35
can you possibly give a few examples of how the text appear in the columns? obviously subsitute the original email addresses with false ones. Are there spaces before and after the email addresses?

example

uwegiwgfu hello@hotmail.com qugeiwgiueifuy

if so then a VBA solution is possible (only works on A1 so far)

Just as an example - tested very very quickly as it is not time for me to leave work... woohoo.... if you give us a few examples maybe we can help

Sub test()
    
    Dim sString As String
    
    sString = Range("A1").Value
    
    first = InStr(1, sString, " ", vbTextCompare)
    Secondee = InStr(first + 1, sString, " ", vbTextCompare)
    
    thelen = Secondee - first
    
    MsgBox Mid(sString, first, thelen)
    
End Sub


message edited by AlwaysWillingToLearn


Report •

#2
September 30, 2015 at 12:22:21
Here is a Formula solution that requires only a space around the email address:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",LEN(A1))),LEN(A1)))

BUT

You said in your post:
There are no commas or spaces or any type of conformity that would allow me to use

Without some type of delimiter, it is going to be extremely difficult,
if not impossible for any single Formula or VBA Macro to extract the data.

If it is only a single long string of text
Where does the email address begin?
Where does the email address end?

MIKE

http://www.skeptic.com/


Report •

#3
September 30, 2015 at 12:48:12
Ahh damn mmcconaghy i skim read the OP's post and didnt see the bit about no spaces.. Hopefully they will provide some examples, but i agree, without a delimiter it wont be easy.

I really wish i understood formulas as well as you do, thats amazing what you provided..

message edited by AlwaysWillingToLearn


Report •

Related Solutions

#4
September 30, 2015 at 13:44:21
AlwaysWillingToLearn
I really wish i understood formulas as well as you do

I'll trade you for your understanding of VBA. :-)

MIKE

http://www.skeptic.com/


Report •

#5
September 30, 2015 at 19:38:03
I should clarify:

So i did misspeak when I said there are no spaces or or commas -it's set up like this:
All of these are in one column; each has its separate cell, but not all of the companies has a website listing - does this make sense?

John Doe Company
1442 Wanwright Rd, Denver, Co 80201
www.johndoe.com
I want to be able to put each in its respective column: Company, Address, Website.
There are over 1,000.

Thanks for your help!


Report •

#6
October 1, 2015 at 05:53:39
We will need more then one example of your data,
please read this HOW-TO which explains the use of the < PRE >
tags:

http://www.computing.net/howtos/sho...

Post a representative sample of what your data actually looks
like
using Column Letters and Row Numbers.

MIKE

http://www.skeptic.com/


Report •

#7
October 2, 2015 at 14:01:54
OK - had a delay of a day. So this is how they look on the spreadsheet: They are all in one column (#s here represent row #s, 1, 2, 3, etc)

COLUMN A
1 Mark Services, LLC
2 Oklahoma City, OK 73127-9712
3 Netmark
4 Kansas City, MO 91685
5 ABCNet, Inc.
6 Huntington Beach, CA 92683
7 Martin Communications, LLC
8 Baltimore, MD 68342-4094

Is this clearer?


Report •

#8
October 2, 2015 at 16:23:03
✔ Best Answer
Where are the E-Mail addresses?
All I see are Company Names & Company Address

Let's try it this way.
If ALL your Data looks like this:

                 A
1) John Doe Company
2) 1442 Wanwright Rd, Denver, Co 80201
3) www.johndoe.com
4) John Smith Company
5) 122 Wright Rd, Aurora, Co 80012
6) www.JohnSmith.com
7) John Williams Company
8) 442 Main St, Sterling, Co 80751
9) www.JohnWilliams.com

Note there is one line with company name,
one line with company address and
one line with an E-Mail
Then a Formula solution should be possible to break apart the pieces, because everything is uniform and predictable.

BUT, if your Data looks like this:

                 A
1) John Doe Company
2) 1442 Wanwright Rd, Denver, Co 80201
3) www.johndoe.com
4) John Smith Company
5) 122 Wright Rd, Aurora, Co 80012
6) John Williams Company    <<< No E-Mail Address
7) 442 Main St, Sterling, Co 80751
8) www.JohnWilliams.com

Note there is one line for Company Name
one line for Company Address
BUT some do not have an e-mail.
Then a Formula solution will probably not work and you will need a VBA Macro to try and sort out which line contains a Company Name or Address and which line contains an E-Mail.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#9
October 7, 2015 at 12:41:45
Thank you all very much for your help. MIke, your were right. I had to write a macro.

Report •

Ask Question