Make all text ALL Caps in Excel 1

[chillay]

Hello

I am trying to capitalize all text in one specific column. I am unable to find the button or command to make all lowercase text ALL CAPS. Can anyone help.

Thank you.

Chillay

 

[mscallisto]

Can you use some vba code?

Sub Convert2Upper()
'Convert the used portion of a row to Uppercase

'Count the used rows
usedrows = ActiveSheet.UsedRange.Rows.Count

'Highlite the active sheet "used" area for a visual
'of the area used
ActiveSheet.UsedRange.Select

Dim i As Integer
With Worksheets(1).Range("a:a")

For i = 1 To usedrows
'To Stop at first non blank cell add the following
'If (.Cells(i).Value = "") Then Exit For

.Cells(i).Value = UCase(.Cells(i).Value)
Next i
End With
End Sub

 

[TonyJollans]

Hi Chillay,

As this is not the VBA forum, you could add a temporary column.

Assume your values are in column A
Insert a column to the right (a new column B)
In B1, enter [blue]=UPPER(A1)[/blue]
Copy down as far as your data go
Select Column B, and Copy it
Edit > Paste Special > Values (to itself)
Delete Column A

Note that this won't work if you have formulae in your column (and neither will mscallisto's code).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[chillay]

Thank you TonyJollans and mscallisto

I really appreciate it. I went with TonyJollans solution and it worked like a charm.

Chillay

 

[DaleWatson123321]

Hi chillay,

With you having been a Tek-Tips member for some time, I know you'll appreciate learning the following...

The "proper" method of showing "appreciation" for the contributions received at Tek-Tips, is by awarding purple STARS. It's a simple matter of clicking on the link at the bottom-left-corner of a contributor's posting - the one that begings with a little star, followed by "Thank ______ for this valuable post!" And you're not limited to one STAR - i.e. you can or perhaps should also award STARS for other contributions you condsider to be worthy.

You're not the first member to overlook the "proper" method of acknowledging the contributions made by Tek-Tips contributors. Like many things, it only becomes obvious AFTER it's been pointed out ;-).

Because it's happened without asking the following... please don't award me a star for my posting (because it's not a "true" tip).

Regards, Dale Watson

 

[chillay]

Thank you Dale.

This is very good information.

Chillay