Needed a formula to convert an address from all upper case to normal address format

Stumpped

New Member
Joined
Dec 3, 2009
Messages
36
Office Version
  1. 2019
I have tried applying "PROPER" to the address, and it comes close, but is not quite what I am looking for.
For instance, the address was originally: 64875 234TH AVE CT SE
This is becoming far more complicated than I'd like it to be, but here we go.....

So, when cell B807 pulls its data from cell M576, the address is 64875 234TH AVE CT SE
When the formula is applied, =PROPER(M576) it comes up with 64875 234Th Ave Ct Se (Actual address not provided due to privacy of homeonwer)
The problem with this is that the Th after the 234, should be all lowercase.
The reason that this is becoming more complicated is that the Se should be all capitals like SE
So truly converting this to a combination of upper case when needed, lower case when needed and a combination of the two is needed.
Because streets, courts, avenues, etc should all be a combination of upper case for the abbreviation, the directions, such as South, South East, South West, etc should all be capitalized like S, SW, SE, NE, NW, etc but the
letters after the street name should be all lowercase like 68th, 43rd, 21st, etc. Is there a way to do this without using VBA?

I finally figured out how to use the xl2bb in excel. here is what I am looking to do.
ALAMODE WORKSHEET RE WORKING SHEET CALCULATIONS FOR APPRAISAL NARRATIVES AND IMPORTING DATA INTO APPRAISAL SOFTWARE.xlsx
ABC
863PROPERTY INFORMATION
864PROPERTY ADDRESS
865
86664875 234TH AVE CT SE64875 234Th Ave Ct Se
867Desired result64875 234th Ave Ct SE
IMPORT PAGE
Cell Formulas
RangeFormula
B866B866=PROPER(A866)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I don't think you can do this without VBA--you've already noted Excel's text manipulation limitations.

I think you need to set up a table of conditions, much as you have, (st, nd, rd, th in lowercase; S, SE, SW, E, NW, N, NE, W in uppercase and so forth, and then plug them into a global search and replace. You can do that with VBA. Maybe someone can tell me I'm wrong
 
Upvote 0
Unfortunately, I cannot do VBA code for this project. It is being imported into my software which uses pretty much all of Excel's formula functions but does not support VBA. It is my appraisal software with a built-in worksheet, but no extensive capabilities like VBA. It would probably be easier to convert all the addresses to all CAPS and leave it at that. I was just trying to make my reports look nice, that's all.
 
Upvote 0
I think you're right. I fooled around a bit with some text formulas, but rapidly found myself going down a rabbit hole.
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,341
Members
452,638
Latest member
Oluwabukunmi

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
Back
Top