Changing Case Upper to Lower 3

[4321z]

Hi,

Is there a quick way to change the case of text in cells in an excel worksheet from lower to upper and vice versa?

 

[Corgano]

If you can do it via formulas LOWER(CELLREF) will convert them, alternatively a find & replace of each letter will also do the job but you'd need to do it 26 times.

Ie FIND "A" REPLACE WITH "a".

I'm sure someone will have a macro solution though and maybe some better ways.

 

[jfhewitt]

Here is macro you can put into a button on your toolbar.




Sub CaseLower()
'
' ToggleUpper Macro
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In Selection

cell.Value = UCase(cell.Value)
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

To toggle:

Sub ToggleCase()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In Selection
If cell.HasFormula = False And cell.Value = UCase(cell.Value) Then
cell.Value = LCase(cell.Value)
Else
cell.Value = UCase(cell.Value)
End If
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[krinid]

4231z,
How many cells are in your sheet? Using a for each cell in selection technique will be really slow for big selections. I can't think of a better way, but the replace idea would most likely execute quicker.

If you do go with the for each cell in selection scheme, I would suggest only changing the cell contacts when necessary... for example

for each cell in selection
   if cell <> LCase(cell) then
      cell = LCase(cell)
   end if
next

And vice versa with UCase for changing to uppercase.

Using the above will speed up the entire process immensely, unless every cell actually has to be changed, if which case there will be no difference.

It's just the nature of Excel, changing cells takes time. It's a small amount of time for a single cell, but when changing multiple cells, the delay becomes very noticeable.