Excel Formula to Format an Address and Preserve State Abbreviations
Question details
The user needs to convert an uppercase address string to title case, while keeping the two-letter state abbreviation fully capitalized and simultaneously removing the trailing ZIP code.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning and standardizing raw address data in a spreadsheet for better readability and formatting.
- Observed behavior
- The address needs to be formatted as Proper Case, but standard text casing functions lower the state abbreviation, and the ZIP code needs to be stripped from the final result.
Ensure your address data follows a consistent pattern at the end of the string, such as a space, a two-letter state abbreviation, another space, and a fixed-length ZIP code, as the formula relies on exact character counts.
Use a Combination of PROPER, LEFT, RIGHT, and LEN Functions
This formula isolates the street and city for proper casing, then appends the uppercase state abbreviation while discarding the ZIP code.
This specific solution assumes the address string ends with a strict pattern: a space, a two-letter state abbreviation, another space, and a six-character ZIP code (e.g., ' TX 123456', which totals 9 characters). It uses the LEN function to determine string length, LEFT to trim the end, and PROPER to fix the casing.
Click on an empty cell adjacent to your raw address data where you want the newly formatted address to appear.
Type the formula =PROPER(LEFT(C3,LEN(C3)-9))&LEFT(RIGHT(C3,9),2) into the formula bar, replacing 'C3' with the actual cell reference containing your address.
Press Enter to execute the formula. You should see the address in title case, with an uppercase state abbreviation and no ZIP code.
Click and drag the fill handle (the small square at the bottom-right of the cell) down the column to apply this formatting to the rest of your address list.

Clean and Format Address Data Using WPS Spreadsheet
WPS Spreadsheet provides powerful text functions and data processing tools, allowing you to clean, extract, and format complex address strings seamlessly. It is fully compatible with all standard Excel formulas, making data management simple and efficient.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your address data.
- 2. Select an output cell: Click on the cell right next to your raw uppercase address.
- 3. Input the exact formula: Type =PROPER(LEFT(C3,LEN(C3)-9))&LEFT(RIGHT(C3,9),2) into the formula bar and press Enter.
- 4. Drag to autofill: Double-click or drag the fill handle at the bottom-right corner of the cell to format your entire column automatically.

Frequently Asked Questions
How do I modify the formula if my ZIP code is 5 digits instead of 6?
If the ZIP code is exactly 5 digits and preceded by a space (e.g., ' TX 12345', which is 8 characters at the end), change the formula to subtract 8 instead of 9: =PROPER(LEFT(C3,LEN(C3)-8))&LEFT(RIGHT(C3,8),2).
What does the PROPER function do in this formula?
The PROPER function converts the first letter of each word in a text string to uppercase and changes all other letters to lowercase. This is ideal for standardizing street and city names into title case.
Can I use Flash Fill instead of a formula to format addresses?
Yes. In modern spreadsheet applications, you can manually type the desired formatted address (e.g., '1234 Main St, City TX') in the first adjacent cell, press Enter, and then use the Flash Fill feature (Ctrl+E) to let the program automatically detect the pattern and fill the rest of the column.




