Correct Ordinal Suffixes After PROPER in Excel
Question details
The user needs to keep ordinal suffixes lowercase when applying PROPER to text in Excel.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Formatting text containing ordinal numbers such as 1ST and 2ND with the PROPER function.
- Observed behavior
- PROPER changes ordinal suffixes to mixed case, producing values such as 1St and 2Nd.
Identify the cell containing the original text and check which ordinal suffixes appear in your data. The example below assumes the original text is in A1.
Use SUBSTITUTE to correct suffixes after PROPER
Use this formula when you want corrected text to update automatically as the source cell changes.
In an empty cell such as B1, enter =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A1),"1St","1st"),"2Nd","2nd"),"3Rd","3rd"),"4Th","4th") and press Enter.
If your data includes other endings, wrap the formula in additional SUBSTITUTE functions. For example, use SUBSTITUTE(existing_formula,"5Th","5th") for 5th and SUBSTITUTE(existing_formula,"1Th","1th") for 11th.
Select B1 and drag its fill handle down alongside the source cells to apply the formula to the remaining rows.

Correct a column with Excel Flash Fill
Use Flash Fill when you prefer to provide corrected examples instead of maintaining a formula.
Fix ordinal suffixes with a formula in WPS Spreadsheets
Use PROPER and SUBSTITUTE in WPS Spreadsheets to format text while restoring lowercase ordinal suffixes.
- 1. Open your spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Enter the correction formula: In an empty cell such as B1, enter =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A1),"1St","1st"),"2Nd","2nd"),"3Rd","3rd"),"4Th","4th") and press Enter.
- 3. Extend the formula if needed: Add another SUBSTITUTE around the formula for each other ending in your data, such as SUBSTITUTE(existing_formula,"5Th","5th").
- 4. Apply it to other rows: Select the formula cell and drag its fill handle down the column, then review the corrected text.

Frequently Asked Questions
Why does PROPER change 1ST to 1St?
PROPER capitalizes the first letter of a word and lowercases the rest. After a number, it treats the suffix as a word, so 1ST becomes 1St.
Will the example formula correct 11th and 12th?
Not by itself. Add SUBSTITUTE functions for 1Th and 2Th to correct the endings produced by PROPER for 11TH and 12TH.
How do I apply the formula to an entire column?
Enter the formula beside the first source cell, select the formula cell, and drag its fill handle down alongside the remaining rows.
Does Flash Fill update when the original text changes?
No. Flash Fill produces values rather than formulas. Use the SUBSTITUTE formula if the corrected text needs to update automatically.




