Pulling email address from irrelevant data within cell and inserting in adjacent column

[SouthFloridaBadger]

I have a limited background in VB and am looking for an easy way to sort through a column containing upwards of 800 cells. Each cell has a bunch of irrelevant information mixed around the email address I'm looking to pull from the cell.

Example (cell A1):

Sample message used here to show info|
More sample information that I do not need|
You get the point, I don't need this information|
[email protected] 8282013 random information here

All I need in cell B1 would be: [email protected]

All help would be greatly appreciated!

[GC Excel]

Hi and welcome to the forum,
This function will return the email address found in the cell using a simplified RegEx expression :

Please Login or Register  to view this content.

[SouthFloridaBadger]

I should clarify, when I said I have limited background in VB I meant I have almost no background in VB.

I've entered in the code and when I run it, it prompts me to name it which I will name 'test' for simplicity. I then create it which then prompts a window for Module1 starting with:

Sub test()

End Sub

What should I put in the Sub?

Sorry, I'm a noob

[GC Excel]

You need to copy the code in a module. To do so.
- from excel, ALT + F11 to open the VBE editor
- ALT, I, M to insert a new module
- copy the code there (in the white section).

The code I provided is a function. Go in B1 and type =FindEmail(A1) and if there is an email address in A1 you will get the result.
Copy the formula down.

Instead of a Function, the code could be re-written as a procedure (Sub) to loop through all cells and write the result back.

[jindon]

http://www.mrexcel.com/forum/excel-q...il-cell-3.html

Please Login or Register  to view this content.

[SouthFloridaBadger]

I tried that and I got #VALUE!. As far as the attachment goes, I am unable to open it on my current machine. Not sure if its because I'm on Excel 2000 but I have a feeling that it is.

[jindon]

File attached to my previous post.

[SouthFloridaBadger]

Thank, this is a big improvement. There may be some fine tuning needed however. I'll use an example from a cell with names changed below.

Cell A1:
This is the message at sample.server.server.net.
I'm sorry to inform you that the message below could not be delivered.
When delivery was attempted, the following error was returned.
<[email protected]>: host 00.00.000.000

Cell B1 [=FindEmail(A1)]:
[email protected]>: host 00.00.000.000

Unfortunately there is no pattern of how cells in column A contain the email; in some the email is between <> and others between '' or have no separators at all.

Make no mistake, this is a big improve and if this is as good as it can get, I'll be happy with that!

[jindon]

Can you upload a small sample workbook showing possible patterns of your data?

[SouthFloridaBadger]

I'll try, there's sensitive information contained within though so I have to change some names and whatnot.

[jindon]

Just delete "^" from the pattern...

Please Login or Register  to view this content.

[SouthFloridaBadger]

Not every cell contains emails and that is fine. There are over 800 of these so I can't say for sure if they are similar to these obviously. These are all essentially bad email addresses from a mass emailing.

[SouthFloridaBadger]

Just delete "^" from the pattern...

Please Login or Register  to view this content.

This works perfectly! Thank you very much!

[jindon]

Just let you know that it will not recognize a string like

abc(xyz)@abc.com

as invalid character used.

[SouthFloridaBadger]

Just let you know that it will not recognize a string like

abc(xyz)@abc.com

as invalid character used.

I don't think we will run into too many errors with that but I'll be double checking all the cells where an email isn't generated. Thank you so much for the help, it is greatly appreciated!