How to Split Excel Data into Multiple Columns on Another Sheet
Question details
The user needs to split text from a specific column on one sheet into multiple separate columns on another sheet, while also stripping out single and double quotation marks and specific separators.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Reorganizing and cleaning text data by separating strings into distinct columns across different worksheets without transferring unwanted characters.
- Observed behavior
- The text contains delimiters like hyphens and spaces, as well as surrounding quotation marks, which need to be simultaneously removed and used to separate the text into columns C, D, and E.
Verify the exact sheet name of your source data and identify the specific delimiters (like hyphens, commas, or spaces) used in your text before applying the formula.
Use TEXTSPLIT and SUBSTITUTE Functions
Use a nested formula to simultaneously strip unwanted quotation marks and split the text dynamically into adjacent columns.
The TEXTSPLIT function dynamically splits strings across columns based on specific delimiters. By wrapping the source cell reference with the SUBSTITUTE function, you can clean the text of both single and double quotation marks before the split occurs. Note that TEXTSPLIT is available in newer spreadsheet versions such as Microsoft 365, Office 2024, or the latest updates of WPS Office.
Open 'Sheet 2' (or your target sheet) and click on the starting cell where you want the split data to appear, such as cell C3.
Type the formula: =TEXTSPLIT(SUBSTITUTE(SUBSTITUTE('Sheet 1'!C3, "'", ""), """", ""), {"-", " "}). Replace 'Sheet 1' with your actual source sheet name if it differs.
Press Enter. The cleaned data will automatically spill into the adjacent columns (e.g., C, D, and E).
Click the small square at the bottom-right corner of cell C3 and drag the fill handle down to apply the formula to the remaining rows.

Use the Text to Columns Feature
If your software lacks the TEXTSPLIT function, you can manually transfer the data and use the built-in Text to Columns wizard to achieve the same result.
Clean and Split Your Data Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful advanced functions and intuitive built-in tools like Text to Columns, making it effortless to split, clean, and organize complex datasets across multiple sheets.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your text data.
- 2. Navigate to the destination sheet: Click on the worksheet tab where you want the separated data to be placed and select the starting cell.
- 3. Apply formulas or tools: Enter your combined data manipulation formula, or navigate to the Data tab to utilize the Text to Columns feature.
- 4. Adjust column formatting: Resize the new columns to ensure all your separated data and headers are clearly visible.

Frequently Asked Questions
Why am I getting a #NAME? error when using TEXTSPLIT?
This error generally occurs if your spreadsheet software version does not support the TEXTSPLIT function, which was introduced in newer versions like Microsoft 365 or Office 2024. If your version is older, you can update your software or use the 'Text to Columns' feature under the Data tab instead.
Can I use multiple different delimiters in the TEXTSPLIT formula?
Yes, you can specify multiple delimiters by enclosing them in array brackets. For example, using {"-", ",", " "} as the delimiter argument will tell the function to split the text whenever it encounters a hyphen, a comma, or a space.
How do I remove both single and double quotes from my Excel data using formulas?
You can nest the SUBSTITUTE function multiple times. Use SUBSTITUTE(A1, "'", "") to remove single quotes, and then wrap that entire formula inside another SUBSTITUTE function like SUBSTITUTE(..., """", "") to remove double quotes simultaneously.




