I have an excel worksheet which I need to change all the columns from
capitals to lower case text. Is there a way to do this without having to
retype the whole database?
"Elissa" <Elissa@discussions.microsoft.com> wrote in message
news:A55C0E80-8262-48F8-B87F-60DB17255F26@microsoft.com...
> I have an excel worksheet which I need to change all the columns from
> capitals to lower case text. Is there a way to do this without having to
> retype the whole database?
Use something like this in a helper column. Copy it down with the Fill
Handle.
=LOWER(A2)
or
= PROPER(A2)
If that looks like what you need, you can permanently convert the original
column with this: Select your helper column, Copy. Now select the first
cell of the original column, A (it must coorelate with the top cell that you
selected when you did the copy -- (same row)). Do Edit - Paste special -
Values. Now you can trash the helper column.
"Elissa" <Elissa@discussions.microsoft.com> wrote in message
news:A55C0E80-8262-48F8-B87F-60DB17255F26@microsoft.com...
>I have an excel worksheet which I need to change all the columns from
> capitals to lower case text. Is there a way to do this without having to
> retype the whole database?
Here are some Macro's for changing text cells in the selection
Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub Lowercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = LCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub Propercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = StrConv(cel.Value, vbProperCase)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
"Elissa" <Elissa@discussions.microsoft.com> wrote in message news:A55C0E80-8262-48F8-B87F-60DB17255F26@microsoft.com...
>I have an excel worksheet which I need to change all the columns from
> capitals to lower case text. Is there a way to do this without having to
> retype the whole database?
Sub Change_Case()
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
"Elissa" wrote:
> I have an excel worksheet which I need to change all the columns from
> capitals to lower case text. Is there a way to do this without having to
> retype the whole database?
Question
Reply
Top
Answer
Notify me of replies