Creating Email Addresses for Blank Cells

rempowersports · Nov 16, 2011

I have a spreadsheet that has 4 columns -

Company Name, First Name, Last Name, Email Address

The first 3 are complete. the 4th, Email Addresses has blank cells.

Two issues: How do I create a valid email address for the blank cells using the information in the cell above? Also, is it possible to do this when the email address format changes from company to company? For Example:

<table border="0" cellpadding="0" cellspacing="0" width="565"><colgroup><col style="mso-width-source:userset;mso-width-alt:7314;width:150pt" width="200"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:7058;width:145pt" width="193"> </colgroup><tbody><tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl63" style="height:12.0pt;width:150pt" height="16" width="200">3D Systems Corporation</td> <td class="xl63" style="width:57pt" width="76">Chad</td> <td class="xl63" style="width:72pt" width="96">Hull</td> <td class="xl63" style="width:145pt" width="193">hullc@3dsystems.com</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl63" style="height:12.0pt" height="16">3D Systems Corporation</td> <td class="xl63">Andrea</td> <td class="xl63">Johns</td> <td class="xl63">
</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl63" style="height:12.0pt" height="16">3D Systems Corporation</td> <td class="xl63">Annie</td> <td class="xl63">Reichert</td> <td class="xl63">reicherta@3dsystems.com</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl63" style="height:12.0pt" height="16">A G Industries, Inc</td> <td class="xl63">David</td> <td class="xl63">Anderson</td> <td class="xl63">david.anderson@amgreetings.com</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl63" style="height:12.0pt" height="16">A G Industries, Inc</td> <td class="xl63">Joel</td> <td class="xl63">Boyle</td> <td class="xl63">
</td> </tr> </tbody></table>

I have a list of about 20,000 that has about 35% blank email cells that I need to fill with the correct 'local-part' and the correct '@domain'.

Thanks!

bensonsearch · Nov 16, 2011

Hi, You could use a formula that does this, adds cell c then first letter of cell b then @cell 1.

But that wont work 100% if the rules are different for any company. otherwise if its only a few companies you could write a macro to do the same thing with an IF statement in it.

Their are some formula wizards here that could make the formula work

bensonsearch · Nov 16, 2011

an example of a start would be:

=CONCATENATE(C2,LEFT(B2,1))&"@hotmail.com"

rempowersports · Nov 17, 2011

Thanks. I've been going the long way around mountain by doing things separately like using;
 ="@"&MID(A1,FIND("@",A1)+1,LEN(A1)-FIND("@",A1)) - to extract the ‘@’ and everything to the right

Then, using;

=LEFT(MID(A1,FIND("@",A1)+1,20),FIND(".",MID(A1,FIND("@",A1)+1,20))-1) - To pull the company name out of the email address

Then, using;

=IF(ISNUMBER(FIND(".",LEFT(F1,FIND("@",F1)))),LEFT(F1,FIND(".",F1)-1),LEFT(F1,FIND("@",F1)-1)) - To pull everything in front of the “@” symbol

Of course, the problem is, now I have to recombine to create addresses in another column ie =A1&B1&c1. So, depending on how many different local-part variations in the email strings there are, I might end up with 4 or 5 columns where only 1 address actually matches the correct string for that company.

Not sure where to go from there or how to circumnavigate all of that and use a "look-up to the cell above and pull the domain out and then add in the correct local part". (telling the formula to only do this when it encounters a blank cell so the cells with actual email addresses in them remain untouched) (also not sure how to tell it to determine the correct local part combination - that may be a huge, multi IF/Then formula)

Thanks

bensonsearch · Nov 17, 2011

going beyond my example I couldnt really say. as the example you gave us has verying amounts of data to make the company name. You could join all your formulas together in one big mother of a formula but thats too large a task for me sorry

Creating Email Addresses for Blank Cells

rempowersports

New Member

Excel Facts

bensonsearch

Well-known Member

bensonsearch

Well-known Member

rempowersports

New Member

bensonsearch

Well-known Member

Similar threads

Forum statistics

Share this page

Creating Email Addresses for Blank Cells

rempowersports

New Member

Excel Facts

bensonsearch

Well-known Member

bensonsearch

Well-known Member

rempowersports

New Member

bensonsearch

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock