Capitalising text
When there is a column of text how do I change the text in the whole column
to UPPERCASE in one step? |
If you're not too particular about an extra 2-3 steps ..
Assume col A is the col of text in A1 down Put in B1: =TRIM(UPPER(A1)) Copy down Copy col B and then right click on col A Choose paste special values OK to overwrite col A Delete col B -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Winsome" wrote in message ... When there is a column of text how do I change the text in the whole column to UPPERCASE in one step? |
"Winsome" wrote...
When there is a column of text how do I change the text in the whole column to UPPERCASE in one step? You don't unless you have a macro to do this for you. If no macro, you could use formulas, but it requires more than one step. To capitalize A1:A1000, if col X were empty, enter =UPPER(A1) in X1, fill X1 down into X2:X1000, select X1:X1000, Edit Copy, select A1, Edit Paste Special as values, then clear X1:X1000. If you want a macro to do this, the following one will capitalize all cells containing text constants in the selected range. Sub uc() Dim c As Range, r As Range If Not TypeOf Selection Is Range Then Exit Sub On Error Resume Next Set r = Selection.SpecialCells(xlCellTypeConstants, 2) If Err.Number < 0 Then Err.Clear Exit Sub End If On Error Goto 0 For Each c In r c.Value = UCase(c.Value) Next c End Sub |
Max wrote:
|| If you're not too particular about an extra 2-3 steps .. || || Assume col A is the col of text in A1 down || || Put in B1: =TRIM(UPPER(A1)) What does TRIM do that just "=UPPER(A1)" doesn't? -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk |
What does TRIM do that just "=UPPER(A1)" doesn't?
It isn't relevant here as far as changing the case goes, but I'd normally use TRIM as well whenever working on text cols, to tidy up/remove any extraneous spaces which might be present. The result? A cleaner output besides the case conversion. Just a die-hard habit, I guess <g. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Gordon" wrote in message ... Max wrote: || If you're not too particular about an extra 2-3 steps .. || || Assume col A is the col of text in A1 down || || Put in B1: =TRIM(UPPER(A1)) What does TRIM do that just "=UPPER(A1)" doesn't? -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk |
Max wrote:
||| What does TRIM do that just "=UPPER(A1)" doesn't? || || It isn't relevant here as far as changing the case goes, but I'd || normally use TRIM as well whenever working on text cols, to tidy || up/remove any extraneous spaces which might be present. The result? || A cleaner output besides the case conversion. Just a die-hard habit, || I guess <g. -- || Rgds || Max Good thinking....... -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com