Capitalising text

[Winsome]

When there is a column of text how do I change the text in the whole column
to UPPERCASE in one step?

[Max]

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 <at>yahoo<dot>com
----
"Winsome" <Winsome@discussions.microsoft.com> wrote in message
news:D289F46E-3DE7-47A3-9584-63247CF4F18F@microsoft.com...
> When there is a column of text how do I change the text in the whole
column
> to UPPERCASE in one step?

[Harlan Grove]

"Winsome" <Winsome@discussions.microsoft.com> 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

[Gordon]

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]

> 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 <at>yahoo<dot>com
----
"Gordon" <gordonbp1@yahoo.co.uk.invalid> wrote in message
news:uwL$MiHKFHA.3652@TK2MSFTNGP10.phx.gbl...
> 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
>
>

[Gordon]

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