Excel - data validation to ensure capitals

Excel - data validation to ensure capitals

Didn't find your answer?

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.

avatar
By Cantona1
20th Mar 2012 09:37

Any one could invalidate data validation set-up by either copy and paste, or copy and drag

Thanks (0)
Quack
By Constantly Confused
20th Mar 2012 09:55

Skip that then

Is it possible to have the cell compared to a list (there is a list of codes in the spreadsheet) and corrected to an entry on the list?  Presumably that would then make a123 into A123?

Thanks (0)
avatar
By leestevens
20th Mar 2012 09:56

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

 

 

 

 

Thanks (0)
Quack
By Constantly Confused
20th Mar 2012 10:07

Thanks

I like macros :)

Thanks (0)
avatar
By stevie
20th Mar 2012 13:11

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!

Thanks (0)
Simon Hurst
By Simon Hurst
20th Mar 2012 16:03

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.

Thanks (0)
Simon Hurst
By Simon Hurst
20th Mar 2012 16:09

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)

Thanks (0)
avatar
By SashaOJ
13th Apr 2012 15:53

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

Thanks (0)
pic
By jndavs
13th Apr 2012 19:07

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

Thanks (0)
pic
By jndavs
17th Apr 2012 08:42

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

Thanks (0)
avatar
By paulwakefield1
14th Apr 2012 09:23

Events are also

available in earlier versions back to Excel 97 which is when I believe they were first introduced.

Thanks (0)