| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Capitalizing Text
I need to format cells in Excel so it automatically converts text to all CAPS
when a user enters in data. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
I'd simply get it done in an adjacent col
In B2: =TRIM(UPPER(A2)) B2 copied down as far as data is expected in col A -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Carin" wrote: I need to format cells in Excel so it automatically converts text to all CAPS when a user enters in data. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
Yeah, I would do that, however it is a large spreadsheet with a lot of data
that needs to be changed to Caps so to add an extra column doesn't make sense. And yes, we could just tell everyone to enter their data in caps, but that doesn't always work. :o) "Max" wrote: I'd simply get it done in an adjacent col In B2: =TRIM(UPPER(A2)) B2 copied down as far as data is expected in col A -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Carin" wrote: I need to format cells in Excel so it automatically converts text to all CAPS when a user enters in data. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
No need to be sarcastic. Someone will pop by soon enough with the direct
answer for you. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
There is no such "format" that you can apply. If you are looking to do this
with any and all entries (that is, as the entry is made) within a range of cells, you can use this worksheet event procedure. Right-click the tab for the worksheet you want this functionality on and copy/paste the following code into the code window that appeared... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B3:E10")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Target.Value = UCase$(Target.Value) End If Whoops: Application.EnableEvents = True End Sub Anytime you enter something containing letters, those letters will automatically become upper case. This, of course, won't convert lower case text that is pre-existing within the range. You can use this macro to do that... Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Value = UCase$(R.Value) Next Whoops: Application.EnableEvents = True End Sub Put this code in the same code window you put the Change event procedure above in. You can either run it directly from the code window or from the worksheet (press Alt+F8, select it from the list and Run it). After you upper case you existing text, you can delete the macro (**not** the Change event procedure). In both procedures above, change the B3:E10 example range to the range you want this to apply to. Rick "Carin" wrote in message ... I need to format cells in Excel so it automatically converts text to all CAPS when a user enters in data. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
Just a caveat with Rick's code.
If you have any formulas in the B3:E10 range they will be converted to values. To prevent that. Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Formula = UCase$(R.Formula) Next Whoops: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Mon, 2 Jun 2008 17:06:23 -0400, "Rick Rothstein \(MVP - VB\)" wrote: You can use this macro to do that... Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Value = UCase$(R.Value) Next Whoops: Application.EnableEvents = True End Sub |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
Good point... thanks for catching that.
Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Just a caveat with Rick's code. If you have any formulas in the B3:E10 range they will be converted to values. To prevent that. Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Formula = UCase$(R.Formula) Next Whoops: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Mon, 2 Jun 2008 17:06:23 -0400, "Rick Rothstein \(MVP - VB\)" wrote: You can use this macro to do that... Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Value = UCase$(R.Value) Next Whoops: Application.EnableEvents = True End Sub |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
By the way, the same caveat applies to my Change event code also. If you
attempt to enter a formula into one of the covered cells, the upper cased result will be entered and the formula will be gone. So, change the .Value property calls to .Formula property calls in the Change event procedure as well. Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Just a caveat with Rick's code. If you have any formulas in the B3:E10 range they will be converted to values. To prevent that. Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Formula = UCase$(R.Formula) Next Whoops: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Mon, 2 Jun 2008 17:06:23 -0400, "Rick Rothstein \(MVP - VB\)" wrote: You can use this macro to do that... Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Value = UCase$(R.Value) Next Whoops: Application.EnableEvents = True End Sub |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
I missed that completely Rick.............sheepish grin, tug on forelock<g
Gord On Mon, 2 Jun 2008 18:09:38 -0400, "Rick Rothstein \(MVP - VB\)" wrote: By the way, the same caveat applies to my Change event code also. If you attempt to enter a formula into one of the covered cells, the upper cased result will be entered and the formula will be gone. So, change the .Value property calls to .Formula property calls in the Change event procedure as well. Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Just a caveat with Rick's code. If you have any formulas in the B3:E10 range they will be converted to values. To prevent that. Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Formula = UCase$(R.Formula) Next Whoops: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Mon, 2 Jun 2008 17:06:23 -0400, "Rick Rothstein \(MVP - VB\)" wrote: You can use this macro to do that... Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Value = UCase$(R.Value) Next Whoops: Application.EnableEvents = True End Sub |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
Another way could be to use Data Validation, with a Custom formula
=EXACT(A1,UPPER(A1)) which will prevent any lowercase entry. Dave url:http://www.ureader.com/msg/104234538.aspx |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
I was thinking about this a little more and realize that there is a caveat
to your caveat.<g Depending on what formulas exist in the cells (or, in the case of my Change event procedure, what formulas the user is attempting to enter), it may be inappropriate to simply UCase the Formula property of each cell. Consider a formula like this (which relies on a case-sensitive evaluation of some sort)... =IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No") If we simply UCase the Formula property, the above formula will become this... =IF(ISNUMBER(FIND("RICK",$A$1)),"YES","NO") and no longer work properly. The YES/NO would be correct, but the evaluation taking place for the contents of A1, which lies outside of the range being UCase'd, has now been transformed to something different than intended. So, it is probably more correct to change this line from my code... R.Formula = UCase$(R.Formula) to this... If Not R.HasFormula Then R.Formula = UCase$(R.Formula) End If instead (and, of course, make the similar change to my Change event code as well); although I would be willing to bet, if I thought about it long enough, that there are probably cases where this might not be the appropriate solution either.<g Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Just a caveat with Rick's code. If you have any formulas in the B3:E10 range they will be converted to values. To prevent that. Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Formula = UCase$(R.Formula) Next Whoops: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Mon, 2 Jun 2008 17:06:23 -0400, "Rick Rothstein \(MVP - VB\)" wrote: You can use this macro to do that... Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Value = UCase$(R.Value) Next Whoops: Application.EnableEvents = True End Sub |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
David McRitchie's code covers all bases and runs much faster on a large range.
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 Gord On Tue, 3 Jun 2008 10:27:43 -0400, "Rick Rothstein \(MVP - VB\)" wrote: I was thinking about this a little more and realize that there is a caveat to your caveat.<g Depending on what formulas exist in the cells (or, in the case of my Change event procedure, what formulas the user is attempting to enter), it may be inappropriate to simply UCase the Formula property of each cell. Consider a formula like this (which relies on a case-sensitive evaluation of some sort)... =IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No") If we simply UCase the Formula property, the above formula will become this... =IF(ISNUMBER(FIND("RICK",$A$1)),"YES","NO") and no longer work properly. The YES/NO would be correct, but the evaluation taking place for the contents of A1, which lies outside of the range being UCase'd, has now been transformed to something different than intended. So, it is probably more correct to change this line from my code... R.Formula = UCase$(R.Formula) to this... If Not R.HasFormula Then R.Formula = UCase$(R.Formula) End If instead (and, of course, make the similar change to my Change event code as well); although I would be willing to bet, if I thought about it long enough, that there are probably cases where this might not be the appropriate solution either.<g Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Just a caveat with Rick's code. If you have any formulas in the B3:E10 range they will be converted to values. To prevent that. Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Formula = UCase$(R.Formula) Next Whoops: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Mon, 2 Jun 2008 17:06:23 -0400, "Rick Rothstein \(MVP - VB\)" wrote: You can use this macro to do that... Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Value = UCase$(R.Value) Next Whoops: Application.EnableEvents = True End Sub |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
Uh, his code is nice, but it doesn't address (meaning it has the same flaw)
as I was attempting to point out... it upper cases **everything** in the formula including text strings used to check text from outside the range, even if doing so destroys the formula's intent. Using the same formula I posted in my last message... =IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No") Because FIND is being used, the contents of A1 is being searched in a case-sensitive manner; hence, "rick" needs to stay "rick" in order for the function to do what it was designed to do. However, David's code and my code, changed in accordance with the caveat you posted, both change the "rick" to "RICK"... that means FIND will no longer be able to find what it was intended to find and the formula, while still a formula, will no longer perform as intended. Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message ... David McRitchie's code covers all bases and runs much faster on a large range. 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 Gord On Tue, 3 Jun 2008 10:27:43 -0400, "Rick Rothstein \(MVP - VB\)" wrote: I was thinking about this a little more and realize that there is a caveat to your caveat.<g Depending on what formulas exist in the cells (or, in the case of my Change event procedure, what formulas the user is attempting to enter), it may be inappropriate to simply UCase the Formula property of each cell. Consider a formula like this (which relies on a case-sensitive evaluation of some sort)... =IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No") If we simply UCase the Formula property, the above formula will become this... =IF(ISNUMBER(FIND("RICK",$A$1)),"YES","NO") and no longer work properly. The YES/NO would be correct, but the evaluation taking place for the contents of A1, which lies outside of the range being UCase'd, has now been transformed to something different than intended. So, it is probably more correct to change this line from my code... R.Formula = UCase$(R.Formula) to this... If Not R.HasFormula Then R.Formula = UCase$(R.Formula) End If instead (and, of course, make the similar change to my Change event code as well); although I would be willing to bet, if I thought about it long enough, that there are probably cases where this might not be the appropriate solution either.<g Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message . .. Just a caveat with Rick's code. If you have any formulas in the B3:E10 range they will be converted to values. To prevent that. Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Formula = UCase$(R.Formula) Next Whoops: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Mon, 2 Jun 2008 17:06:23 -0400, "Rick Rothstein \(MVP - VB\)" wrote: You can use this macro to do that... Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Value = UCase$(R.Value) Next Whoops: Application.EnableEvents = True End Sub |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalizing Text
Gothcha
Thanks for keeping at this Rick. Gord On Tue, 3 Jun 2008 13:20:26 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Uh, his code is nice, but it doesn't address (meaning it has the same flaw) as I was attempting to point out... it upper cases **everything** in the formula including text strings used to check text from outside the range, even if doing so destroys the formula's intent. Using the same formula I posted in my last message... =IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No") Because FIND is being used, the contents of A1 is being searched in a case-sensitive manner; hence, "rick" needs to stay "rick" in order for the function to do what it was designed to do. However, David's code and my code, changed in accordance with the caveat you posted, both change the "rick" to "RICK"... that means FIND will no longer be able to find what it was intended to find and the formula, while still a formula, will no longer perform as intended. Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . David McRitchie's code covers all bases and runs much faster on a large range. 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 Gord On Tue, 3 Jun 2008 10:27:43 -0400, "Rick Rothstein \(MVP - VB\)" wrote: I was thinking about this a little more and realize that there is a caveat to your caveat.<g Depending on what formulas exist in the cells (or, in the case of my Change event procedure, what formulas the user is attempting to enter), it may be inappropriate to simply UCase the Formula property of each cell. Consider a formula like this (which relies on a case-sensitive evaluation of some sort)... =IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No") If we simply UCase the Formula property, the above formula will become this... =IF(ISNUMBER(FIND("RICK",$A$1)),"YES","NO") and no longer work properly. The YES/NO would be correct, but the evaluation taking place for the contents of A1, which lies outside of the range being UCase'd, has now been transformed to something different than intended. So, it is probably more correct to change this line from my code... R.Formula = UCase$(R.Formula) to this... If Not R.HasFormula Then R.Formula = UCase$(R.Formula) End If instead (and, of course, make the similar change to my Change event code as well); although I would be willing to bet, if I thought about it long enough, that there are probably cases where this might not be the appropriate solution either.<g Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Just a caveat with Rick's code. If you have any formulas in the B3:E10 range they will be converted to values. To prevent that. Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Formula = UCase$(R.Formula) Next Whoops: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Mon, 2 Jun 2008 17:06:23 -0400, "Rick Rothstein \(MVP - VB\)" wrote: You can use this macro to do that... Sub UpperCaseExistingText() Dim R As Range On Error GoTo Whoops Application.EnableEvents = False For Each R In Range("B3:E10") R.Value = UCase$(R.Value) Next Whoops: Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Capitalizing text from the left limited by a comma | Excel Discussion (Misc queries) | |||
| Capitalizing | Excel Discussion (Misc queries) | |||
| Capitalizing an existing column. | Excel Discussion (Misc queries) | |||
| Capitalizing first word only... | Excel Worksheet Functions | |||
| Capitalizing selected area | Excel Discussion (Misc queries) | |||
Linear Mode
