Excel Text to Columns: Split Pasted Data Into Fields

To split pasted data in Excel, select the column containing the combined text, then choose Data > Text to Columns and follow the wizard. Choose Delimited for values separated by commas, tabs, spaces, or another character, or choose Fixed width when the fields line up at consistent positions.

What Text to Columns does

Text to Columns takes text in one column and distributes its parts across neighboring columns. For example, a cell containing Jordan Lee,Seattle,WA can become three cells: a name, a city, and a state. The feature is useful when pasted or imported records arrive as one long string instead of separate fields.

It is important to distinguish splitting from pasting. Excel normally recognizes tab characters and line breaks in copied data: tabs separate columns, while line breaks separate rows. If you paste a block of tab-separated data, it may already appear in several columns. Text to Columns is mainly for cases where the pasted fields remain together in a single column, separated by a character or aligned by position.

The command splits values; it does not detect every possible meaning in the text. You choose where the divisions belong and how Excel should interpret the resulting columns. That choice matters for dates, product codes, phone numbers, and other values that may look like numbers but should remain text.

Run the Text to Columns wizard

In Excel for Microsoft 365 on Windows, paste or enter the combined values, select the cells to split, and use Data > Text to Columns. Selecting the full column is convenient when every row has data, but selecting only the used cells can help avoid processing thousands of blank rows. The wizard has three steps:

Choose the split method. Select Delimited when a character marks each boundary. Select Fixed width when boundaries occur at set character positions. The preview shows how Excel plans to divide the values.
Set delimiters or column breaks. For Delimited, check the relevant delimiter boxes or enter a character under Other. For Fixed width, add, move, or remove break lines in the preview.
Set the output format and destination. Choose how each output column should be interpreted, review the preview, and finish the wizard.

Excel writes the results into the selected column and the columns to its right unless you specify another destination in the final step. Before clicking Finish, check that the destination area is empty or that you are comfortable replacing its contents. If you are unsure, copy the source column or duplicate the worksheet first.

On Excel for Mac, the command is also found on the Data tab as Text to Columns, though the ribbon layout and dialog appearance can differ. In Excel for the web, ribbon commands can vary; if Text to Columns is not available in your current view, open the workbook in the desktop app to use the wizard.

Choose the right delimiter

A delimiter is the character that marks the boundary between fields. Common examples include a comma in a comma-separated list, a tab between copied fields, a semicolon in some regional formats, or a space between words. In the Delimited step, select every character that should create a split. Use Other for a delimiter such as a vertical bar (|), slash, or caret.

For example, if a cell contains 1042|Blue mug|18.50, choose Other and enter the vertical bar. The preview should show three fields before you continue. If a value itself contains the delimiter, such as a product description with a comma, check whether the source uses quotation marks around that field. The Text qualifier setting can tell Excel to treat text enclosed by quotation marks as one field rather than splitting at every comma inside it.

Spaces need particular care. Selecting Space splits at every space, so Jordan Lee becomes two fields. That can be appropriate for a simple first-name/last-name list, but it is unreliable for full names with middle names, multiword street names, or inconsistent spacing. If a dependable character such as a tab or comma exists, use that instead. The option to treat consecutive delimiters as one can help with repeated spaces, but it can also erase meaningful empty fields.

Use the preview as a test, not a decoration. Check several rows, especially rows containing missing values, punctuation, or unusually long text. A correct split should produce the same field structure throughout the data.

Use Fixed width for aligned text

Choose Fixed width when fields begin and end at consistent character positions, even though there is no delimiter character between them. A plain-text report might place an account number in characters 1–8, a name in characters 10–29, and a balance after that. In the wizard preview, click to add a break line where a new field should begin. Drag a line to adjust it, or remove a line that divides a value incorrectly.

Fixed width works best with consistently formatted text. It can fail when names or descriptions vary in length and shift the later fields. If one row has a longer name than another, a break based on character position may cut through the name or place part of it in the next column. Review the preview across multiple rows before finishing, and use a true delimiter or a more structured import method when the spacing is not consistent.

Protect IDs, dates, and other values

The final wizard step lets you select a format for each output column. General is convenient for ordinary values, but Excel may interpret number-like text as a number, or recognize a string as a date. That can change the displayed or stored value. For example, an identifier such as 00127 may lose its leading zeros if Excel treats it as a number.

To preserve identifiers, select the relevant column in the preview and choose Text. This is often appropriate for account codes, postal codes, phone numbers, and product IDs. For a date field, use the Date option and select the order that matches the source, such as MDY or DMY. A text value like 03/04/2026 is ambiguous without knowing whether the source means March 4 or April 3; choosing a date order does not determine the source’s intent for you.

The wizard also allows a column to be skipped when you do not need it. In some versions of the dialog, an Advanced option lets you set decimal and thousands separators for numeric data. This can matter when a file uses a comma for decimals and a period for thousands, or vice versa. Verify the displayed result before relying on the converted values.

Set a safe destination before splitting

Text to Columns can fill several columns to the right of the source. If the source is in column A and the split produces four fields, Excel needs room in columns A through D. Existing values in that area may be overwritten. Check the worksheet before finishing, particularly when the pasted text sits beside other data.

If the output should go somewhere else, set the Destination box in the wizard to a blank starting cell. You can also copy the original data to a new worksheet and split the copy. This gives you a way to compare the original text with the results and makes it easier to recover if the delimiter or data format was chosen incorrectly.

For a one-time cleanup, a normal worksheet copy may be enough. For important or repeatedly used data, retain an untouched source sheet and document the delimiter and date assumptions. That small precaution helps explain later why a value was split or converted in a particular way.

Understand what happens when you paste

When a source application copies a rectangular range, Excel commonly uses tabs between columns and line breaks between rows. Pasting that block into a worksheet generally fills a matching grid without any need for Text to Columns. If the entire record lands in one cell per row, the source may have used commas, semicolons, pipes, or another character instead of tabs.

Some copied content includes quotation marks, line breaks inside fields, or formatting from a web page. The clipboard representation can affect how Excel places it, so inspect a few rows immediately after pasting. If the content is in one column, choose the split method based on the actual characters visible in the cell, not on the file’s label or what you expected the source to contain.

For repeated work with a text or CSV file, importing it through Excel’s data tools can offer more control than pasting. On Windows, Data > From Text/CSV opens an import workflow in supported Microsoft 365 versions. There you can preview the source and set options such as delimiter and data type before loading. That is useful when the same file format arrives regularly or when automatic conversion could damage codes and dates.

Fix common Text to Columns problems

Nothing splits: The selected delimiter may not match the character in the text. Check for tabs, semicolons, or a different punctuation mark, and try Other if needed. Confirm that the preview changes when you select a delimiter.
Too many columns appear: A selected character may also occur inside the values. Try a more specific delimiter, or check whether quotation marks are being used as text qualifiers. Avoid choosing Space when fields contain multiword names or addresses.
Empty fields disappear: Consecutive delimiters can represent an intentionally blank value, such as North,,Active. If you tell Excel to treat consecutive delimiters as one, it may combine those boundaries and shift the later fields. Leave that option off when empty fields carry meaning.
Leading zeros vanish or dates change: Excel interpreted a field instead of preserving its text. Re-run the wizard on the original values and set that output column to Text, or choose the correct date order when a real date conversion is wanted. If the original text has already been replaced, restore it from a copy or use Undo immediately if available.
Neighboring data is replaced: The split output needed more columns than expected. Undo the operation if possible, clear or move the conflicting data, or specify a safe destination and run the wizard again.
Rows split inconsistently: The source may not use one consistent delimiter or fixed layout. Inspect exceptional rows and consider importing or cleaning the source data rather than forcing one split rule onto every record.

When to use another method

Text to Columns is a practical choice for a quick, one-time split where the source has a clear delimiter or stable positions. For recurring imports, Data > From Text/CSV can be more repeatable because you can inspect the data before loading it. Power Query is also useful for repeatable cleanup workflows involving multiple transformations, especially when a new source file must be processed again later.

For small, formula-driven tasks, newer Excel functions can extract text around separators without changing the source column. Microsoft 365 and Excel 2024 include functions such as TEXTSPLIT, TEXTBEFORE, and TEXTAFTER. These can be useful when you want a calculated result that updates if the original text changes, whereas Text to Columns writes split values into cells. Function availability depends on the Excel version, and formulas need more care when delimiters are inconsistent or fields contain the delimiter themselves.

Choose based on the job: use Text to Columns for a quick manual split, an import workflow for repeated files, and formulas or Power Query when the result should update or the transformation needs to be repeated. None of these methods can infer an ambiguous source reliably without rules about what each field means.

Frequently Asked Questions

Can I split pasted text into columns in Excel?
Yes. If each row’s fields are in one cell, select that column and choose Data > Text to Columns. Pick Delimited or Fixed width, set the rules, check the preview, and finish.

Why did pasted data already appear in separate columns?
Excel usually recognizes tab characters as column breaks when pasting copied tables or spreadsheet ranges. If the paste already created the desired columns, you do not need to run Text to Columns.

How do I split text at a pipe or semicolon?
Open Text to Columns, choose Delimited, and select Other to enter the pipe character, or check Semicolon if that is the separator. Confirm that the preview shows the intended fields.

How can I keep leading zeros when splitting?
In the wizard’s final step, select the relevant output column in the preview and choose Text before finishing. This prevents Excel from treating identifiers such as 00127 as ordinary numbers.

Can Text to Columns split a full name into first and last names?
It can split at spaces, but that is reliable only when every name follows the same pattern. Middle names, compound surnames, and inconsistent spacing can create extra or misplaced fields, so review the data or use a more suitable cleanup process.

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.