Excel Formula to Change Address Format

Question

In Excel, I need to change the way the "PROPER" function outputs an address. I want to be able to copy the data from my working spreadsheet into another sheet so that it will convert the data into the format of another spreadsheet that I need to enter it into. (I know, lots of copy and pasting. Saves more time than retyping it all.)

I enter the original data in all caps: 1234 MAIN ST, CITY TX 123456

I copy that data into another tab to format it, so that I can copy it into an email message as well as into another spreadsheet. I use the "PROPER" function for that, which gives me: 1234 Main St, City Tx 123456

I need to have the state as two capital letters and to drop the zip code: 1234 Main St, City TX

I have tried the "SUBSTITUTE" and "REPLACE" functions without success. Can anyone please help me with the formula to accomplish this? Any guidance is much appreciated!

Answer 1

assuming that the address will always end with <2 letters for State><space><6 characters for zip code>

say your uppercase address is in c3,

use the formula and copy down

=PROPER(LEFT(C3,LEN(C3)-9)) &LEFT(RIGHT(C3,9),2)

Answer 2

Hi NK_2021,

I apologize for all the trouble it has caused and sorry about your time.

As per your description, to add “xl_geek” reply here  is screenshot for your reference:

 

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad

Answer 3

That works perfectly! Thank you so much! I had read a bit about this type of formula, but have never used it so was honestly trying to save some time. YOU ROCK!