Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lowercase to uppercase
What is the easiest way to change text in Excel from upper to lower case or
vice versa? Thank you. Louise |
#2
|
|||
|
|||
Louise,
This macro does it Sub ChangeCase() Dim cell As Range For Each cell In Selection cell.Value = LCase(cell.Value) Next cell End Sub To change to upper-case just use Ucase/ Proper case (This is. This isn't, for instance) is trickier, and you need a worksheetfunction as well Sub ChangeCase() Dim cell As Range For Each cell In Selection cell.Value = WorksheetFunction.Proper(cell.Value) Next cell End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Louise" wrote in message ... What is the easiest way to change text in Excel from upper to lower case or vice versa? Thank you. Louise |
#3
|
|||
|
|||
The worksheet functions UPPER and LOWER will do this.
EG if you have the text "BOB SMITH" in cell A1, then in another cell you put =LOWER(A1) the result will be "bob smith" Also helpful is the function PROPER - if you use this on the above example you would get "Bob Smith" "Louise" wrote: What is the easiest way to change text in Excel from upper to lower case or vice versa? Thank you. Louise |
#4
|
|||
|
|||
This might come in handy stored in your personal.xls file
Sub ChangeCase() 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 -- Don Guillett SalesAid Software "Louise" wrote in message ... What is the easiest way to change text in Excel from upper to lower case or vice versa? Thank you. Louise |
#5
|
|||
|
|||
Hi, Don:
If a cell contains a literal (text or number), it's formula is it's value, so you can just write For Each r In Selection.Cells r.Formula = LCase(r.Formula) Next On Mon, 10 Jan 2005 08:09:49 -0600, "Don Guillett" wrote: This might come in handy stored in your personal.xls file Sub ChangeCase() 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 |
#6
|
|||
|
|||
A caveat with Bob's macro.....
If you have any formulas in the selected range, they will be wiped out and replaced with values. To prevent that, change to..... Sub ChangeCase() Dim cell As Range For Each cell In Selection cell.Formula = LCase(cell.Formula) Next cell End Sub Gord Dibben Excel MVP On Mon, 10 Jan 2005 10:45:18 -0000, "Bob Phillips" wrote: Louise, This macro does it Sub ChangeCase() Dim cell As Range For Each cell In Selection cell.Value = LCase(cell.Value) Next cell End Sub To change to upper-case just use Ucase/ Proper case (This is. This isn't, for instance) is trickier, and you need a worksheetfunction as well Sub ChangeCase() Dim cell As Range For Each cell In Selection cell.Value = WorksheetFunction.Proper(cell.Value) Next cell End Sub |
#7
|
|||
|
|||
I guess I'm slipping. Those should both be "its", not "it's".
On Mon, 10 Jan 2005 13:10:35 -0600, Myrna Larson wrote: Hi, Don: If a cell contains a literal (text or number), it's formula is it's value, so you can just write For Each r In Selection.Cells r.Formula = LCase(r.Formula) Next On Mon, 10 Jan 2005 08:09:49 -0600, "Don Guillett" wrote: This might come in handy stored in your personal.xls file Sub ChangeCase() 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Date in uppercase | Excel Worksheet Functions | |||
uppercase to lowercase | Excel Discussion (Misc queries) | |||
Uppercase in a whole sheet | Excel Worksheet Functions | |||
How to change text in multiple cells from Uppercase to proper cas. | Excel Worksheet Functions |