VBA To Extract Email Address From Text

[Rick Rothstein]

Hey Rick, this workaround for the cell formats has not been working for me. In the actual field I see the email address, but in the formula field I'll see something to the effect of =extractemail(F2035), and selecting the General cell format both before and after applying the formula does not change this.

The Formula Bar (think about its name) shows the formula in the cell, the cell shows what the formula evaluates to.... this is standand Excel functionality. The only way the Formula Bar will show what the cell displays is if the cell contains a non-formula value (that is, a constant value that was typed in or copy/pasted in). If you want the Formula Bar to show the same thing as the cell, select all the cells you want this to happen for, then press CTRL+C to copy the cells into the Clipboard, then with the same cells selected, right-click the selection and choose "Paste Special" from the popup menu that appears and select the Values option button... after you do that and then click OK, all the formulas will be converted to the constant values they evaluated to. NOTE though, you will have then lost the formulas.

[dunndealpr]

Thanks Rick, that worked. The issue was that when I would ctrl-x a row then ctrl-v it somewhere else, the email address your formula found for me would turn into #NAME?, along with every other email address in that column.

[Manfred]

Thanks for sharing, i have the same problem so i will try with these steps. However i will be looking for other theme to find more information.

[LittleGirl]

Hi,

im doing an intern ship here and found after a few hours your piece of code which works quite good. But its is so complex that i have no chance to change it to my needs.

I have data which i have to prepare. In this data there are a lot of customer infos like phone number, date, email, adress, names i have to get rid of.

Is there a possibility to change the code a bit. I just have to find aout if there is an EMail and replace it with "E-MAil" instead of taking it out. But i need no real verification. Sometimes the E-Mail adresses are invalid.

if found following scenarios which have to be found and replaced:

1. xxx@xxx.xx
2. @xxxx.xx
3. xxxxx@
4. @

Thx a lot in advance!
Julia