Is it possible to pre-format a column of cells as "text" such that any
entered text will automatically be converted to UPPER CASE?

For example, entering lower "x" will be 'forced' by the formatting to
automatically appear as cap "X".

This would be comparable to database software that I use wherein I can
assign a "field input mask" to a data entry field that "forces" all text
entries to UPPER CASE.

Thanks,

John

Hi John,

Yes, it is possible to pre-format a column of cells as "text" and set it to automatically convert any entered text to UPPER CASE. Here's how you can do it:

1. Select the column of cells that you want to format.
2. Right-click on the selected cells and choose "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, click on the "Number" tab.
4. Select "Text" from the Category list.
5. Click on the "Custom" option in the Type list.
6. In the "Type" field, enter the following code:
   
   
   Formula:

   @ 

7. Click "OK" to close the dialog box.

Now, any text entered into the selected cells will automatically be converted to UPPER CASE. You can test this by typing in some text in lowercase and pressing Enter. The text will automatically be converted to uppercase.

A further clarification: I'm NOT speaking here about calculations that use
the =UPPER() function. Instead, I'm concerned about assigning some sort of
input formatting to non-calculated cells so that entered text is converted
within the cell into UPPER CASE.

Thanks -- J

"John Cutler" wrote:

Is it possible to pre-format a column of cells as "text" such that any
entered text will automatically be converted to UPPER CASE?

For example, entering lower "x" will be 'forced' by the formatting to
automatically appear as cap "X".

This would be comparable to database software that I use wherein I can
assign a "field input mask" to a data entry field that "forces" all text
entries to UPPER CASE.

Thanks,

John

A clarification: I know about the UPPER() function for calculations.
Instead, what I'm asking for here is how to format non-calculated cells to
force entered text to UPPER CASE.

Thanks.

"John Cutler" wrote:

Is it possible to pre-format a column of cells as "text" such that any
entered text will automatically be converted to UPPER CASE?

For example, entering lower "x" will be 'forced' by the formatting to
automatically appear as cap "X".

This would be comparable to database software that I use wherein I can
assign a "field input mask" to a data entry field that "forces" all text
entries to UPPER CASE.

Thanks,

John

If all you're limited to is formatting, then the closest thing you could use is
a font that is all uppercase.

You could use another column with that =upper() worksheet function

or you could use a macro that reacts when someone changes a cell in the range
you want.

Chip Pearson has some event procedure code he
http://www.cpearson.com/Excel/ChangingCase.aspx

Chip has some code that same page that you can run on demand.

John Cutler wrote:

Is it possible to pre-format a column of cells as "text" such that any
entered text will automatically be converted to UPPER CASE?

For example, entering lower "x" will be 'forced' by the formatting to
automatically appear as cap "X".

This would be comparable to database software that I use wherein I can
assign a "field input mask" to a data entry field that "forces" all text
entries to UPPER CASE.

Thanks,

John

--

Dave Peterson

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

Copied to a sheet module.


Gord Dibben MS Excel MVP


On Sun, 28 Feb 2010 12:07:01 -0800, John Cutler
wrote:

A clarification: I know about the UPPER() function for calculations.
Instead, what I'm asking for here is how to format non-calculated cells to
force entered text to UPPER CASE.

Thanks.

"John Cutler" wrote:

Is it possible to pre-format a column of cells as "text" such that any
entered text will automatically be converted to UPPER CASE?

For example, entering lower "x" will be 'forced' by the formatting to
automatically appear as cap "X".

This would be comparable to database software that I use wherein I can
assign a "field input mask" to a data entry field that "forces" all text
entries to UPPER CASE.

Thanks,

John

Hi John

Regrettably not.
You could do it though with some event code on your sheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Columns("A:E")
If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End Sub

In this example, columns A to E inclusive will have their values cahnged
to upper case. You could make rng a single column, or, no contiguous
columns such as ("A", "D", "G")

To use
Copy code above.
Right click on your sheet tabView code
Paste code into white pane that appears
Alt+F11 to return to Excel

Regards
Roger Govier

John Cutler wrote:
Is it possible to pre-format a column of cells as "text" such that any
entered text will automatically be converted to UPPER CASE?

For example, entering lower "x" will be 'forced' by the formatting to
automatically appear as cap "X".

This would be comparable to database software that I use wherein I can
assign a "field input mask" to a data entry field that "forces" all text
entries to UPPER CASE.

Thanks,

John

Thanks Roger, this worked with me like a charm.
Have a good day.