Extract or Delete only specific email addresses from a cell (i.e., *.gmail.com)

[symaxf]

I have a 3 column spreadsheet with headers that has 500 rows.
Col C contains multiple email addresses in each cell.
I need to remove ONLY the *@gmail.com email addresses but keep all of the others.
Each email address is seperated by a semicolon (";").

Example
Col A = Company
Col B = Status
Col C = eMail addresss (i.e., joe.black@companyname.com; john.smith@gmail.com; jane.doe@companyname.com)

After the code is run I would like Col C to be joe.black@companyname.com; jane.doe@companyname.com (Cols A & B remain the same).

Any help would be greatly appreciated!

[xladept]

Try this:

Please Login or Register  to view this content.

[symaxf]

Thx xladept! I ran the code and it failed on the following line:

Please Login or Register  to view this content.

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[symaxf]

Sample file attached. Note: The sample contains 3 records but the actual data varies from month to month upwards of 500+ records. I would like the macro to be able to handle a variable range. Thx again!

[xladept]

Well, it didn't fail for me but this new code allows for the carriage return that I hadn't expected:

Please Login or Register  to view this content.

[symaxf]

The actual file I'm running the macro on also has the following conditions, although rare:

-Blank spaces after a carriage return
-Email addresses not always separated by a semicolon
-Some cells in Col C are blank

I'm trying to attach the actual file here but it's not letting me (via 'Go Advanced').

[symaxf]

xladept, Thx again for all of your help on this and for pointing me in the right direction. This is the final code that was generated using your code as a model. Take care my friend!

Please Login or Register  to view this content.

[xladept]

You're welcome!