Using an excel file with ALL CAPS problem

[aglenn01]

I have an excel sheet for a mailing with roughly 2000 names and addresses. When I submitted it to USPS to have the addresses sorted the file came back to me with the entire column of address in ALL CAPS while the names remain normal case. Is there a way to change the addresses back to normal case with out re-typing every address?

Thank you

 

[rassten]

Something like this

Excel Workbook
	A	B
1	JAN	Jan
2	FEB	Feb
3	MAR	Mar
4	APR	Apr
5	MAJ	Maj
6	JUN	Jun
Ark4

 

[aglenn01]

How do I get to make this formula?

 

[wigi]

You need to use the Proper function.

Go to a blank cell, type: =Proper( and click on the cell of which you want to change the case. Enter to confirm the formula.

Now copy copy the cell with the formula a number of times down the column to also convert the other cells.

A copy-paste values will get rid of the formulas and only keep the result.

Wigi

 

[lenze]

Hi and welcome to the board!!
For that many, use this simple VBA code

Sub CorrectCase()
Dim cl As Range
For Each cl In Selection
On Error Resume Next
cl = Application.WorksheetFunction.Proper(cl)
Next cl
End Sub

Open the VBE(ALT+F11) and paste it in the panel. Close the VBE(ALT+Q). Now select the column(s) that need changed. Open the macro dialog(ALT+F8). Select the macro and choose run.
You're done.

lenze

 

[aglenn01]

I understand the =Proper( cell ) but how can I copy this to all the cells in that list?

 

[lenze]

I understand the =Proper( cell ) but how can I copy this to all the cells in that list?

Not sure what you mean. The code I posted doesn't do that, it CORRECTS the cells from ALL CAPS to PROPER. Is that not what you want?

lenze

 

[aglenn01]

Yes, I want all of them to be in Proper case. I guess I'm asking if there is a way to make the cells follow the origional code I typed in as opposed to having to re-type the code for each cell.

 

[wigi]

Either use the automatic code Lenze suggests, either use a semi-automatic approach by using a formula that you copy-paste as values (as I suggested).