Forum Discussion

Copper Contributor

Nov 20, 2020

calculation creating unique email address

Can anyone help me with a formula please. I have about 8 columns of email address for an individual (some are blank and some are the same email) and want to create a formula in the last column to cr...

Formulas and Functions

Bennadeau

Iron Contributor

Nov 20, 2020

HiHelen15

You can use =Textjoin

=TEXTJOIN("; ",1,B2:I2)

the first part "; " is simply a delimiter.

the 1 is set to ignore blank cells

while B2:I2 is the range where your email address are found.

Bennadeau
Iron Contributor
Nov 20, 2020
Helen15
Forgot to select only the unique email address.
Try this instead:
=TEXTJOIN("; ",1,UNIQUE(B2:I2,1,0))
- Helen15
  Copper Contributor
  Nov 20, 2020
  The first one works a treat but for some reason when I put unique in the formula it doesn't work for me, saying #name? . Any ideas?
  Thank you every so much in replying ☺️
  - SergeiBaklan
    Diamond Contributor
    Nov 20, 2020
    Helen15
    
    Please check this https://techcommunity.microsoft.com/t5/excel/removing-duplicates-when-using-textjoin/m-p/188950 thread. If you have no UNIQUE() other variants of formula are here.