Software

Ask something
Discussion Locked

Excel: Uppercase problem

0 helpful
9 replies

by fitgirl

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Have a file with 4000 records, each record looks something like this :

S1000555235 Butler,Florence 123 Williams Road Richmond VA 23001 51

We will be sending this information to a state agency which requires all letters to be in caps.

I have tried the INSERT-FUNCTION-UPPER function but keep getting an error like this:
"MS Excel cannot calculate a formula. Cell references in the formula refer to the formulas result, creating a circular reference." (Whatever that is ????)

Can this be done??

Before answering please remember:
1) I have 4000 more records like this one, so inputing a string individually is not an option.

2) I need to have ALL letters in each record to be in caps.

Thanks,

Fitgirl

9 replies

by yatst

OK here's a way to cheat the system.

Copy your 4000 records. Try going to the last cell of last record a do a "Ctrl" + "Shift" + "Home" to keep the file size down.

Paste it into word. Word will convert it to a table so none of the formatting will get lost.

Select All "Ctrl" + "A".

Select Format then Convert Case then Uppercase.

Then paste it back to your excel file.

Good luck.

by don christner

You are on the right track by using insert-=function-upper. However, you can't do it within the same cell. For example, if your data is in cells A1-D1, you will go to cell E1 and then do the insert-function-upper and then enter cell A1. Then in cellE1 you will have the all caps version of what was in A1. Drag the formula across into cells F1-H1 and then down for your 4000 records. Now in cells E1-H1 you will have the data the way that you need it. Of course you could paste it into another worksheet, file or whatever if you need to.

Don

by dfowler

This might be easier than copying and pasting back and forth from Word. Create a macro in your excel file that says the following:

Sub ConvertToUpperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value)
End If
Next Rng
End Sub

Then, select (block) all the records you need changed to all caps - all 4000 at one time if you want. With the records selected, run the macro and it will convert all at once. Ifyou have trouble with it or need instructions on how to create the macro without using the record feature, feel free to contact me via e-mail.

by dklippert

DFowler has the right answer, but if you'd rather just use the worksheet use =UPPER().
In a blank column enter =UPPER(A1). Copy the formula to the bottom of the column. (Double-click the fill handle if the data is contiguous).
Now select all of the converted data and Copy then Right-click Paste Special>Values. The latter step leaves just the results of the formulas. You could then paste this over the original data if need be.

This discussion is closed to new replies.