If you mean splitting CSV data in Excel, use Data > From Text/CSV to import it and choose the correct delimiter; for data already in one column, use Data > Text to Columns. “C-V method” is not a standard Excel feature name, so this answer treats it as a reference to CSV, or comma-separated values.
What splitting a CSV means
A CSV file stores tabular data as text, usually with commas separating fields and line breaks separating records. For example, a row might look like Chen,Mira,Seattle,98101. When Excel reads that row using commas as delimiters, it places each field in a separate cell. A CSV can also use semicolons, tabs, or another delimiter, depending on how it was created.
People use “split a CSV” to mean different things. They may want to put fields into separate worksheet columns, divide one text column into several columns, or break a very large file into multiple smaller files. The Excel methods below address the first two tasks. Splitting a file into separate output files is a different operation; Excel does not provide a simple one-click command for making evenly sized CSV files.
CSV is a plain-text format, not an Excel workbook format. It does not preserve multiple sheets, formulas, cell formatting, or workbook features. Once the data is arranged as needed, save a working copy as an Excel workbook if you want to keep those features.
Choose the method that fits the data
Before changing anything, decide whether the CSV has been opened as a file or whether its contents are already in a worksheet. The distinction matters: importing lets Excel interpret the source file before it is loaded, while Text to Columns changes data already on the sheet.
- Import a CSV that has not been loaded yet: Use Data > From Text/CSV. This is generally the best choice when you need to select a delimiter, check a preview, or control how columns are interpreted.
- Separate one existing column: Use Data > Text to Columns for a quick, one-time split based on a delimiter or fixed character positions.
- Repeat the same split on refreshed files: Use Power Query, which can save the transformation steps and apply them again when the source changes.
- Create a formula-driven result: Use TEXTSPLIT in Microsoft 365 or Excel 2024 if the source is in a cell and you want the result to update when that cell changes.
Keep an untouched copy of the original data before splitting. Some methods fill cells to the right of the source column, and existing content there may be overwritten or block the result.
Import a CSV with the correct delimiter
In Excel for Microsoft 365 on Windows, open a blank workbook or select the worksheet where you want the data, then choose Data > From Text/CSV. Browse to the file and select it. Excel displays a preview, including a delimiter setting. Choose the delimiter that actually separates the fields—often comma, but sometimes semicolon or tab—then check that the preview shows the intended number of columns before selecting Load.
If the preview keeps the whole row in one column, the selected delimiter may be wrong. If it creates too many columns, the delimiter may also appear inside field values. For instance, a city field such as Portland, Oregon can include a comma that is part of the value rather than a field boundary. A well-formed CSV encloses such a field in quotation marks, as in "Portland, Oregon", and a CSV-aware importer should keep it together. Check the preview rather than assuming every comma marks a split.
Importing is safer than double-clicking a CSV when column types matter. You can inspect the result before loading and, where available, change data type or encoding options. Menu labels and import dialogs can vary slightly by Excel build. On Mac, use the CSV import option under the Data tab when available; the exact dialog and available Power Query features depend on the installed Microsoft 365 version. Excel for the web can open CSV files, but desktop Excel offers more control over import settings.
Use Text to Columns on data already in a sheet
For a one-time split, put the unsplit values in a worksheet column and make sure the cells to its right are empty. Select the cells, then choose Data > Text to Columns. In the wizard, choose Delimited when a character such as a comma or tab separates the fields. Choose Fixed width only when the fields line up at consistent character positions. Select Next, choose the delimiter, and examine the preview. You can set a destination cell if you do not want the output to begin in the original column, then select Finish.
For example, if A2 contains Chen,Mira,Seattle, choosing comma as the delimiter produces three fields: Chen, Mira, and Seattle. If the original column must remain unchanged, set the destination to another empty area, such as C2, before finishing. Otherwise, the split may replace the original values in column A.
Use the wizard’s text qualifier setting when fields are wrapped in quotation marks. That setting helps Excel treat a delimiter inside a quoted value as part of the value, not as a boundary. Review the preview carefully, especially if the source includes quotation marks, commas within names or addresses, or empty fields. Text to Columns changes the selected worksheet data; it is not a live connection to the source file.
Split a cell with TEXTSPLIT
TEXTSPLIT is useful when the source text is in a cell and you want the result to recalculate if the source changes. It is available in Microsoft 365 and Excel 2024. Suppose A2 contains Chen,Mira,Seattle. With the text in A2 and empty cells to the right, enter this formula in B2:
With the text to split in A2, enter =TEXTSPLIT(A2,",") in B2. The result spills across B2:D2 as Chen, Mira, and Seattle.
TEXTSPLIT returns an array, so the cells where the results need to spill must be clear. If they are occupied, Excel may show a spill error. The formula uses a comma as the column delimiter; use the actual delimiter found in the source. For a tab delimiter, the delimiter can be supplied with CHAR(9), as in =TEXTSPLIT(A2,CHAR(9)). This formula is appropriate for straightforward delimited text, but it is not a full replacement for importing a complex CSV with quoted fields containing delimiters.
Unlike Text to Columns, a formula leaves the source text in place and updates its result when the source changes. It is therefore convenient for repeat calculations within a workbook. However, the output is a formula result, not a newly created CSV file, and it depends on a version of Excel that supports TEXTSPLIT.
Use Power Query for repeatable imports
Power Query is useful when you regularly receive files with the same structure or need to clean and reshape data as part of a repeatable process. In Excel for Microsoft 365 on Windows, choose Data > From Text/CSV, select the file, and choose Transform Data rather than loading immediately. In the Power Query Editor, select the column to split and choose Transform > Split Column > By Delimiter. Select the delimiter and review the split options and preview, then choose Close & Load to return the results to Excel.
Power Query records transformation steps. When the source data is updated, you can refresh the query rather than repeat each split manually. That makes it a stronger choice for recurring reports, although it takes longer to learn than Text to Columns. It can also perform other cleanup steps, such as removing unwanted spaces or assigning data types. Feature availability and exact import options differ between Windows, Mac, and web versions, so check the installed Excel build if a command is missing.
Handle delimiters, quotes, and data types carefully
Many apparent splitting errors are actually import or data-type problems. A file can use a different delimiter than expected, and regional settings may affect how Excel interprets separators or dates. A value like 03/04/2026 could represent different dates in different date conventions. If that column must remain exactly as written, import it as text rather than letting Excel guess.
- Leading zeros: Codes such as
00427may become427if interpreted as a number. Import that column as text when the zeros are meaningful. - Long identifiers: Excel has a limit of 15 significant digits for numeric precision. Treat long account or product identifiers as text to avoid losing digits.
- Quoted commas: A comma inside a quoted field should not become a new column in a CSV-aware import. Check the preview or use Power Query for more involved cleanup.
- Empty fields: Consecutive delimiters can indicate a blank field. Make sure the output columns remain in the right positions and do not shift later values.
- Encoding: If accented or non-Latin characters look garbled, select the appropriate file origin or encoding during import if the dialog offers that choice.
After loading, compare a few complete records with the source file. Check both the first and last columns, since a mistaken delimiter can appear to work for much of a row but still shift or merge fields near the end.
Save the result without losing important information
When you save an Excel worksheet as CSV, Excel writes only the active sheet’s cell values in a text format. It does not save the workbook’s other sheets, formatting, formulas as formulas, or Power Query definitions into the CSV. If Excel warns that some workbook features are not compatible with CSV, that is expected. Keep an .xlsx copy if you need to retain the workbook structure or transformations.
When a separate CSV file is required, select File > Save As or File > Save a Copy, depending on the current interface, and choose a CSV format. Review the saved file before sharing it, especially if it contains leading zeros, special characters, or values that Excel may reinterpret. Do not assume that saving as CSV preserves the same display or data types shown in the workbook.
Common problems and how to fix them
If everything appears in one column after opening a CSV, do not immediately run Text to Columns on the wrong data. First check whether the file uses semicolons or tabs instead of commas. Re-importing through Data > From Text/CSV gives you a preview and makes it easier to choose the right separator.
If the split output overwrites neighboring data, undo immediately with Ctrl+Z on Windows, then repeat the operation with an empty destination area. On Mac, use Command+Z. If Text to Columns puts a value into more columns than expected, inspect the delimiter and text qualifier settings. If a formula returns a spill error, clear the cells where its results should appear. If a result loses leading zeros, re-import the affected field as text; changing the display format after Excel has already discarded digits may not restore the original value.
Frequently Asked Questions
Is “C-V method” an Excel term?
No. Excel does not have a standard feature called the C-V method. If the intended term is CSV, it means comma-separated values; the relevant Excel tools are From Text/CSV, Text to Columns, Power Query, and, for simple cell text, TEXTSPLIT.
How do I split a CSV file into columns in Excel?
For a file you have not imported, choose Data > From Text/CSV and select the delimiter shown in the preview. If the text is already in one worksheet column, choose Data > Text to Columns and follow the Delimited wizard.
Can I split CSV data automatically when it changes?
For text in a cell, TEXTSPLIT can update its spilled result when the source changes. For recurring file imports, Power Query is usually more suitable because you can refresh the saved transformation.
Why does Excel remove zeros from my CSV?
Excel may interpret a value such as 00152 as a number and remove its leading zeros. Import that field as text, particularly when it is an identifier rather than a quantity. If the zeros have already been removed and the source is unavailable, Excel may not be able to infer them.
Does splitting data in Excel create a new CSV file?
No. Text to Columns and TEXTSPLIT arrange data in worksheet cells; they do not automatically produce separate output files. You can save a worksheet as CSV, but that exports the active sheet’s values. Dividing one large CSV into multiple files requires a separate file-splitting workflow.
Related
- Balance of Payments Theory: Meaning and How It Works
- Bullseye Graphic in PowerPoint: Make One Cleanly
- Access Split Form: Put the Datasheet on the Bottom
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Insert a Filter in an Excel Column, Step by StepTo insert a filter in an Excel column, click a cell inside your data range, then select Data…
- Flash Fill in Excel: Is There a Formula or Shortcut?Flash Fill in Excel is not a formula: it detects a pattern in your example and fills matching…
- Excel Median IF: Multiple Criteria Made EasyTo calculate a median in Excel based on multiple criteria, use an array formula with the MEDIAN and…
- Attendance Counting Formula in Excel: Count Days PresentIf each student’s daily attendance marks are in B2:AF2 and P means present, use =COUNTIF(B2:AF2,"P") to count days…
- Excel Sum by Date: Formulas for Daily and Monthly TotalsTo sum values for a date in Excel, use SUMIF with the date column as the criteria range…
- COUNTIF Excel: Count Duplicates, Repeats, and MatchesYes. In Excel, COUNTIF counts how many times a value appears: with items in A2:A100, enter =COUNTIF($A$2:$A$100,A2) beside…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.