Excel Formula to convert existing text to all CAPS

[gail321]

How can I use a formula to convert the text in an existing document to all capital letters?

 

[ashleywanless]

you may find the =upper(a1) function useful, where a1 is the cell you wish to convert.

 

[gail321]

It says I am creating a circular reference when i use the UPPER formula and changes the value of the cell to 0. Any suggestions?

 

[ashleywanless]

Sorry you will need to put that formula in sheet 2 cell a1 with the formula looking at cell a1 in sheet 1. You can then copy paste special as values and format to create a copy in upper case.

 

[HalfAce]

Or you could insert a new column A, put the formula (=UPPER(B1)) in A1. Copy down column A, Copy column A & PasteSpecial (as Values) and then delete column B.

Hope it helps.

 

[shg]

Or you could select the column of interest and run UpperSelection:

Sub UpperSelection()
    UpperIt Intersect(Selection, ActiveSheet.UsedRange)
End Sub
 
Sub UpperIt(r As Range)
    With r
        .Value = Evaluate("if(row(), upper(" & r.Address & "))")
    End With
End Sub