How to Extract Multiple Name and Date Pairs from One Cell in WPS Spreadsheets
Question details
The user needs to parse repeated name-and-date pairs from a single cell that contains multiple lines or structured text into separate name and date columns.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a spreadsheet cell containing several 'Name, Date' entries, one per line or separated by a consistent delimiter, and wants to use them for sorting, filtering, or reporting.
- Observed behavior
- The repeated name and date pairs remain combined in a single cell and cannot be used as structured data until extracted.
Check whether each name/date pair is on its own line and confirm the delimiter between the name and date, such as a comma or semicolon. If date formats are inconsistent, standardize them before splitting.
Import the cell text as a delimited text file to create name and date rows
Use this method when each pair is on a separate line inside the cell and the name/date values are separated by a comma. WPS Spreadsheets can read the pasted text as rows and split at the comma in one import.
Select the WPS Spreadsheets cell containing the name/date pairs and press Ctrl + C to copy it.
Open a plain text editor such as Notepad, paste the copied content, and save it as a .txt file with UTF-8 encoding.
In WPS Spreadsheets, click the Menu button and choose Open, then select the saved .txt file.
In the text import wizard, select Delimited, click Next, check Comma, and click Finish.
WPS puts each original line into its own row, with names in the first column and dates in the adjacent column.
Use Text to Columns and Paste Transpose for an all-in-sheet method
Use this alternative when you do not want to create a separate file and prefer to keep the entire operation inside the same WPS Spreadsheets worksheet.
Convert JSON or structured text to CSV before importing
Use this method when the cell contains JSON-like records or deeply structured text that cannot be split cleanly by a single delimiter.
Extract Name and Date Pairs with WPS Spreadsheets Text Tools
Use the built-in text import and Text to Columns tools in WPS Spreadsheets to split a single cell into separate name and date columns without a complex formula.
- 1. Open the worksheet: Open WPS Spreadsheets and select the cell or copy the cell content that contains the multiple name/date pairs.
- 2. Use the built-in split or import tool: Go to Data > Text to Columns for an in-sheet split, or paste the content into a .txt file and open it via Menu > Open.
- 3. Choose the delimiter: Select Comma as the delimiter for name/date separation, and use line break selection if you are splitting by lines first.
- 4. Complete the wizard and check the result: Click Finish and review the resulting rows and columns, then format the date column as Date if needed.
Frequently Asked Questions
Can WPS Spreadsheets split a cell by line break into rows?
Yes. You can paste the cell content into a .txt file and import it with WPS Spreadsheets, or use Data > Text to Columns with Ctrl + J as the delimiter and then Paste Special > Transpose to turn the split columns into rows.
What if my name and date pairs use a semicolon or another delimiter?
Use the same text import or Text to Columns wizard, but choose Semicolon as the delimiter. For a custom character, select Other and enter that character in the delimiter box.
Will the extracted dates remain usable for sorting and filtering?
Yes. After splitting, select the date column, click Home > Format Cells, choose Date, and apply the desired date format so WPS Spreadsheets treats the values as dates.
Does WPS Spreadsheets support JSON parsing like Excel Power Query?
WPS Spreadsheets may not expose the same native JSON transformation pane as Excel Power Query in all versions. For simple JSON-like structures, copy the records to a plain text editor, save as a .csv or .txt file, and open it through the WPS text import wizard.
