Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
formatting data
How to change data by column or comlete spreadsheet from lower case to upper
case by column. |
#2
|
|||
|
|||
You can use a formula in a Helper column: =UPPER(A1). Then use Edit/Copy and
Edit/Paste Special,Values to convert those formulas to their values. Another solution is John Walkenbach's Power Utility Pack, available for purchase at www.j-walk.com On Sun, 10 Apr 2005 08:47:02 -0700, smithrdsr wrote: How to change data by column or comlete spreadsheet from lower case to upper case by column. |
#3
|
|||
|
|||
Sub UppercaseAll()
Dim Rng As Range For Each Rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants, xlTextValues) Rng.Value = UCase(Rng.Text) Next Rng End Sub "smithrdsr" wrote: How to change data by column or comlete spreadsheet from lower case to upper case by column. |
#4
|
|||
|
|||
Manually using a helper column and the UPPER Function.
In B1 enter =UPPER(A1) Double-click on the fill handle of B1 to copy down. VBA Macro......... Select range to change or for complete sheet hit CTRL + A(twice in 2003) then run the macro. Sub optUpper_Click() 'David McRitchie, programming, 2003-03-07 Dim rng1 As Range, rng2 As Range, bigrange As Range Dim Cell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error Resume Next Set rng1 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeConstants)) Set rng2 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeFormulas)) On Error GoTo 0 If rng1 Is Nothing Then Set bigrange = rng2 ElseIf rng2 Is Nothing Then Set bigrange = rng1 Else Set bigrange = Union(rng1, rng2) End If If bigrange Is Nothing Then MsgBox "All cells in range are EMPTY" GoTo done End If For Each Cell In bigrange Cell.Formula = UCase(Cell.Formula) Next Cell done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Gord Dibben Excel MVP On Sun, 10 Apr 2005 08:47:02 -0700, smithrdsr wrote: How to change data by column or comlete spreadsheet from lower case to upper case by column. |
#5
|
|||
|
|||
Put in say, B1: =UPPER(A1)
Copy down Copy col B and paste special as Values to overwrite col A -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "smithrdsr" wrote in message ... How to change data by column or comlete spreadsheet from lower case to upper case by column. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retain PivotTable formatting after "Refresh Data" | Excel Discussion (Misc queries) | |||
Formatting data | Excel Discussion (Misc queries) | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |