Converting Small letters to capitals in a Large Excel Document?

Question

I have been given a large document to "clean" in excel. (Runs from A to G and over 5000 lines.)
One column is poscodes so also has numbers in.
Some of the 5000 lines have lower case, some upper but i want them ALL Upper case.
How to do please........
It would be helpful also to tell me WHERE to put formula etc as all help i can find is how to change one box not a whole document!!

Answer 1

You can either use a formula to produce the result you want, or add in some VBA code to use as a Macro to execute. We will start off with the first option:



If column B needs to be uppercase, then Insert a new column right beside that (column C), so that you will have empty cells to work with.



In cell C1, enter the following formula:



= UPPER(B1)



Now select that same cell, and use the Fill Handle (the small black square in the lower-right corner) to copy that same formula down for as many rows as you have data in column B.



Select column C when that is completed and Copy it.



Select column B and do a PASTE SPECIAL... When the dialog box appears, select "Values" under the Paste section. Then click OK.



You have now replaced what was in column B with the uppercase values. So the formula column may be removed now.



Do the same with any other columns that need to be converted.





Here is a Macro that will do something similar:



Option Explicit

Public Sub UpCase()

Dim rng As Range

Dim cell As Range

Set rng = ActiveWindow.RangeSelection

For Each cell In rng

cell.Value = StrConv(cell.Value, vbUpperCase)

Next cell

End Sub



The above will work for only those cells that are selected at the time you run the Macro.



If you want the Macro to select all cells that have data around the current active cell, then replace the above SET statement with the following:



Set rng = ActiveCell.CurrentRegion



.

Answer 2

I would use: =PROPER(A1) or whatever your cell that has capital letters in it. The Proper function will keep the first letter capitalized.

Answer 3

Go to http://www.asap-utilities.com and download and install the latest version. It’s an add in for Excel that starts when Excel starts, and it gives you loads of other functions under different menus. One of which under the Text menu is 'Convert to Uppercase', select your column, hit that and 1 second later, it's done

Answer 4

http://www.mrexcel.com/archive/Edit/7907.html



A simple search got me this site, for exactly what you want to do. It will leave the numbers unchanged and leave all text, regardless of which it was - upper or lower case - as upper case.