Excel : Data Validation, how to force the user to

问题:

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.

e.g.

1m
2m
9w
8y

How would you do that?

Also, if a lower case letter is entered, i'd like to render it an upper case letter when the input is finished.

回答1:

Only for the first part (no VBA needed), you can use Data Validation:

• 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.

Btw, you could add a check with an UPPERCASE test.

[EDIT] Also have a look at brettj's answer for a similar but optimized solution

回答2:

Select range, then in the Data menu, choose Validation.
Allow: choose Text length, Minimum = 2, maximum = 2

回答3:

For the first part you will need a formula such as this for Data Validation

=AND(LEN(A1)=2,ISNUMBER(VALUE(LEFT(A1,1))),CODE(RIGHT(LOWER(A1),1))>=97,CODE(RIGHT(LOWER(A1),1))<=122)

This part runs a case insensitive test for a letter between a to z CODE(RIGHT(LOWER(A1),1))>=97,CODE(RIGHT(LOWER(A1),1))<=122)

ISTEXT(RIGHT(A1,1))) will validate characters other than a-z to being text as it is a string test, even numbers will be allowed. This is why the VALUE portion of (VALUE(LEFT(A1,1))) is needed to force a numeric evaluation of the first position in the string

回答4:

Jerome,

In case you want to resolve this without VBA, you could

• use a (hidden) sheet where you enter all 260 combinations 0A through 9Z in one column.
• Give this range a name e.g. "MyList".
• go to your input sheet (e.g. A1) and use data validation, selecting the "list" option.
• In the source field, click [F3] to select "MyList".

If you need this data in uppercase, you can use the formula =UPPER(A1) where ever you use the input.

回答5:

Here's an overkill method, literally just for fun (don't downvote me for having a little fun with VBA - this is actually useful to know how to do). It adds a data validation list to a range that only allows a number followed by an uppercase letter. I do this by quite literally adding every single combination allowable :) Of course you don't have to SELECT form the list, but if you try to type something that's not allowed, you get the buzzer :)

Sub AddValidation()

Dim cell As Range
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
Dim alphabet As String, numbers As String
alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
numbers = "1234567890"
Dim validList As String

For i = 1 To Len(numbers)
    For j = 1 To Len(alphabet)
        dict.Add Mid$(numbers, i, 1) & Mid$(alphabet, j, 1), 1
    Next
Next

validList = Join(dict.keys, ", ")

'Example using B1:B10
With Range("B1:B10").Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=validList
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = "Invalid data entered"
    .ShowInput = True
    .ShowError = True
End With

End Sub

回答6:

If you can use VBA then try to catch event Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "You just changed " & Target.Address
    ' your logic goes here
End Sub