如何在逗号分隔的单个或多个电子邮件地址上使用数据验证? [英] How Can I Use Data Validation On Single or Multiple Email Addresses That are Comma Separated?

问题描述

我正在寻找用于单元格数据验证规则的自定义公式.我需要用户能够在单个电子邮件地址中写东西，而=IsEmail()对此非常有用.但是，我还需要它们能够列出在同一单元格中用逗号分隔的多个电子邮件地址.

I'm looking for a custom formula for a cell data validation rule. I need users to be able to write in a single e-mail address, and =IsEmail() works great for that. However, I also need them to have the ability to list multiple e-mail addresses that are comma separated in the same cell.

例如email1 @ gmail.com，email2 @ otherdomain.net

Ex. [e-mail],[e-mail]

是否有附录可以进行这种验证以使其起作用?

Is there an addendum that I can do this type of validation to get it to work?

任何人都能提供的帮助/建议将不胜感激.

Any help/advice you all could provide would be greatly appreciated.

谢谢！

推荐答案

您可以使用以下命令检查特定单元格中所有用逗号分隔的电子邮件是否为有效的电子邮件地址，在这种情况下为A1单元格

You can use the following to check if all comma separated emails in a particular cell is valid email address, in this case, cell A1

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

函数split(A1,",")将所有用逗号分隔的电子邮件拆分为电子邮件数组，并且arrayFormula()将各个值提供给isEmail()函数.它返回TRUE或FALSE值的数组.使用countif()对该数组进行计数以检查它们中的任何一个是否具有值False.如果是，则if()返回False，表明其中一封电子邮件是错误的.其他将返回True，表明所有输入的电子邮件均有效.

The function split(A1,",") split all comma separated emails into an array of emails and arrayFormula() feeds individual values to the isEmail() function. Which returns a array of TRUE or FALSE values. This array is counted to check if any of them have the value False using countif(). If yes then the if() returns False suggesting one of the emails is wrong. Else will return True suggesting all the entered emails are valid.

这篇关于如何在逗号分隔的单个或多个电子邮件地址上使用数据验证?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！