HOW TO CHANGE EXISTING XL DATA SHEET TO ALL UPPERCASE TEXT

[Guest]

I have a data sheet with a mix of upper and lower case text in various fonts.
How do I change it to all upper case? Can change font & size ok. Thanks.
Regards John

 

[Jim Cone]

John,
You will have to use the "Upper" worksheet function in a blank column.
If you have multiple columns of data, that is not always a useful fix.

The free Excel add-in "Excel Extras" adds... Lower, Upper, Proper and Sentence
case options to the format menu (select your data and click the menu item).
The add-in also does other useful stuff.

Download from (no registration required) ...
http://www.realezsites.com/bus/primitivesoftware
--
Jim Cone
San Francisco, USA


"John"
<[email protected]>
wrote in message
I have a data sheet with a mix of upper and lower case text in various fonts.
How do I change it to all upper case? Can change font & size ok. Thanks.
Regards John

 

[Paul B]

John, you can use a macro like this, select the range you want to change and
then run it

Sub MAKE_CAPS()
'select range and run this to change to all CAPS
Dim cel As Range
For Each cel In Intersect(Selection, _
ActiveSheet.UsedRange)
cel.Formula = UCase$(cel.Formula)
Next
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Gord Dibben]

John

Example only..........

Assume you have data in column A

In B1 enter =UPPER(A1)

Copy down column A

When happy, copy Column B then, in place, Edit>Paste Special>Values>OK>Esc.



Gord Dibben MS Excel MVP

 

[Guest]

Thanks for your advice - works well! Regards John

 

[Guest]

Copy the following macro:

Sub Change_Case()
Dim ocell As Range
Dim Ans As String
Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")
If Ans = "" Then Exit Sub
For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub