I have a tracker. People putting lower case letters in codes is not cool, so I want to make it so my spreadsheet either rejects them, or better yet, corrects them.
I've found the 'data validation' button, but it doesn't seem to have 'ensure capitals' as an option.
Replies (11)
Please login or register to join the discussion.
Any one could invalidate data validation set-up by either copy and paste, or copy and drag
If you don't mind using macros
If you don't mind using a macro then you could use this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "" Then Exit Sub
If Target.Column = 1 Then ' 1 = column A, 2 = B etc
Target.Value = UCase(Target.Value)
End If
End Sub
What this will do is, convert anything entered into a row in Column A into uppercase automatically.
If you are au fait with macros you could extend this to a range of columns, e.g. Target.Column>=1 And Target.Column<=5 ... would operate on columns A - E.
I couldn't work out how to do it (not quickly anyway) using data validation.
Hope that helps...
asap utilities
ASAP is an add-in for Excel with lots of useful commands including the ability to change all text in a selected area to uppercase.
It has so many invaluable functions that I recently actually paid for the fully licensed version rather than the free version!
Data validation - limit to list
Although it doesn't get over the fact that Data Validation is far from tamper-proof, there is an option in Data Validation to look up, and limit to, values from a list. If you already have the codes in a list in your workbook then that would be worth a try (copy and paste issue permitting). If you turn your list into an Excel 2003 list or Excel 2007,2010 table before you link data validation to it, then the list in data validation should adjust to include items added to the original list.
Or...
If you really wanted to create a data validation criterion that checked for the first character being a capital letter, you could use the custom option and a formula such as (assuming that J2 is the current cell):
=AND(CODE(LEFT(J2,1))>=65,CODE(LEFT(J2,1))<=90)
UPPER?
Assuming that the incorrect entry "a123" is in A1, you could convert in (say) cell B1, just by using =UPPER(A1). This will generate the answer "A123" in cell B1.
It will convert all lower case to upper case quite easily and a nice formula to use for non-macro motivated excel users....
Data validation
These solutions all correct the error after the deed is done.
If you want to use data validation to enforce uppercase as the data is input, try using the EXACT() function in the formula box - something like
=EXACT(A1,UPPER(A1))
The EXACT() function is case sensitive
If you do not mind using VBA
If you are using a newish version of Excel (2007+) you can use events:
In the code below, every time a cell/range is changed, a Worksheet_Change event is fired. Target is the cell that changed.
If Target is is within the range A1 to C50 its value will be converted to uppercase.
------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:C50")) Is Nothing Then
Target.Value = UCase(Target.Value)
End If
Application.EnableEvents = True
End Sub
Events are also
available in earlier versions back to Excel 97 which is when I believe they were first introduced.