Excel Title Case Formula

[jeffv]

So, I have a spreadsheet with a couple of thousand addresses that are all in upper case. In the column to the right of the address, I'm using the formula =PROPER(A1) and that does a pretty good job of converting it to title case. My major issue with it is for those addresses like 1st, 2nd, 3rd, 4th street because it comes out like 1St St., which looks kind of weird. Is there any way around that?

Thanks.

[Arkadi]

Give this a shot:

Please Login or Register  to view this content.

[Arkadi]

Oops... you will need to add 3 Substitute layers... 1Th (for 11th for example), 2Th, 3Th

[jeffv]

Thanks. We will have some on 240th St., 265th St. Will I have to add even more substitute layers to go all the way up to that?

[Arkadi]

No... 240th still contains "0th" so it still gets replaced.

Only need the last digit before the ordinal letters ("th" "st" etc.)... So there are 13 cases and I did 10 of them you just need to add the 3 that I mentioned above because I missed those.

Actually, I just did it... as long as I made no typos it should work:

Please Login or Register  to view this content.

[jeffv]

Perfect, that worked. Thank you so much.

[jeffv]

Sorry, I forgot about SW, NE, etc. at the end of an address. Do I just include those into that formula somewhere?

[Arkadi]

Well... sw and ne could be part of a word... so that's a harder one.... I suppose SW and NE would always have a space in front of them, but words could start with those letters too.... so it depends on whether we can find a unique condition such as space-NE or space-SW are always followed by a space, or a period or something else that would ensure it is not the start of a word

[jeffv]

Disregard, I just added those to the end and added a SUBSTITUTE parameter for each one and it's working. Thanks again for your help.

[Arkadi]

Yes but with a straight substitute, Ne for NE, would mean Nebraska becomes NEbraska for example?