Data Validation - all caps and no periods

[shelby55]

Hello

I'm currently using Excel 2010.

I have some cells for data entry where I don't want the users entering non-capital letters or periods. The current data validation I have on these cells is:

=EXACT(L63,UPPER(L63))

How can I edit this to also include when there are periods? Or better yet, how can I do it so that the entries change to putting in upper case and removing periods when they tab out of the cell?

Thanks very much.

 

[shelby55]

Hi

Any suggestions on this? Thanks.

 

[SkipVought]

I believe I'd use Events

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Row = 1 Then Exit Sub
        Application.EnableEvents = False
        If Not Intersect(.Cells, Cells(1, 1).EntireColumn) Is Nothing Then
            .Value = UCase(.Value)
            .Replace What:=".", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        End If
        Application.EnableEvents = True
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...my assumptions being that your data is in column A and data begins in row 2.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[firefytr]

You can still use Data Validation for this, just use an additional function test to see if there are any periods...

=(EXACT(L63,UPPER(L63)))*(LEN(L63)=LEN(SUBSTITUTE(L63,".","")))

Of course the trouble comes into play if you want a whole list of characters not allowed in cell entry. If that were the case, you could build your list and put it in a somewhat convoluted formula for your Data Validation like this...

=(EXACT(A1,UPPER(A1)))*(ISNA(LOOKUP(2,1/FIND(MID(".,;:",ROW(INDIRECT("1:"&LEN(A1))),1),A1))))

In this formula, you can see all of the characters in the MID() function which is inside the quotation marks, ".,;:", those are the characters not allowed.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci