How to capitalize any text in cell?

[Byambadorj]

Hello all,

How do you make a cell or whole selected area/spreadsheet automatically capitalize any inserted text to CAPITAL letters?

Thank you. Much appreciated in advance.

 

[footoo]

One way is to format the cells with a capitals font such as Felix Titling.

 

[Peter_SSs]

Two other ways. Assuming the range you want this to happen in is C3:K20

A) With vba. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Range("C3:K20"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      c.Value = UCase(c.Value)
    Next c
    Application.EnableEvents = True
  End If
End Sub

B) With Data Validation - Force the user to enter upper case in the first place.
- Select C3:K20
- Data ribbon tab -> Data Validation -> Data Validation... -> Allow: Custom -> Formula: =EXACT(C3,UPPER(C3)) -> OK

Note that
- the vba is defeated if the user does not enable macros
- the DV is defeated if the user copy/pastes form another area
. so choose what suits you best if considering these options.

 

[footoo]

One way is to format the cells with a capitals font such as Felix Titling.

In addition to cells that contain constants, this works also for the result of cells that contain formulas and for dates.

Re VBA and Data Validation, in addition to the limitations pointed out by PeterSSs :
- Data Validation doesn't work for dates or formulas
- The macro per post #3 will convert formulas to value only, and will convert dates to text in the system's short date format

 

[Peter_SSs]

In addition to cells that contain constants, this works also for the result of cells that contain formulas ..

I'm not so sure. If we take the OP's request literally, it was to capitalise "inserted text", not capitalise "everything". They may want a formula result to include lower case (though I expect neither formulas or dates are related to the area the OP is wanting to control). Also, the OP might prefer to use a particular font that is not a capitals only font.

- Data Validation doesn't work for dates or formulas

I would expect that the DV would only be applied to cells where text input was expected.

- The macro per post #3 will convert formulas to value only, and will convert dates to text in the system's short date format

As for DV, but in any case the code could easily exclude formulas and dates

If Not c.HasFormula And Not IsDate(c.Value) Then c.Value = UCase(c.Value)

 

[footoo]

I'm not so sure. If we take the OP's request literally, it was to capitalise "inserted text", not capitalise "everything". They may want a formula result to include lower case (though I expect neither formulas or dates are related to the area the OP is wanting to control). Also, the OP might prefer to use a particular font that is not a capitals only font.

I would expect that the DV would only be applied to cells where text input was expected.

As for DV, but in any case the code could easily exclude formulas and dates

If Not c.HasFormula And Not IsDate(c.Value) Then c.Value = UCase(c.Value)

Just pointing out the limitations.
The main point is that formatting with a capitals font covers all possibilities, not just the OP's requirement - and it's simpler.
There are many capitals fonts available - it would be unusual for the font type to be so critical that a suitable capitals font couldn't be found.
Note: The revised code doesn't capitalize formula results and dates - it just ignores such cells.

 

[Peter_SSs]

Note: The revised code doesn't capitalize formula results and dates - it just ignores such cells.

Correct, the OP said they wanted to deal with "inserted text".
I suspect we are discussing situations that the OP is not likely to encounter. My guess is that the OP has a range that the they (or the user of the sheet) is going to be entering text into and they want that to be in capitals. Probably any of the suggestions will succeed.

 

[footoo]

My suggestion is that if it can be done by cell formatting with an acceptable font, why bother with DV or VBA?
For example, if a range of cells containing dates had to be automatically displayed as DD MMM YY, this would be done by formatting the cells - not by a worksheet_change procedure.