Capitalizing Text

  • Thread starter Thread starter Carin
  • Start date Start date
C

Carin

I need to format cells in Excel so it automatically converts text to all CAPS
when a user enters in data.
 
I'd simply get it done in an adjacent col
In B2: =TRIM(UPPER(A2))
B2 copied down as far as data is expected in col A
 
Yeah, I would do that, however it is a large spreadsheet with a lot of data
that needs to be changed to Caps so to add an extra column doesn't make
sense. And yes, we could just tell everyone to enter their data in caps, but
that doesn't always work. :o)
 
No need to be sarcastic. Someone will pop by soon enough with the direct
answer for you.

---
 
There is no such "format" that you can apply. If you are looking to do this
with any and all entries (that is, as the entry is made) within a range of
cells, you can use this worksheet event procedure. Right-click the tab for
the worksheet you want this functionality on and copy/paste the following
code into the code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3:E10")) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = UCase$(Target.Value)
End If
Whoops:
Application.EnableEvents = True
End Sub

Anytime you enter something containing letters, those letters will
automatically become upper case. This, of course, won't convert lower case
text that is pre-existing within the range. You can use this macro to do
that...

Sub UpperCaseExistingText()
Dim R As Range
On Error GoTo Whoops
Application.EnableEvents = False
For Each R In Range("B3:E10")
R.Value = UCase$(R.Value)
Next
Whoops:
Application.EnableEvents = True
End Sub

Put this code in the same code window you put the Change event procedure
above in. You can either run it directly from the code window or from the
worksheet (press Alt+F8, select it from the list and Run it). After you
upper case you existing text, you can delete the macro (**not** the Change
event procedure).

In both procedures above, change the B3:E10 example range to the range you
want this to apply to.

Rick
 
Just a caveat with Rick's code.

If you have any formulas in the B3:E10 range they will be converted to values.

To prevent that.

Sub UpperCaseExistingText()
Dim R As Range
On Error GoTo Whoops
Application.EnableEvents = False
For Each R In Range("B3:E10")
R.Formula = UCase$(R.Formula)
Next
Whoops:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
Good point... thanks for catching that.

Rick


Gord Dibben said:
Just a caveat with Rick's code.

If you have any formulas in the B3:E10 range they will be converted to
values.

To prevent that.

Sub UpperCaseExistingText()
Dim R As Range
On Error GoTo Whoops
Application.EnableEvents = False
For Each R In Range("B3:E10")
R.Formula = UCase$(R.Formula)
Next
Whoops:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
Click to expand...
 
By the way, the same caveat applies to my Change event code also. If you
attempt to enter a formula into one of the covered cells, the upper cased
result will be entered and the formula will be gone. So, change the .Value
property calls to .Formula property calls in the Change event procedure as
well.

Rick
 
I missed that completely Rick.............sheepish grin, tug on forelock<g>


Gord
 
I was thinking about this a little more and realize that there is a caveat
to your caveat.<g> Depending on what formulas exist in the cells (or, in the
case of my Change event procedure, what formulas the user is attempting to
enter), it may be inappropriate to simply UCase the Formula property of each
cell. Consider a formula like this (which relies on a case-sensitive
evaluation of some sort)...

=IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No")

If we simply UCase the Formula property, the above formula will become
this...

=IF(ISNUMBER(FIND("RICK",$A$1)),"YES","NO")

and no longer work properly. The YES/NO would be correct, but the evaluation
taking place for the contents of A1, which lies outside of the range being
UCase'd, has now been transformed to something different than intended. So,
it is probably more correct to change this line from my code...

R.Formula = UCase$(R.Formula)

to this...

If Not R.HasFormula Then
R.Formula = UCase$(R.Formula)
End If

instead (and, of course, make the similar change to my Change event code as
well); although I would be willing to bet, if I thought about it long
enough, that there are probably cases where this might not be the
appropriate solution either.<g>

Rick
 
David McRitchie's code covers all bases and runs much faster on a large range.

Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim Cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each Cell In bigrange
Cell.Formula = UCase(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


Gord
 
Uh, his code is nice, but it doesn't address (meaning it has the same flaw)
as I was attempting to point out... it upper cases **everything** in the
formula including text strings used to check text from outside the range,
even if doing so destroys the formula's intent. Using the same formula I
posted in my last message...

=IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No")

Because FIND is being used, the contents of A1 is being searched in a
case-sensitive manner; hence, "rick" needs to stay "rick" in order for the
function to do what it was designed to do. However, David's code and my
code, changed in accordance with the caveat you posted, both change the
"rick" to "RICK"... that means FIND will no longer be able to find what it
was intended to find and the formula, while still a formula, will no longer
perform as intended.

Rick
 
Gothcha

Thanks for keeping at this Rick.

Gord

Uh, his code is nice, but it doesn't address (meaning it has the same flaw)
as I was attempting to point out... it upper cases **everything** in the
formula including text strings used to check text from outside the range,
even if doing so destroys the formula's intent. Using the same formula I
posted in my last message...

=IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No")

Because FIND is being used, the contents of A1 is being searched in a
case-sensitive manner; hence, "rick" needs to stay "rick" in order for the
function to do what it was designed to do. However, David's code and my
code, changed in accordance with the caveat you posted, both change the
"rick" to "RICK"... that means FIND will no longer be able to find what it
was intended to find and the formula, while still a formula, will no longer
perform as intended.

Rick
Click to expand...
 
Back
Top