In Excel, a text delimiter is the character that separates one piece of data from the next, such as a comma between fields in a CSV file or a tab between columns in copied text. To split data already in a worksheet, use Data > Text to Columns; to bring in a delimited file, use Data > From Text/CSV and select the separator that matches the file.
What a text delimiter means in Excel
A delimiter marks a boundary between values. For example, the line Riley,Chen,Seattle contains three fields separated by commas. When Excel reads it as delimited data, it can place Riley, Chen, and Seattle into separate columns. If Excel does not recognize the delimiter, it may put the entire line in one cell or divide it in the wrong places.
The delimiter is not part of the value in the usual case; it is the character Excel uses to decide where one field ends and another begins. Common examples include a comma, tab, semicolon, space, or a custom character such as a vertical bar (|). The right choice depends on how the source data was created. Excel does not have one universal delimiter for every file or every region.
Delimiter settings matter when importing a text file, splitting a column, or preparing a file for another system. They can also affect whether numbers, dates, and names land in the intended columns. Choosing a delimiter is therefore a small but important part of data cleanup.
Common delimiters and when they are used
Comma-separated values (CSV) files commonly use commas, while tab-delimited text often comes from copying a table or exporting data from a database. Other separators are useful when the values themselves contain commas or when a particular program expects a specific format.
- Comma: Common in CSV files, such as
SKU,Description,Quantity. - Tab: Common in text copied from tables and in tab-separated files. A tab is a character, even though it looks like a space on screen.
- Semicolon: Used by some applications and in some regional settings, where a comma may already be used as a decimal mark.
- Space: Useful for consistently space-separated values, but risky when a field can contain multiple words, such as a full name or street address.
- Custom character: A pipe, caret, or other symbol can separate fields when that symbol does not appear in the data.
Consider 104|Cable|Blue|12. If the pipe is the separator, the row has four fields. If you select comma instead, Excel has no reason to divide that line at all. A delimiter should be chosen from the actual data or the source system’s export specification, not just from the file extension.
Split a column with Text to Columns
Text to Columns is useful when the data is already in a worksheet and you want to divide it into neighboring columns. In Excel for Microsoft 365 on Windows, select the cells, then choose Data > Text to Columns. In the wizard, select Delimited, choose the relevant separator, preview the result, and complete the steps. On Mac, the command is also available on the Data tab; labels and dialog layout can differ slightly by release.
- Select the source cells, preferably a single column containing the text to split.
- Choose Delimited rather than Fixed width. Fixed width is for dividing at set character positions, not at a repeated separator.
- Check the delimiter that appears between fields. Select Other and enter a character if the separator is not listed.
- Review the preview. If the number and position of the resulting columns look right, continue.
- Set a destination if you do not want the results to overwrite cells beside the original data, then finish.
For example, splitting Chen;Riley;Seattle with semicolon selected creates three fields. Before completing the operation, check that the destination cells are empty: splitting a column can overwrite existing content to its right. If the source column contains values you need to preserve, copy it first or specify another destination. Text to Columns changes the selected data in place; it is not a live connection that updates automatically when the original text changes.
Import a delimited text or CSV file
For a file that is not yet in the workbook, use Data > From Text/CSV in Excel for Microsoft 365 on Windows. Browse to the file and select it. Excel displays a preview and provides a delimiter choice; select the separator that produces the intended columns, then choose Load to add the data or Transform Data to inspect and adjust it in Power Query before loading.
Do not assume that a file ending in .csv will always be separated by commas. CSV means comma-separated values by name, but exported text files and regional conventions can use other separators. Check the preview: a correct selection should put each field in its own column and keep each row aligned. If the file is tab-separated or pipe-separated, select that delimiter instead of relying on the extension.
Importing is often safer than opening a text file by double-clicking it when Excel might guess the format incorrectly. In the import preview or Power Query, you can review column types and adjust how values are interpreted. This matters for postal codes with leading zeros, long identifiers, and dates that could be read differently under different regional conventions. If preserving an identifier exactly is important, inspect its type before loading rather than assuming Excel will treat it as text.
Choose the separator and account for quoted text
Choose the delimiter that consistently separates fields while appearing as little as possible inside the values. If a product description includes commas, a comma-separated file may enclose that field in quotation marks, as in 27,"Cable, blue, 2 m",12. The commas inside the quoted description are part of the field; they should not create extra columns. Proper CSV readers recognize quotation marks used this way, but a simple split on every comma may not.
Excel’s text import tools include options for recognizing a text qualifier, commonly a double quote. When a preview breaks a quoted value into unwanted pieces, check the text qualifier setting as well as the delimiter. A file that uses quotation marks inconsistently or does not follow a standard quoting convention may need correction at the source. Removing quote characters blindly can make matters worse because some quotes are structural and some may be literal data.
Whitespace deserves special attention. Selecting both a space and a comma as delimiters can split a multiword field such as “New York” into two columns. If the file uses commas between fields and spaces within values, select only the comma. Similarly, using a space delimiter on names can split a name into more parts than expected, especially when people have middle names or compound surnames.
Use a formula when you want a repeatable split
For a split that should update when the source text changes, a formula can be more convenient than Text to Columns. Microsoft 365 and Excel 2024 include TEXTSPLIT, which returns separated pieces into adjacent cells when space is available. With the source string in A2 and a comma between each field, enter =TEXTSPLIT(A2,",") in an empty cell. For Riley,Chen,Seattle, the result spills across three cells: Riley, Chen, and Seattle.
TEXTSPLIT works well for consistently delimited text, but it is not a universal substitute for a full CSV parser. In particular, a basic formula split should not be relied on to interpret every CSV quoting and escape rule. If the source has quoted commas, irregular rows, or complex text qualifiers, use the import tools and inspect the preview. If the formula returns a spill error, clear the cells where the results need to appear.
Excel’s formula argument separator is a separate issue from the delimiter inside the text. The comma inside the quoted string in the example is the data separator being searched for. Depending on regional settings, Excel may display formula arguments with semicolons instead of commas. That setting does not change the meaning of the comma characters in an imported file.
Common delimiter problems and fixes
- Everything appears in one column: The selected delimiter may not match the file. Reopen the import options or use Text to Columns and select the actual separator.
- Values split into too many columns: A character selected as a delimiter may also occur inside values. Check whether fields are quoted, or choose a separator that is not part of the data.
- Names or addresses split unexpectedly: Avoid using a space as the delimiter when fields can contain spaces. Use the separator specified by the source, or a more targeted cleanup method.
- Leading zeros disappear: Excel may interpret a field as a number. Import the relevant column as text when available, or set its format appropriately before relying on it as an identifier.
- Dates appear reversed or changed: The import interpretation may not match the date format in the source. Review the preview and column data types, and use a consistent source format.
- Text to Columns overwrites nearby values: The split output needs room to the right. Move the results to a clear area or set a different destination before completing the wizard.
When a delimiter problem keeps recurring, fix the export settings in the program that created the file if possible. Consistent source data is easier to import and less likely to require manual repair each time. Keep an unchanged copy of important source files so you can compare the imported result with the original.
Delimiter versus file type and formula separators
A delimiter is the character separating fields; CSV or TXT is a file format or extension. A text file can use tabs, commas, or another separator regardless of whether its name ends in .txt or .csv. Conversely, the extension alone does not guarantee that the content follows a particular convention. The preview or a look at the raw text is often the quickest way to identify the separator.
Do not confuse a data delimiter with the punctuation Excel uses between function arguments. In a formula such as =SUM(A1,A2), the comma separates arguments in a comma-based formula locale. It does not mean that the values in A1 or A2 are comma-delimited. Regional settings can change the formula argument separator, while the file’s own delimiter is determined by the text data and import options.
Frequently Asked Questions
What is the default delimiter in Excel?
There is no single default that applies to every task. The delimiter depends on the file or the operation: CSV data commonly uses commas, copied tables often use tabs, and Text to Columns lets you choose the separator.
How do I change the delimiter in Excel?
For worksheet data, select the cells and choose Data > Text to Columns, then select the desired delimiter. For an external text file, use Data > From Text/CSV and choose the separator in the preview.
Why does my CSV open in one Excel column?
The file may use a delimiter other than the one Excel assumed, or the import may have been interpreted using different regional settings. Import it through Data > From Text/CSV and select the separator that makes the preview line up into fields.
Can a delimiter be more than one character?
Excel’s standard Text to Columns delimiter choices are individual characters, and its custom delimiter is entered as a character. If the source uses a multi-character marker, you may need to replace it with a single character or transform the text before splitting.
Does Excel treat spaces as delimiters?
It can, if you select the space option in Text to Columns. Use that only when spaces reliably separate fields; otherwise, words within names, addresses, or descriptions can be split unintentionally.
Related
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
- Data Validation Reference Table in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add Footnotes in Word: Desktop and WebTo add a footnote in Microsoft Word, click where its number should appear, choose References > Insert Footnote,…
- How to Double Space in Word, Step by StepTo double-space text in Microsoft Word, select it and choose Home > Line and Paragraph Spacing > 2.0.…
- Excel Table Styles: How to Apply and Customize ThemAn Excel table style is a coordinated set of formatting applied to an Excel table, such as a…
- Excel Key to Delete a Row: Shortcut and OptionsIn Excel for Microsoft 365 on Windows, press Shift+Space to select the current row, then press Ctrl+- (Ctrl…
- How to Freeze Panes in Excel: Keep Rows and ColumnsTo freeze panes in Excel for Microsoft 365 on Windows, select the cell immediately below the rows and…
- How to Keep Duplicate Values in Excel Without Losing DataTo keep repeated entries in Excel, do not run Data > Remove Duplicates on the data you need…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.