How to Convert Numbers to Ordinal Values in WPS Spreadsheets
Question details
The user needs an Excel-compatible formula in WPS Spreadsheets that appends the correct ordinal suffix to numbers, including special cases 11th, 12th, and 13th.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a list of numeric values and wants to display them as ordinal positions or rankings, such as 1st, 2nd, 3rd, and 4th.
- Observed behavior
- The desired result is a text value with the correct ordinal suffix; for example, 1 becomes 1st, 2 becomes 2nd, 3 becomes 3rd, and 11, 12, 13 become 11th, 12th, 13th.
Open WPS Spreadsheets and place the numeric values in a column or row. Check that the source cells contain real numbers rather than text, because the formula uses RIGHT and VALUE to inspect the last digits.
Use an IF formula to add the correct ordinal suffix
Best for WPS Spreadsheets and Excel 2010 or later; it works in a single cell and explicitly handles 11th, 12th, and 13th before checking the final digit.
This formula first checks the last two digits to avoid assigning 11st, 12nd, or 13rd. It then checks the final digit for 1, 2, or 3 and applies st, nd, or rd; all other numbers use th.
Open your WPS Spreadsheets workbook and click the cell where you want the ordinal value to appear, for example B1 if the number is in A1.
Type =IF(AND(VALUE(RIGHT(A1,2))>3,VALUE(RIGHT(A1,2))<21),A1&"th",IF(VALUE(RIGHT(A1,1))=1,A1&"st",IF(VALUE(RIGHT(A1,1))=2,A1&"nd",IF(VALUE(RIGHT(A1,1))=3,A1&"rd",A1&"th")))) and press Enter.
Drag the fill handle from the formula cell downward to apply it to the remaining numbers in your list.
Use a dynamic-array LET and SWITCH formula for newer versions
Use this shorter formula if your WPS Spreadsheets build supports dynamic arrays, LET, and SWITCH; it handles an entire spilled range at once.
Create Ordinal Suffix Formulas in WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible formulas, so you can convert numbers to ordinal values without switching tools. Use the IF formula, dynamic-array functions, or a MOD-based variation to turn plain numbers into 1st, 2nd, 3rd, and 4th with correct teen handling.
- 1. Open your workbook in WPS Spreadsheets: Start WPS Office, choose Spreadsheets, and open the workbook that contains the numbers you want to convert.
- 2. Apply the ordinal formula: In an empty cell next to your first number, enter =IF(AND(VALUE(RIGHT(A1,2))>3,VALUE(RIGHT(A1,2))<21),A1&"th",IF(VALUE(RIGHT(A1,1))=1,A1&"st",IF(VALUE(RIGHT(A1,1))=2,A1&"nd",IF(VALUE(RIGHT(A1,1))=3,A1&"rd",A1&"th")))) and press Enter.
- 3. Fill the formula down: Drag the fill handle downward or double-click it to apply the formula to the remaining cells in the list.
- 4. Save or export in your preferred format: Press Ctrl+S to save the WPS spreadsheet, or use Menu > Save As to export it as an .xlsx file for Microsoft Excel compatibility.
Frequently Asked Questions
Will this formula work in WPS Spreadsheets?
Yes. WPS Spreadsheets supports the same IF, VALUE, RIGHT, LET, and SWITCH functions used in Excel, so you can enter the formula directly in the formula bar. If you use a newer dynamic-array formula, check that your WPS version supports spilled arrays.
Why do 11, 12, and 13 need special handling?
These numbers end in 1, 2, and 3 but use th in English: 11th, 12th, and 13th. The formula checks the last two digits first and applies th when the value is between 4 and 20 in the final two-digit group.
Can I convert an entire column without dragging the fill handle?
Yes. If your WPS version supports dynamic arrays, reference a spilled range such as A1# in the LET and SWITCH formula, or use an array formula. In older versions, you can copy the formula and paste it down the column, or use the fill handle.
How do I return a blank or marker when a cell is empty or not a number?
Wrap the formula in IF(ISNUMBER(A1), ... , "--") or use IF(A1="","", ... ) to avoid errors. The ISNUMBER check ensures that text and empty cells are ignored rather than producing #VALUE! errors.
