Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can you change letter case without having to retype words?
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? |
#2
|
|||
|
|||
Sure. One easy way is to copy to Word, select and choose Format-Change case.
However, you can do this using a macro, which you might want to store in your personal.xls file. http://www.vbaexpress.com/kb/getarticle.php?kb_id=69 ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Elissa" wrote in message ... 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? |
#3
|
|||
|
|||
Elissa,
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. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Elissa" wrote in message ... 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? |
#4
|
|||
|
|||
Hi
You can use Code to do this See this webpages http://www.mvps.org/dmcritchie/excel/proper.htm Or http://www.cpearson.com/excel/case.htm 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Elissa" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can you change letter case without having to retype words?
Copy the following macro:
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
convert value in word. For Exampe Rs.115.00 convert into word as . | Excel Discussion (Misc queries) | |||
How can i change this VBA project According to Indian Numeric | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions |