Google-sheets – Google Sheets formula

formulasgoogle sheetsgoogle-forms

I'm using Google Sheets make a spreadsheet with the name in column a, the company in column b and in column f, I need their email.

I have 3 companies, and they have slightly different email formats. The first is firstname.secondname@compnayname.com, the second is the same, but the the third is firstname_secondname@companyname.com.

Is there a way I can format Google Sheets to fill in the emails for me with the addresses?

There are a few questions similar to this but they all have the names in first and second names separated, or are all in same email format?

Best Answer

You can use some If statements or maybe use the function =switch().

Here's a way to do it : enter image description here

The formula : 

=ArrayFormula(
SWITCH(C2:C13,
    F2,INDIRECT(I2)&G2&INDIRECT(J2)&"@"&F2&H2,
    F3,INDIRECT(I3)&G3&INDIRECT(J3)&"@"&F3&H3,
    F4,INDIRECT(I4)&G4&INDIRECT(J4)&"@"&F4&H4
    ))

If the company is AAA (the check in C2:C13) for example, then the formula will concatenate :
Column A, separator :either -,. or _, column B, @company name, the TLD

IE : firstname.secondname@companyname.TLD

Spreadsheet demo.

Related Topic