How to capitalize any text in cell?

Byambadorj

New Member
Joined
Oct 22, 2015
Messages
30
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
One way is to format the cells with a capitals font such as Felix Titling.
 
Upvote 0
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).
Code:
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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
Code:
If Not c.HasFormula And Not IsDate(c.Value) Then c.Value = UCase(c.Value)
 
Last edited:
Upvote 0
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
Code:
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.
 
Last edited:
Upvote 0
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. :)
 
Last edited:
Upvote 0
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.:eeek:
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top