Excel:数据验证,如何强制用户输入 2 个字符长的字符串? [英] Excel : Data Validation, how to force the user to enter a string that is 2 char long?
问题描述
我想在最新的 Excel 中的单元格上添加一些数据验证.我想强制用户输入一个双字符长的字符串,第一个字符是一个数字,第二个字符是一个字母.
I would like to add some data validation on a cell in the latest Excel. I would like to force the user to enter a string that is two-char long, with the first char a digit, and the second char a letter.
例如
1m
2m
9w
8y
你会怎么做?
另外,如果输入的是小写字母,我想在输入完成后将其呈现为大写字母.
Also, if a lower case letter is entered, i'd like to render it an upper case letter when the input is finished.
推荐答案
仅第一部分(不需要VBA),可以使用数据验证:
Only for the first part (no VBA needed), you can use Data Validation:
- 选择要检查的单元格(例如
A1
) - 在功能区中,转到数据 > 数据验证
- 在
Allow:
中,选择 Custom - 在字段中,输入以下公式:
=IF(AND(LEN(A1)=2,ISNUMBER(VALUE(LEFT(A1,1))),ISTEXT(RIGHT(A1,1))),真,假)
- 在标签错误提示中,更改对话框以向用户解释他应该做什么,例如:
- Select the cell you want to check (
A1
for instance) - In the Ribbon, go to Data > Data Validation
- In the
Allow:
, choose Custom - In the field, put this formula:
=IF(AND(LEN(A1)=2,ISNUMBER(VALUE(LEFT(A1,1))),ISTEXT(RIGHT(A1,1))),TRUE,FALSE)
- In the tab Error Alert, change the dialog to explain to the user what he should do, something like:
您必须输入一个数字后跟一个字母.
You have to enter a number followed by a letter.
顺便说一句,您可以使用 UPPERCASE
测试添加检查.
Btw, you could add a check with an UPPERCASE
test.
也看看 brettj 对类似但优化的解决方案的回答
Also have a look at brettj's answer for a similar but optimized solution
这篇关于Excel:数据验证,如何强制用户输入 2 个字符长的字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!