We have a column on a google sheet with cells like this:
username1|username2|username3|....
where the "|" character separates usernames.
I want to automatically create a column where it converts that into emails like this:
[email protected]; [email protected]; [email protected]; ...
I've tried using =SPLIT and then putting another formula 6 rows down (since it puts each username in its own cell in the next column I left space) that uses CONCATENATE to add the "@gmail.com; "
=if(not(isblank(D2)), SPLIT(D2, "|",true,TRUE), "")
=CONCATENATE(M2,if(not(isblank(M2)), "@wccnet.edu;
",""),N2,if(not(isblank(N2)), "@wccnet.edu; ",""),O2,if(not(isblank(O2)),
"@wccnet.edu; ",""),P2,if(not(isblank(P2)), "@wccnet.edu;
",""),Q2,if(not(isblank(Q2)), "@wccnet.edu; ",""),R2,if(not(isblank(R2)),
"@wccnet.edu; ",""))
My problem with this is I figure there is a one-formula way to do this I'm just not aware of. I ran into an issue where some formulas calling on the data in the first row would not work because it's treating it like the formula, not the result. I also want this to ignore empty cells so I can drag the formula down a bunch of rows and have it just be blank until the usernames are entered.
The end goal is to be able to copy+paste a bunch of cells at once into Outlook to send a mass email based on this spreadsheet. If there's a totally different way to do that with the way usernames are entered I'm all ears for that too. We just have to enter it that way for another process we do first, before the emails.
