How to Reorder Parts of a Number or Date in WPS Office Spreadsheets
Question details
The user needs to automatically rearrange a slash-delimited value such as 123/09/2022 into 2022/09/123 in a spreadsheet.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with data that contains slash-delimited number and date components and wants the year, middle part, and first part to appear in a different order.
- Observed behavior
- The source value currently appears in the original order 123/09/2022, so the components need to be reordered automatically.
Check whether the value is text or a real date. Text values can be split with formulas, while real dates should be reformatted with the TEXT function.
Reorder Slash-Delimited Text with a WPS Spreadsheets Formula
Use this formula when the original value is text and contains three parts separated by slashes.
This method reads the original cell as text and rebuilds it in year/middle/first order. It assumes the year is the last four characters and the other parts are separated by slashes.
Click the cell where the reordered value should appear, for example B1.
Type =RIGHT(A1,4)&"/"&MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1) and press Enter.
Drag the fill handle down or double-click it to apply the formula to the remaining rows.
Select the formula results, press Ctrl+C, then right-click and choose Paste Values to replace formulas with static text.
Reformat a Real Date into Year/Month/Day Order
Use this method when the cell already contains a valid Excel or WPS date serial value.
Reorder Number and Date Parts Directly in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use built-in text and date formulas to rearrange slash-delimited values automatically. You can keep using .xlsx and .xls files without switching to a different spreadsheet app.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the file that contains the values to reorder.
- 2. Identify the source cells: Locate the column containing values such as 123/09/2022 that need to be rearranged.
- 3. Apply the recommended formula: In the result cell, enter the text-splitting formula or the TEXT date formula, replacing A1 with your first source cell.
- 4. Fill and finalize: Press Enter, drag the fill handle down, and paste as values if you no longer need the formulas.
Frequently Asked Questions
How do I know whether my value is text or a real date?
Select the cell and look at the formula bar. If it shows 123/09/2022 exactly, it is text; if it shows a date serial or a date format, use the TEXT method.
Can I reorder more than three slash-separated parts?
The example formula handles three parts. For more parts, you can split the text with additional FIND and MID formulas or use Text to Columns in WPS Spreadsheets and then concatenate the columns in the desired order.
Will the result update automatically when the original value changes?
Yes. The recommended formula references the source cell, so it recalculates automatically. Pasted values will not update.
Why does my formula show #VALUE! when I use the TEXT date method?
That usually means the source is text, not a real date. Use the recommended slash-delimited text formula instead.
Does this work in WPS Office on Windows and mobile?
The formula works in WPS Office Spreadsheets on Windows. On mobile the formula bar is available, but the exact menu layout may differ.
