Google Sheets formula

Question

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?

Answer 1

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

Here's a way to do it :

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.