1

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.

6 Spice ups
2
1ST SMITH STREET converts to 1st Smith Street
3RD PORTAGE WAY NORTH converts to 3rd Portage Way North
4TH STREET converts to 4th Street

Something like this might work.

This formula locates the first space in the text; converts the text before the space to lower; converts the space after the text to proper.

=LEFT(A2,(FIND(" “,A2)))&PROPER(RIGHT(A2,LEN(A2)-FIND(” ",A2)))

3

So do I put this in B1, if the original address in all caps is in A1?

4

Yes. Paste the following formula into B1: =LOWER(LEFT(A1,(FIND(" “,A1))))&PROPER(RIGHT(A1,LEN(A1)-FIND(” ",A1)))

Edit: Formula to convert to lowercase was omitted. Sorry about that.

1 Spice up
5

I actually figured it out through some online investigation. I was able to get it to work using the formula below. Thanks for your input.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A1),“0Th”,“0th”),“1St”,“1st”),“3Rd”,“3rd”),“4Th”,“4th”),“5Th”,“5th”),“6Th”,“6th”),“7Th”,“7th”),“8Th”,“8th”),“9Th”,“9th”),“1Th”,“1th”),“2Th”,“2th”),“3Th”,“3th”),“Ne”,“NE”),“Nw”,“NW”),“Se”,“SE”),“Sw”,“SW”)

6

This formula won’t work in instances where the addresses could include these combinations within the street name. For example, SEAGATE BLVD could be converted to SEagate Blvd. Honestly the formula that mondrian_cube provided is more dynamic.

@ldonaldson