No: Excel’s Text to Columns wizard splits at every occurrence of the delimiter you select; it has no setting to split only at the first one. To keep the rest of the text together, use a first-delimiter formula such as TEXTBEFORE and TEXTAFTER, or use Power Query’s Left-most delimiter option.
What Text to Columns does
Text to Columns is a one-time data-splitting tool, not a rule that says how many delimiters to process. For example, if a cell contains North, East, West and you choose the comma delimiter, Excel separates the text at both commas. The result occupies three columns: North, East, and West. There is no “first delimiter only” choice in the wizard.
That behavior is useful when each delimiter marks a genuine boundary, as with a list of names or values. It is less suitable when the first delimiter separates a category from a description and later delimiters belong to the description. In that case, you need to specify the first occurrence in a formula or choose a tool that offers a left-most split.
Run the Text to Columns wizard
In Excel for Microsoft 365 on Windows, select the source column, then choose Data > Text to Columns. Select Delimited if a character such as a comma or tab separates the fields, and choose Next. Select the delimiter, check the preview, and continue to the final screen. You can choose a destination cell there; choosing a separate destination helps preserve the original data.
The wizard can split on common characters, including tabs, commas, semicolons, and spaces, or on a character entered under Other. It also has options such as treating consecutive delimiters as one and choosing a text qualifier. These settings affect how the text is parsed, but they do not limit the split to the first matching character.
Before you choose Finish, check how many output columns the preview shows and whether they will overwrite anything. If the output is going beside the original column, make sure those cells are empty. If the data contains product codes, account numbers, or dates that must retain their original appearance, set the relevant output column’s data format appropriately; automatic conversion can remove leading zeros or interpret dates differently from what you intended.
Use TEXTBEFORE and TEXTAFTER for the first match
For a repeatable split that leaves everything after the first delimiter in one result, use the modern text functions. With the original text in A2 as North, East, West, this formula returns North:
With the source text in A2, the first formula returns the part before the first comma: =TEXTBEFORE(A2,",")
With the same value in A2, this formula returns East, West, retaining the later comma and removing the leading space after the first one: =TRIM(TEXTAFTER(A2,","))
Enter the formulas in two empty columns on the same row as the source, then fill them down. TEXTBEFORE returns the text before the first match by default, and TEXTAFTER returns the text after it. TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to single spaces. If preserving every space matters, use TEXTAFTER without TRIM.
TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024. If a row might not contain the delimiter, decide what the result should be before filling the formulas down: by default, a missing delimiter produces an error. For example, with source text in A2, this formula returns the original value unchanged when no comma is found: =IFERROR(TEXTBEFORE(A2,","),A2). Choose a different fallback if an unchanged value is not appropriate.
Use older formulas if needed
In Excel versions without TEXTBEFORE and TEXTAFTER, LEFT, FIND, MID, and LEN can do the same job. They look for the first comma, take the characters before it, and then take the remaining characters after it. With North, East, West in A2, the first formula returns North:
With the source value in A2, this formula returns the text before the first comma: =LEFT(A2,FIND(",",A2)-1)
With the same value in A2, this formula returns the text after the first comma, including its initial space: =MID(A2,FIND(",",A2)+1,LEN(A2))
These formulas also return an error if the delimiter is missing, because FIND cannot locate it. You can wrap a formula in IFERROR if you want a fallback, but be clear about whether the fallback should be the original text, a blank, or a message. As with the newer functions, copy the formulas down to process additional rows.
Choose Power Query for a repeatable import
Power Query provides a direct first-delimiter option and is often a better fit than formulas when you regularly import or clean a larger dataset. In Excel for Microsoft 365 on Windows, select the data and choose Data > From Table/Range. If Excel asks whether to create a table, confirm that the range and header setting are correct.
In the Power Query Editor, select the column to split and choose Split Column > By Delimiter. Select or enter the delimiter, then set the split option to Left-most delimiter in the dialog’s advanced options. Confirm the split and check the preview: the first output column should contain the text before the first match, and the second should contain everything after it. Choose Home > Close & Load to return the results to Excel.
Unlike Text to Columns, a Power Query transformation can be refreshed when the source data changes. It is not an automatic live link to each edited cell: refresh the query to apply its steps to updated source data. That makes it useful for recurring files, while a formula is usually quicker for a small list that changes frequently in the worksheet.
Which method should you choose?
- Use Text to Columns when every delimiter marks a split and you want a quick, one-time operation. It splits at all selected delimiters.
- Use TEXTBEFORE and TEXTAFTER when you need a formula-based first split that updates with the source cell. They require Microsoft 365 or Excel 2024.
- Use LEFT, FIND, and MID when you need a formula approach compatible with older Excel versions.
- Use Power Query when the same import or cleanup needs to be repeated, especially across many rows, and you want to choose Left-most delimiter.
Also consider what should happen to the original data. Text to Columns changes cells in the worksheet, so use a copy or set a separate destination if you may need the unsplit text. Formulas leave the source values alone as long as you place the results elsewhere. Power Query creates a transformed output that can be refreshed, but its results are managed separately from the source range.
Examples and common problems
Suppose a column contains labels such as Region: North: Coastal. If the first colon separates a field name from its value, splitting at every colon would create three pieces and break up North: Coastal. A first-match formula or Power Query’s left-most option produces Region and North: Coastal, which is usually the intended result.
If a delimiter is not visible, inspect the data carefully. A space may be a regular space, a tab, or a nonbreaking space copied from a web page. Those characters are different, so choosing a regular space will not necessarily split at a tab or nonbreaking space. Likewise, if some records use commas and others use semicolons, one delimiter setting will not consistently handle both without extra cleanup.
Spacing is another frequent source of surprise. A split after a comma often leaves a space at the start of the second value. TRIM is helpful for ordinary spaces, but it does not remove every unusual whitespace character. If the source includes imported or copied text, inspect the results before replacing or deleting the original column.
Finally, remember that a comma in a text file does not always mean “end this field.” Some files use quotation marks to keep a comma inside a value, such as a quoted address. The Text to Columns wizard includes a text qualifier setting for handling quoted text; check its preview rather than assuming every comma is a true boundary. For complex or recurring imports, Power Query gives you a more manageable place to review and adjust parsing steps.
Frequently Asked Questions
Can Text to Columns split only at the first comma?
No. The wizard splits at each occurrence of a selected delimiter. Use TEXTBEFORE and TEXTAFTER, older text formulas, or Power Query’s Left-most delimiter setting to keep later commas in the second part.
How do I keep the rest of a sentence after the first space?
Use the same first-match approach with a space as the delimiter. For example, TEXTBEFORE can return the first word and TEXTAFTER can return the remainder. Check the source for repeated spaces, since they can affect what counts as the first space.
Why does Text to Columns create too many columns?
It is splitting at every occurrence of the delimiter you selected. If a field itself contains that character, the wizard treats it as another boundary unless the data format and text qualifier settings indicate otherwise. Use a first-only method when the later delimiter belongs in the remaining text.
Will the formulas update when the original text changes?
Yes. A formula referring to A2 recalculates when A2 changes, provided the formula remains in place. Text to Columns, by contrast, performs a one-time split and does not keep the output linked to the original cell.
Can I use the first-delimiter formulas in Excel for the web?
TEXTBEFORE and TEXTAFTER are available in Microsoft 365, including supported Excel for the web accounts. The ribbon and Power Query options can differ by platform and account, so if a command is not shown, use the formulas or open the workbook in Excel for Microsoft 365 on Windows.
Related
- Excel Table Data Filtering: How to Narrow Rows
- Excel Table Data Sorting: Steps, Options, and Fixes
- Excel Spreadsheet Not Responding? Causes and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Add Sequential Page Numbers in WordTo add sequential page numbers in Microsoft Word, choose Insert > Page Number, select where the numbers should…
- Conditional Formatting in Microsoft Forms: What Works?Microsoft Forms does not have a built-in conditional-formatting feature for changing question or answer colors based on a…
- Excel Flash Fill for Number Sequences: How It WorksExcel Flash Fill can sometimes extend a number pattern when you show it examples, but it is designed…
- Google Sheets Custom Conditional Formatting: A GuideYes. In Google Sheets, custom conditional formatting lets you apply a style when a formula evaluates to TRUE:…
- Amortization Schedule Excel: Build One Step by StepTo build an amortization schedule in Excel, enter the loan amount, annual interest rate, term, and payments per…
- Apps Script Google Sheets Bad Request? Causes & FixesYes—“Bad Request” in a Google Sheets Apps Script usually means a request was rejected because its URL, range,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.