How Can I Use Data Validation On Single or Multiple Email Addresses That are Semi-colon Separated?-CodePudding

I found this that seems exactly what I'm looking for if it worked with ";" instead of "," but I can't get it to work. Is it possible? https://stackoverflow.com/a/48551211/20499116

The data I'm working with gives me an email column with somewhere between 1 to 10 email separated by a Semi-colons.

Really appreciate any help.

I tried amending

=if(countif(arrayformula(ISEMAIL(split(A1,","))),False)>0,False,TRUE)

=if(countif(arrayformula(ISEMAIL(split(A1,";"))),False)>0,False,TRUE)

CodePudding user response：

As far as I have tested it, you could consider some things:

If there may be spaces before or after the semi-colon you could add TRIM to remove them:

=if(countif(arrayformula(ISEMAIL(TRIM(split(A1,";")))),False)>0,False,TRUE)

If there is the possibility that there are two or more semicolons together (empty options) or even a final semi-colon, you can substract the amount of empty results:

=if(countif(arrayformula(ISEMAIL(TRIM(split(A1,";")))),FALSE)-countif(arrayformula(TRIM(split(A1,";",TRUE,TRUE))),"")>0,FALSE,TRUE)

Does this work for you?

CodePudding user response：

try:

=IF(COUNTIF(INDEX(ISEMAIL(SPLIT(A1; ";"))); FALSE)>0); FALSE; TRUE)