Upper/Lower Case in text

[hiteshkataria]

Hi,

I have data in cell A1 as below:
36, TREE ROAD, 5TH FLOOR

I want it in proper format in cell A2 as below:
36, Tree Road, 5th Floor

I used formula =Proper(A1), it gave results as:
36, Tree Road, 5Th Floor

The only problem I face is after any number, the next character should in lowercase, i.e. 5th

Your help is highly appreciated.

Thanks.

 

[pgc01]

I tried to use the below code again, but it shows an error in cells as #NAME?

This usually happens when you do not paste the code in a general module. Insert a module in the menu Insert->Module (if it's the first one it will be called Module1) and paste the code there.