Excel Text to Columns: What the Text Qualifier Does

How-to basics · Updated November 21, 2026

In Excel for Microsoft 365 on Windows, the Text qualifier in Data > Text to Columns tells Excel which character can enclose a text field; the default is a double quotation mark ("). When you split delimited data, Excel treats a delimiter inside a matching pair of qualifiers as part of the field, rather than as a point where it should split.

What a text qualifier means

A text qualifier is a character placed around a field to mark its boundaries. It is especially useful when a field contains the same character used to separate columns. In a comma-delimited file, for example, a comma normally indicates the start of another field. If a name or address is enclosed in quotation marks, Excel can distinguish a comma within that value from a comma between fields.

Consider this row of comma-delimited data: 1042,"Portland, Oregon",Active. With the comma selected as the delimiter and the double quote selected as the text qualifier, Excel can split the row into three values: 1042, Portland, Oregon, and Active. The comma between Portland and Oregon stays inside one value. Without the qualifier, Excel may treat that comma as another separator and produce an extra column.

The qualifier usually does not become part of the resulting value. Its job is to guide parsing, not to add punctuation to the cell. That is why a value written as "Portland, Oregon" generally appears in the worksheet as Portland, Oregon, without the enclosing quotation marks.

Where to find the setting

In the Windows desktop version of Excel for Microsoft 365, select the cells or column you want to split, then open Data > Text to Columns. In the Convert Text to Columns Wizard, choose Delimited and select Next. The next screen contains delimiter choices and the Text qualifier dropdown. By default, the dropdown uses a double quote. Choose a different option if your source data consistently uses another enclosing character.

  1. Select the source column or cells. If the source is in one column, selecting that column is common, but make sure the cells to its right are available for the output.
  2. Choose Data > Text to Columns, select Delimited, and click Next.
  3. Select the delimiter that separates fields, such as comma, tab, semicolon, or a custom character.
  4. Set Text qualifier to the character used around quoted fields, or choose {none} when the data has no qualifiers.
  5. Preview the split, continue to the final wizard screen, and review the destination and column data formats before selecting Finish.

The wizard also offers a Fixed width option for data whose columns line up at consistent character positions rather than being separated by a delimiter. The text-qualifier choice is most relevant when parsing delimited text; it does not define where fixed-width breaks occur. Excel for Mac also has a Text to Columns feature, though the dialog presentation can differ by release. The steps above describe Microsoft 365 for Windows.

How Excel uses the qualifier

Excel reads a delimited row from left to right. When it encounters the selected qualifier at the start of a field, it treats delimiters encountered inside that quoted field as ordinary characters. The field ends at the matching qualifier; a delimiter after that point can separate the next field. This is why selecting the right qualifier matters even when the delimiter itself is correct.

For instance, with comma as the delimiter and double quote as the qualifier, 7,"Unit 4, Building B",Open represents three fields. The comma inside the quoted address does not split it. A delimiter that is not enclosed by the selected qualifier still separates fields. Text in quotation marks elsewhere in a value may not behave as intended if the quotation marks are unbalanced or do not follow the structure Excel expects.

Common data formats use double quotes to enclose text fields. Some files use single quotes instead, so the wizard provides that option as well. The qualifier is not the same thing as the delimiter: a comma, tab, or semicolon divides fields, while the qualifier groups text that may contain that dividing character.

How to choose the right option

Choose the qualifier by inspecting the actual source data, not by guessing from the file extension. A CSV file commonly uses double quotes, but the format and the software that created the file determine how its fields are represented. Look at several representative rows, including ones with commas, tabs, or other punctuation inside values.

  • Choose double quote when fields are enclosed like "North, West".
  • Choose single quote when the source consistently encloses fields like 'North, West'.
  • Choose {none} when the data has no enclosing character and every delimiter should act as a split point.
  • Check whether different rows follow the same convention. A file with inconsistent quoting may need cleanup before Text to Columns can split it reliably.

Use the preview in the wizard as a practical check. If a value that should remain together appears in two columns, verify both the delimiter and the qualifier. If unrelated fields are being combined, check for unclosed quotes, a qualifier mismatch, or a delimiter choice that does not match the source. The preview helps catch these problems before you commit the split.

Text qualifier versus column data format

The text qualifier controls how Excel recognizes field boundaries; it does not tell Excel what kind of value a field is. A quoted number can still be converted to a number, and a quoted date can still be interpreted as a date, depending on the column format selected in the final wizard screen. These are separate decisions.

That distinction matters for values such as postal codes, product IDs, and account numbers. If a code begins with zero, Excel may remove the leading zero when it interprets the field as a number. In the final step of the wizard, select the relevant column in the preview and choose Text under Column data format when you need to preserve its characters exactly. For dates, check the date order and format in the source; an ambiguous value such as 03/04/2025 can be interpreted differently depending on regional settings and the chosen format.

If you want a quote character to remain as literal data rather than act as a qualifier, the result depends on how the source represents that character and how the row is structured. Do not assume that every quotation mark is automatically removed or preserved. Test a few representative rows in a spare area or on a copy of the data before processing a large range.

Common problems and practical fixes

The most frequent mistake is using the right delimiter with the wrong qualifier. For example, choosing {none} for data that uses quotes around comma-containing fields can split one logical field into several columns. Conversely, selecting double quote for data that uses single quotes does not make those single quotes protect embedded delimiters.

  • Extra columns appear: Check whether a delimiter occurs inside a field and whether that field is enclosed by the selected qualifier. Also confirm that the chosen delimiter matches the source.
  • Fields unexpectedly run together: Look for a quote that opens but does not close, or for quote characters in the data that the parser interprets as field boundaries.
  • Quote marks disappear: This is normally expected when they function as enclosing qualifiers. If they are intended as literal characters, inspect the source’s escaping convention and test the import.
  • Leading zeros or date appearance change: Set the affected column to Text, or choose an appropriate date format in the final wizard step. The qualifier setting does not preserve a value’s display format.
  • Existing cells are replaced: Text to Columns writes results into the worksheet, often starting at the source location. Set a different destination in the final wizard screen or copy the source first, and make sure the output area has room.

Text to Columns changes worksheet cells, so it is wise to keep an untouched copy of important source data. The preview cannot reveal every downstream issue, such as formulas or existing values in the destination area. Check the destination before finishing, then inspect the resulting columns and a few sample records.

Text to Columns and other import methods

Text to Columns is convenient when the data is already in a worksheet and you need to split a column once. It is a direct worksheet operation rather than a general-purpose data-cleaning workflow. It is suitable for many small, straightforward jobs, but it may be less convenient when you repeatedly receive files that need the same steps.

For importing a text or CSV file, Excel’s Data > From Text/CSV route provides an import preview and options for interpreting the file before loading it. Power Query can be useful when you need to repeat transformations or refresh data from a source. Its import and parsing controls are not the same wizard as Text to Columns, so check the preview and available quote-character settings in that import experience rather than assuming the worksheet wizard’s selections carry over.

In Microsoft 365, a formula can also split text when the source remains in a cell and you want a formula-driven result. The TEXTSPLIT function is available in Microsoft 365 and Excel 2024, but it is not a replacement for every feature of the import wizard: it requires a delimiter-based pattern and does not automatically provide all the same column-format and import controls. For a one-time split, the wizard may be simpler; for repeatable file cleanup, an import workflow may be more appropriate.

Frequently Asked Questions

What is the default text qualifier in Excel Text to Columns?
The default is a double quotation mark. It tells Excel to treat delimiters inside a double-quoted field as part of that field instead of splitting at them.

Should I choose {none} as the text qualifier?
Choose {none} if the source does not use a character to enclose fields. If fields may contain the selected delimiter and are protected with quotes, choose the matching quote character instead.

Does the text qualifier remove quotation marks?
When quotation marks serve as field enclosures, Excel generally uses them to parse the row and does not include those enclosing marks in the resulting value. Quotation marks intended as literal content depend on the source’s escaping convention and should be checked in a preview.

What is the difference between a delimiter and a text qualifier?
A delimiter marks the boundary between fields, such as a comma or tab. A text qualifier surrounds a field so that a delimiter appearing inside it is treated as text rather than as a boundary.

Why does Excel split a quoted address at its comma?
The selected qualifier may not match the quote character in the source, the field may have unmatched quotes, or the delimiter and qualifier settings may not reflect the file’s structure. Check the wizard preview and inspect a row containing the problem value.

Can I keep leading zeros when using Text to Columns?
Yes. In the final wizard step, select the affected column in the preview and set its column data format to Text before finishing. The text qualifier itself does not preserve leading zeros.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.