Function Problems

How to Use TEXTSPLIT in Excel to Extract Data from CSV Reports

Rana Garcia Oct 21, 2026 868 views

Question details

The user needs to create a reusable Excel workbook that processes monthly CSV reports by splitting delimited text, ignoring unwanted columns, and extracting specific data to a separate worksheet.

How to Extract and Filter CSV Data Using TEXTSPLIT in Excel

Product: Excel
Device & OS: not provided
Scenario: Handling recurring monthly CSV data dumps where text needs to be split dynamically and specific columns must be isolated for final reporting.
Observed behavior: The user is looking for an automated formula-based approach, utilizing TEXTSPLIT or Power Query, to replace manual text-to-columns processing and row deletion.

Before you start

Verify that you are using Microsoft 365 or Excel for the Web, as the TEXTSPLIT function is not available in standalone versions like Excel 2019 or 2021. Also, ensure you have a sample of your monthly CSV report accessible to test your delimiters.

Solution 1Recommended

Use TEXTSPLIT Combined with CHOOSECOLS Formula

This method uses dynamic array formulas to parse the CSV string and immediately filter out the columns you do not need, making it great for dashboard templates.

By nesting TEXTSPLIT inside the CHOOSECOLS function, you can split a comma-separated text string into an array and selectively return only the columns that matter for your report.

Import or Paste Raw Data

Paste your raw CSV text strings into Column A of your dedicated raw data worksheet (e.g., A2 down to A100).

Apply the TEXTSPLIT Function

In your reporting worksheet, select the first destination cell and type '=TEXTSPLIT(Sheet1!A2, ",")' to separate the text using a comma delimiter.

Filter Unwanted Columns

Wrap the formula in the CHOOSECOLS function to specify which columns to keep. For example, '=CHOOSECOLS(TEXTSPLIT(Sheet1!A2, ","), 1, 3, 5)' will extract only the 1st, 3rd, and 5th columns.

Process Multiple Rows

Drag the formula down to apply it to all rows, or use the MAP/REDUCE functions to evaluate the entire range of raw data dynamically.

Use TEXTSPLIT Combined with CHOOSECOLS Formula

Handling Different Delimiters: If your CSV file uses semicolons or tabs instead of commas, simply change the delimiter argument in the quotes, such as ";" or CHAR(9) for tabs.

Solution 2

Automate Data Extraction Using Power Query

Power Query is the most robust solution for processing recurring monthly CSV reports, allowing you to set up a transformation process once and refresh it every month.

Efficient Data Processing

Effortlessly Extract and Split CSV Data in WPS Spreadsheet

WPS Spreadsheet provides powerful text-processing capabilities and intuitive data tools, allowing you to easily split CSV reports and extract specific columns without needing complex dynamic arrays.

1. Open CSV Directly: Launch WPS Spreadsheet and open your monthly CSV file directly; WPS will automatically detect basic delimiters.
2. Use Text to Columns: If the data is in a single string, select the column, go to the 'Data' tab, and click 'Text to Columns' to split the text by your specific delimiter.
3. Filter the View: Simply delete or hide the columns you do not need, or use lookup functions in a separate sheet to pull only the required columns from the parsed dataset.

Seamless format compatibility with Microsoft Excel CSV and XLSX filesAdvanced built-in text parsing tools to handle complex delimitersIntuitive Text-to-Columns feature and powerful lookup formulasLightweight, fast, and completely free to use for your daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is the TEXTSPLIT function returning a #NAME? error in my Excel?

The TEXTSPLIT function is exclusively available in Microsoft 365 and Excel for the Web. If you are using Excel 2021, 2019, or an older version, the software will not recognize the function and will return a #NAME? error. In this case, you should use the Text-to-Columns feature or Power Query.

How do I ignore empty cells when using TEXTSPLIT?

You can use the 'ignore_empty' argument in the TEXTSPLIT function. Set it to TRUE (for example: =TEXTSPLIT(A2, ",", , TRUE)) to skip consecutive delimiters and prevent empty cells from appearing in your extracted array.

Can I use multiple different delimiters at once with TEXTSPLIT?

Yes, you can pass an array constant of delimiters enclosed in curly brackets. For instance, using =TEXTSPLIT(A2, {",", ";", "-"}) will split the text string whenever a comma, semicolon, or hyphen is encountered.

What is the best alternative to TEXTSPLIT for reusable templates?

Using Power Query (via Data > Get & Transform Data) is widely considered the best method for recurring monthly reports. Once you establish the steps to split columns and remove unwanted data, you can process future CSV files simply by clicking 'Refresh All', without needing to adjust any formulas.

Excel & Data Problems