Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Text formatted as All Capitals?
In MSWord, formatting can easily be set so that selected text is always in
capitals by FormatFontsAll Caps. Can text in Excel be similarly formatted? |
#2
|
|||
|
|||
=UPPER(A1)
-- HTH Bob Phillips "Joey" wrote in message ... In MSWord, formatting can easily be set so that selected text is always in capitals by FormatFontsAll Caps. Can text in Excel be similarly formatted? |
#3
|
|||
|
|||
Use Upper function will be fine.
However, if you want to apply to a large area of text and want to change it on itself, then you need some other tools, like third-party add-in, etc. ===== * ===== * ===== * ===== Daniel CHEN Spreadsheet/VBA Specialist www.Geocities.com/UDQServices Your "Impossible" Task Could Be Someone Else's "Piece of Cake" ===== * ===== * ===== * ===== "Joey" wrote in message ... In MSWord, formatting can easily be set so that selected text is always in capitals by FormatFontsAll Caps. Can text in Excel be similarly formatted? |
#4
|
|||
|
|||
"Daniel CHEN" wrote in message ... Use Upper function will be fine. However, if you want to apply to a large area of text and want to change it on itself, then you need some other tools, like third-party add-in, etc. What, like something you sell? That is trivial VBA. |
#5
|
|||
|
|||
Try the following code (assume you are familar with VBA/macro)
Sub muUpperCase() Dim rngText As Range, cl As Range Set rngText = Range("A1:B10") For Each cl In rngText cl = UCase(cl.Value) Next End Sub You can change A1:B10 to any range you want. ===== * ===== * ===== * ===== Daniel CHEN Spreadsheet/VBA Specialist www.Geocities.com/UDQServices Your "Impossible" Task Could Be Someone Else's "Piece of Cake" ===== * ===== * ===== * ===== "Joey" wrote in message ... In MSWord, formatting can easily be set so that selected text is always in capitals by FormatFontsAll Caps. Can text in Excel be similarly formatted? |
#6
|
|||
|
|||
Daniel
One issue with your macro. If any formulas in the range, they will be wiped out and changed to values only. Preferable to that would be David McRitchie's code....... 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 Wed, 1 Jun 2005 12:36:05 -0500, "Daniel CHEN" wrote: Try the following code (assume you are familar with VBA/macro) Sub muUpperCase() Dim rngText As Range, cl As Range Set rngText = Range("A1:B10") For Each cl In rngText cl = UCase(cl.Value) Next End Sub You can change A1:B10 to any range you want. ===== * ===== * ===== * ===== Daniel CHEN Spreadsheet/VBA Specialist www.Geocities.com/UDQServices Your "Impossible" Task Could Be Someone Else's "Piece of Cake" ===== * ===== * ===== * ===== "Joey" wrote in message ... In MSWord, formatting can easily be set so that selected text is always in capitals by FormatFontsAll Caps. Can text in Excel be similarly formatted? |
#7
|
|||
|
|||
Joey
Not by formatting... If you want the text in CAPS as you type it. Use worksheet event code that changes typed text to CAPS as you enter it. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column 6 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub This code operates on Column A:F. Change the 6 to suit. Right-click on the sheet tab and "View Code". Copy/paste the code into that module. If you just want to make the change after the fact, see other posts for a macro. Gord Dibben Excel MVP On Wed, 1 Jun 2005 21:03:33 +1000, "Joey" wrote: In MSWord, formatting can easily be set so that selected text is always in capitals by FormatFontsAll Caps. Can text in Excel be similarly formatted? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text All Capitals | New Users to Excel | |||
i have fields that are formatted for text that randomly convert t. | Excel Discussion (Misc queries) | |||
reference the result of a formula in a text formatted cell | Excel Discussion (Misc queries) | |||
How to make a cell return the formatted value in a text string (i. | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |