Excel 2010数据验证允许特定布局 [英] Excel 2010 Data Validation allow specific layout

问题描述

我想将数据验证应用于参考编号，以强制以下布局(理想情况下仅适用于大写字母):

I want to apply data validation to reference numbers, to force the following layout (ideally uppercase only):

XX_NNX-XX_NNN_NN-XXX

X =数字

N =字母

例如:12_AB1-23_ABC_AB-123

以下自定义公式允许除数字外的所有公式-对此有任何解决方法吗?

The following custom formula allows all of it except for numbers - is there any workaround for this?

我不想使用*，因为它允许的字符数超过我想要的.

I don't want to use * since it allows for more characters than I want.

= COUNTIF(A1，"?? _ ???-?? _ ??? _ ??? _ ???-???")

推荐答案

您可以选择 AND 为已编写的函数添加条件.例如以下将测试应为数字的位置.

You can choose AND to add a condition to the function you have already written. e.g. following shall test the positions which shall be numeric.

= AND(COUNTIF(A1，"?? _ ???-?? _ ??? _ ??? _ ???-???")，ISNUMBER((LEFT(A1,2)& MID(A1,6,1)& MID(A1,8,2)& RIGHT(A1,3))+ 0))

注意事项:这是一种非常基本的方法，如果您遇到涉及使用小数的情况，则可能需要进行一些调整.

Notes: This is fairly basic approach and may need some tweaks if you have cases involving usage of decimals etc.

编辑:您可以尝试以下方法来检查指定位置的大写字母.

You can try below approach for checking upper case text in specified positions.

= AND(COUNTIF(A1，"?? _ ???-?? _ ??? _ ??? _ ???-???")，ISNUMBER((LEFT(A1,2)& MID(A1,6,1)& MID(A1,8,2)& RIGHT(A1,3))+ 0)，INDEX(频率(-CODE(MID(MID(A1,4,2)& MID(A1,11,3)&MID(A1,15,2)，ROW($ A $ 1:$ A $ 7)，1))，{-91，-65,0})，2)= 7)

Edit2 :事实证明，这比我想象的要难.以下公式在DV中适用于单元格 A1 .

This turned out to be tougher than I had imagined. Following formula works in DV for cell A1.

= AND(COUNTIF(A1，"?? _ ???-?? _ ??? _ ??? _ ???-???")，ISNUMBER((LEFT(A1,2)& MID(A1,6,1)& MID(A1,8,2)& RIGHT(A1,3))+ 0)，MIN(FLOOR(CODE(MID(MID(AID，A1,4,2)& MID(A1，11,3)& MID(A1,15,2)，ROW($ A $ 1:$ A $ 7)，1))，65))= 65，MAX(CEILING(CODE(MID(MID(A1,4,4)，2)& MID(A1,11,3)& MID(A1,15,2)，ROW($ A $ 1:$ A $ 7)，1))，90))= 90)

但是由于一些怪异，Excel不允许我简单地将其粘贴.因此，我为此编写了一个小代码，将DV应用于单元格 A1 ，令人惊讶的是，它通过代码接受了相同的长公式.运行此代码之前，请确保删除单元格中的DV.

But due to some quirk, Excel won't let me simply paste it. So I wrote a small code for the same which applies DV to cell A1 and surprisingly it accepts that same long formula through code. Make sure that you delete DV in the cell before you run this code.

With Range("A1")
    .Value = "12_AB1-23_ADC_AZ-123"
    .Validation.Add xlValidateCustom, , , "=AND(COUNTIF(A1,""??_???-??_???_??-???""),ISNUMBER((LEFT(A1,2)&MID(A1,6,1)&MID(A1,8,2)&RIGHT(A1,3))+0),MIN(FLOOR(CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),65))=65,MAX(CEILING(CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),90))=90)"
End With

在那里，只需执行 copy>>即可在其他任何单元格中获取它.特殊粘贴>>验证.

为清楚起见，我使用Excel 2016.

For clarity, I use Excel 2016.

这篇关于Excel 2010数据验证允许特定布局的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！