Text Case issue

**tshirttom** · 04-04-2008, 12:20 PM

I need to find a way of changing an all Caps address to an address that has a cap in the first letter only.

For example:
convert 44 HARRISON RD to 44 Harrison Rd

This might also be accomplished by converting to an Access database.

Thank you for any help in coding this

tshirttom

**Portuga** · 04-04-2008, 12:30 PM

Use the "Proper" formula

=Proper(A1)

**tshirttom** · 04-06-2008, 01:34 PM

when I put that in a cell I got an error message that I had created a circular reference. When I pasted an address in, it remains all in CAPS.

Thanks for your time,
Tom

**mikerickson** · 04-06-2008, 02:16 PM

That formula should be not be put in A1.

Excel spreadsheet formulas do not replace cell contents with the results. The original column of bad addresses is needed for the formula to have data to evaluate.

If you have a column of UPPERCASE addresses in column A, one way to change it to Proper Case would be to put Portiga's
=PROPER(A1) in cell B1 and drag the formula down to the end of the column.

Then select the (corrected) addresses in column B and copy that and use PasteSpecial/Values to replace column A with the Proper Addresses.

**martindwilson** · 04-06-2008, 02:17 PM

It doesnt go in the same cell!
that function assumes your address is in a1 so you put the formula in an empty cell preferably on the same row (so they line up)and it will change it
and show it in that new cell

**Portuga** · 04-07-2008, 09:54 AM

if "44 HARRISON RD to 44 Harrison Rd" is on cell A1 you use the formula in ANY OTHER CELL than A1

=proper(a1)

If "44 HARRISON RD to 44 Harrison Rd" is on B1 you use the formula in ANY OTHER CELL than B1

=proper(b1)