CHANGING TYPE CASE WITHOUT RETYPING

  • Thread starter Thread starter Marie
  • Start date Start date
M

Marie

Kia ora

I am looking for a way to change type case in excel. A
toogle switch like in Word or even a format/function with
out having to write a formula.

Can anyone help.

ka kite ano
Marie
 
I don't think you can do it without a formula, however, the formula is
easy...

I would do 2 things...

ONE...Insert a new column, in the example, let's call it column B and let's
say that your text to be changed is in column A. In column B use one of the
following formulae:

proper(A1) will change all the words in A1 to Proper Text (first letter
of every word is capitalized)

upper(A1) will change all the words to ALL CAPS

lower(A1) will change all the words to lower case


TWO...Now for a neato trick,

Select column B
from Edit menu, Select COPY
Select column B again
from the Edit Menu, Select PASTE SPECIAL | Value

Now you have the correct case *text* (not formula) in column B and can
delete column A if you want to.

Cameo
 
try these macros that you may assign to a button

Sub TextConvert() 'Better than mine
'By Ivan F Moala
Dim ocell As Range
Dim Ans As String

Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")

If Ans = "" Then Exit Sub

For Each ocell In Selection '.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub

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
 
Back
Top