Function Problems

Excel Formula to Format an Address and Preserve State Abbreviations

Steve KSteve K Nov 4, 2026 868 views

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.

How to Format an Address and Preserve State Abbreviations in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on an empty cell adjacent to your raw address data where you want the newly formatted address to appear.

2
Enter the formatting formula

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.

3
Apply the formula

Press Enter to execute the formula. You should see the address in title case, with an uppercase state abbreviation and no ZIP code.

4
Fill the remaining cells

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.

Use a Combination of PROPER, LEFT, RIGHT, and LEN Functions
Data Consistency Matters: If your ZIP codes vary in length (e.g., 5 digits instead of 6) or if there are irregular spaces, this exact formula may cut off the wrong characters. You will need to adjust the static numbers '9' and '2' based on the exact character count at the end of your strings.
Format Data Easily in WPS Spreadsheet

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. 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your address data.
  2. 2. Select an output cell: Click on the cell right next to your raw uppercase address.
  3. 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. 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.
Fully compatible with Microsoft Excel formulas like PROPER, LEFT, RIGHT, and LENLightweight and fast, even when processing large address datasetsIncludes free built-in text-to-columns and data extraction toolsFamiliar user interface requires no learning curve
microsoft office alternative - wps office

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.