Capitalising text

W

Winsome

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

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
 
H

Harlan Grove

Winsome said:
When there is a column of text how do I change the text in the whole column
to UPPERCASE in one step?
Click to expand...

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
 
G

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?
 
M

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>.
 
G

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.......
 
Top