Using an excel file with ALL CAPS problem

aglenn01

New Member
Joined
May 4, 2009
Messages
6
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

Hi and welcome to the board!!
For that many, use this simple VBA code
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

New Member
Joined
May 4, 2009
Messages
6
I understand the =Proper( cell ) but how can I copy this to all the cells in that list?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

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

New Member
Joined
May 4, 2009
Messages
6
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,709
Messages
5,597,697
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top