formatting data

[smithrdsr]

How to change data by column or comlete spreadsheet from lower case to upper
case by column.

 

[Don Guillett]

this may come in handy

Sub ChangeCase() 'Don Guillett
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub

Sub ChangeCase1()
Dim r As Range
'nCase = UCase(InputBox("Enter U L or P"))
'MsgBox nCase
nCase = InputBox("enter 1,2,3")


Select Case Number

Case Is = 1
For Each r In Selection.Cells
If r.HasFormula Then

r.Formula = "=UPPER(" & Mid(r.Formula, 2) & ")"
Else
r.Value = UCase(r.Value)
End If
Next
Case Is = 2
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = "=LOWER(" & Mid(r.Formula, 2) & ")"
Else
r.Value = LCase(r.Value)
End If
Next

End Select
End Sub

 

[Kassie]

Sub UppercaseAll()
Dim Rng As Range
For Each Rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants,
xlTextValues)
Rng.Value = UCase(Rng.Text)
Next Rng
End Sub

 

[Gord Dibben]

Manually using a helper column and the UPPER Function.

In B1 enter =UPPER(A1)

Double-click on the fill handle of B1 to copy down.

VBA Macro.........

Select range to change or for complete sheet hit CTRL + A(twice in 2003) then
run the macro.

Sub optUpper_Click()
'David McRitchie, programming, 2003-03-07
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
End Sub


Gord Dibben Excel MVP

 

[Max]

Put in say, B1: =UPPER(A1)
Copy down
Copy col B and paste special as Values to overwrite col A