MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Need help with code to find data within a cell

  Asked By: Kiral    Date: Mar 09    Category: MS Office    Views: 1078

I have a range of cells that contain email addresses in the format of
SMTP:first.last@..., however, this address may be at the
beginning, end or middle of the cell contents with a lot of "junk" on
either side. For example it could be

SMTP:first.last@...%123ert%lpoku0987990% OR
%598ert%llejfufdu0987990%SMTP:first.last@... OR

I can do a find/replace looking for SMTP:*.rib and replace the entire
email address with something else, but I really need to keep the
address, and delete all the other "junk".

Any ideas would be greatly appreciated!



13 Answers Found

Answer #1    Answered By: Ulfah Hashmi     Answered On: Mar 09

If the addresses  don't contain any spaces, I'd replace  SMTP with space SMTP and
.rib with .rib space, then use "text to columns" in the Data Menu to split up
the text into the bits you do and don't want. (If you have spaces you'll need to
find a character that doesn't appear in the strings of text.)You'll need to
insert a couple of columns to receive the split text, but they can be deleted
once you have the information you want.

Answer #2    Answered By: Adaulfo Fischer     Answered On: Mar 09

I hope I'm not helping a spammer harvest email  addresses..
Assuming ".rib" comes directly at the end  of each email address:
If your string containing the email address  is in C30 then
should extract one email per line as long as '.rib' always comes after

Answer #3    Answered By: Nicholas Wells     Answered On: Mar 09

Where I work we receive an Outlook address  book from another site to
import into ours for a consolidated address book. Unfortunately the
file we recieve has "junk" in with the email  addresses. Since it is
in .csv/excel format, I'm trying to help
sort it out for the folks to do the importing.. no spammer email
address harvesting issue here.

Anyway, when I tried your formula, I get an error #VALUE.. trying to
figure out why... but this looks like it might be promising.

Answer #4    Answered By: Lily Brown     Answered On: Mar 09

The most likely reason for a #VALUE result is that either or both
'.rib' and 'SMTP:' are absent. Also happens if '.rib' comes before 'SMTP:'

Does '.rib' come immediately after each email  address?

Answer #5    Answered By: Umaiza Hashmi     Answered On: Mar 09

For my test, I used the formula on this data  below, which is all run
together in one cell. I found my mistake, had .RIB in my formula in
caps, oops! However I have found that in some of my data .rib IS in
caps - .RIB, so I guess I'll need to get this uniform before I run the
Find function.

The FIND function did a nice job and returned the info in the middle  -
john.smith@... -which is great! But I still need the SMTP: at
the beginning and .rib at the end.

MS:SILLMAIL/SILLPOX1/smithjb%CCMAIL:smith, john at SILL

Answer #6    Answered By: Barachias Levi     Answered On: Mar 09

you can use the wildcard find  and replace  facility in textpad or any other
text editor to do it .

copy the entire column to textpad ..

in the find what box type: (.*)(SMTP:.*.rib)(.*)
in the replace with box type: \2

check the regular expression checkbox and replace all..

Answer #7    Answered By: Naomi Lee     Answered On: Mar 09

I never knew that functionality was there - good stuff!
I couldn't do this in WordPad or NotePad but I could do it in Word. I
had a few difficulties with the dots but got this to work (after, as
John Marshall suggests, converting all upper case .RIB to .rib):

in the Find what box:

in the Replace with box:

Answer #8    Answered By: Bathilda Schmidt     Answered On: Mar 09

I've got it! Thanks Pascal! I just needed to adjust a couple of
numbers to get the SMTP and .rib to show up in the results! yeah!
Looks like this


Answer #9    Answered By: Joyce Edwards     Answered On: Mar 09

I read a lot of messages but don't post too often. Pasxal
has it right most of the time. On this one, I just tweaked his
function a bit and came up with this:


It seems to work, although I do see you have put in some constants
to get the job done.

Answer #10    Answered By: Adel Fischer     Answered On: Mar 09

You have most likely finished it all by now. I just had a suggestion for
the ".rib" and ".RIB" issue. By using an IF and ISERR statements, you
should be able to capture anyone of the two in the same formula.



Answer #11    Answered By: Teresa Rogers     Answered On: Mar 09

I'm coming in to this late in the game, but I would seriously consider
switching the string to upper case BEFORE processing.

Answer #12    Answered By: Tammy Sanders     Answered On: Mar 09

To handle the possibility of .rib being .RIB, .Rib, .rIB etc. etc. you
can use the UPPER function thus:


Answer #13    Answered By: Hilma Miller     Answered On: Mar 09

Until you recommended using UPPER, I was doing a find/replace
to change to all same case, but this function works like a CHARM!!

Thanks so much, I put the entire formula together in a Macro that does
a few other find/replace actions on other columns in the spreadsheet
and then attached the macro to a toolbar button to be used as
new "messy" files come in.

Didn't find what you were looking for? Find more on Need help with code to find data within a cell Or get search suggestion and latest updates.