Problem:
In column A I have a list of email addresses, I want to put these into column B but in the same cell I want to add another line with a new email address – based on everything before the domain of old email address.
So basically I want to substitute everything with the original text string with my new text string.
Solution:
I want the following result to appear from a standard list of email addresses in column A. So when finished column B should appear as the below:
To do this we use the formula:
=A1 & ” ” & SUBSTITUTE(A1,”domain”,”newcompany”)
I then want to add a prefix to the new email address so that it appears as below:
To do this we edit the formula as below:
=A1 & CHAR(10) & “new_” & SUBSTITUTE(A1,”domain”,”newcompany”)
Lastly we need to click wrap text, so that the email addresses appear on different lines.
We can then edit the formula to only apply to blank cells:
=IF(A1 <>””,(A1 & CHAR(10) & “new_” & SUBSTITUTE(A1,”domain.com”,”newcompany.com”)),””)
Essentially everything highlighted between the brackets is where we could put any formula and this would only apply to blank cells
