Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change case
Is there a way to change all the entries in a column from lower case to
upper case? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change case
In Excel you can use the formulas
Upper & Lower In VBA you can use UCase & LCase It depends houw you want to approach the problem... -- HTH... Jim Thomlinson "john d" wrote: Is there a way to change all the entries in a column from lower case to upper case? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change case
I just tried entering the =UPPER() formula for the column. It works on a new
entry, and unless I am not using the syntax correctly, it does not change existing entries. Column name is UMB John "Jim Thomlinson" wrote: In Excel you can use the formulas Upper & Lower In VBA you can use UCase & LCase It depends houw you want to approach the problem... -- HTH... Jim Thomlinson "john d" wrote: Is there a way to change all the entries in a column from lower case to upper case? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change case
Hi
See : http://www.mvps.org/dmcritchie/excel/proper.htm Or http://www.cpearson.com/excel/case.htm Here are a few 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 "john d" wrote in message ... Is there a way to change all the entries in a column from lower case to upper case? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change case
Upper only works on a single cell. In a column next to the one you want to
convert add the formula (assuming the data is in column A and the desired result will be in B or where ever the formula is entered) and copy the formula down... =Upper(A1) Which will convert the contents of Cell A1 to upper case (the result will be in B1) -- HTH... Jim Thomlinson "john d" wrote: I just tried entering the =UPPER() formula for the column. It works on a new entry, and unless I am not using the syntax correctly, it does not change existing entries. Column name is UMB John "Jim Thomlinson" wrote: In Excel you can use the formulas Upper & Lower In VBA you can use UCase & LCase It depends houw you want to approach the problem... -- HTH... Jim Thomlinson "john d" wrote: Is there a way to change all the entries in a column from lower case to upper case? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change case
This would work for column A
Sub test2() Range("A:A").Select For Each cell In Range("A:A") cell.Value = UCase(cell.Value) Next cell End Sub You might want to modify this a little in order to keep it from checking cells below the point where your data stops. Sub test2() For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row) cell.Value = UCase(cell.Value) Next cell End Sub You can adjust the column reference as necessary. My example is using column A. HTH, Paul "john d" wrote in message ... Is there a way to change all the entries in a column from lower case to upper case? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change case
Hi PCLIVE
Warning : If there are formulas in the range they will be values after you run the macro. Use the code examples I posted to avoid that problem -- Regards Ron de Bruin http://www.rondebruin.nl "PCLIVE" wrote in message ... This would work for column A Sub test2() Range("A:A").Select For Each cell In Range("A:A") cell.Value = UCase(cell.Value) Next cell End Sub You might want to modify this a little in order to keep it from checking cells below the point where your data stops. Sub test2() For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row) cell.Value = UCase(cell.Value) Next cell End Sub You can adjust the column reference as necessary. My example is using column A. HTH, Paul "john d" wrote in message ... Is there a way to change all the entries in a column from lower case to upper case? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change case
Thanks Ron,
I just assumed that since the poster mentioned "Entries" in a column, that these would be manual entries. But you're correct. Thanks for pointing that out. Paul "Ron de Bruin" wrote in message ... Hi PCLIVE Warning : If there are formulas in the range they will be values after you run the macro. Use the code examples I posted to avoid that problem -- Regards Ron de Bruin http://www.rondebruin.nl "PCLIVE" wrote in message ... This would work for column A Sub test2() Range("A:A").Select For Each cell In Range("A:A") cell.Value = UCase(cell.Value) Next cell End Sub You might want to modify this a little in order to keep it from checking cells below the point where your data stops. Sub test2() For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row) cell.Value = UCase(cell.Value) Next cell End Sub You can adjust the column reference as necessary. My example is using column A. HTH, Paul "john d" wrote in message ... Is there a way to change all the entries in a column from lower case to upper case? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change data of entire column from small case to upper case | Excel Worksheet Functions | |||
How do I change from upper case to proper case in excel 2002 | Excel Discussion (Misc queries) | |||
How to change mixed case to upper case in Excel for all cells | Excel Discussion (Misc queries) | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
How do I change existing text from lower case to upper case | Excel Discussion (Misc queries) |