VBA To Extract Email Address From Text

[dunndealpr]

hey all! been working with these codes a couple of days. Rick's most recent 'getemailaddress' code results in #NAME? straight down the line for me.
Excel Fox's most recent 'extractemail' code works, but the email address themselves are not reflected in the formula fields. Instead of seeing the email address in the formula field up top I see the formula itself. Is this normal?

[Rick Rothstein]

hey all! been working with these codes a couple of days. Rick's most recent 'getemailaddress' code results in #NAME? straight down the line for me.

Just so you know, my code works fine, even against the email addresses in the file you posted in Message #36 (I just tested it). If you are getting a #NAME? error, it is because you did one of two things wrong... either you put the code in the wrong place (it needs to go in a general Module, the same kind of module that macros go in) or you spelled the function name incorrectly when you used it in your formula. I will say, though, that my function (and I would guess most other functions) will return the wrong text for the first email address in your posted file, namely this one...

"thanks for the email man hit me at moneymankp@yahoo.com.LOOKING FOR MANAGEMENT!!!"

My function will return moneymankp@yahoo.com.LOOKING as the email address, not moneymankp@yahoo.com which is what you would be wanting it to. Notice that the quoted text above also assumes moneymankp@yahoo.com.LOOKING is the email address (it should be highlighted in blue and underlined)... that is because an email address can have more than one dot after the @ sign and because LOOKING are all valid characters for use in an email address.

Excel Fox's most recent 'extractemail' code works, but the email address themselves are not reflected in the formula fields. Instead of seeing the email address in the formula field up top I see the formula itself. Is this normal?

It sounds like you put the formula in a cell formatted as TEXT. Try changing the cell format to General and then re-enter the formula.

[dunndealpr]

Just so you know, my code works fine, even against the email addresses in the file you posted in Message #36 (I just tested it). If you are getting a #NAME? error, it is because you did one of two things wrong... either you put the code in the wrong place (it needs to go in a general Module, the same kind of module that macros go in) or you spelled the function name incorrectly when you used it in your formula. I will say, though, that my function (and I would guess most other functions) will return the wrong text for the first email address in your posted file, namely this one...

"thanks for the email man hit me at moneymankp@yahoo.com.LOOKING FOR MANAGEMENT!!!"

My function will return moneymankp@yahoo.com.LOOKING as the email address, not moneymankp@yahoo.com which is what you would be wanting it to. Notice that the quoted text above also assumes moneymankp@yahoo.com.LOOKING is the email address (it should be highlighted in blue and underlined)... that is because an email address can have more than one dot after the @ sign and because LOOKING are all valid characters for use in an email address.



It sounds like you put the formula in a cell formatted as TEXT. Try changing the cell format to General and then re-enter the formula.

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. My only option has been to save the file as a csv, but that also removes all kinds of highlighting that I have on the files. Do you have any other ideas about how I can fix this problem?

Thanks again for your help with all this.

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