To divide text in columns in Excel, use the Text to Columns wizard (Data > Data Tools > Text to Columns) for a one-time split, or use the TEXTSPLIT function (available in Microsoft 365) for a dynamic, formula-based split. For example, to split “John Smith” into first and last names, select the cell, go to Data > Text to Columns, choose Delimited, select Space, and finish.
What Does “Divide Text in Columns” Mean?
Dividing text in columns means taking a single cell that contains multiple pieces of information (like a full name, an address, or a product code) and splitting it into separate cells across different columns. This is a common data-cleaning task when you import data from other systems, copy text from a PDF, or receive a spreadsheet where someone crammed everything into one column. Excel offers two main approaches: the built-in Text to Columns wizard for a permanent split, and formulas (like TEXTSPLIT, TEXTBEFORE, TEXTAFTER) for a dynamic split that updates if the source data changes.
How to Use Text to Columns (Step-by-Step)
Text to Columns is the classic method. It works in all modern versions of Excel, including Excel 2016, 2019, 2021, and Microsoft 365 on Windows and Mac.
- Select the column (or range) that contains the text you want to split. Make sure there are enough empty columns to the right to hold the results—if not, Excel will overwrite existing data.
- Go to the Data tab and click Text to Columns (in the Data Tools group).
- Choose Delimited if your text is separated by a character like a comma, space, or tab. Choose Fixed width if the text is aligned in columns with consistent spacing (e.g., data from a fixed-width report).
- If you chose Delimited, select the delimiter(s): Tab, Semicolon, Comma, Space, or Other (type a custom character). You can also check “Treat consecutive delimiters as one” to handle multiple spaces or commas.
- Click Next, then choose the data format for each column (General is usually fine) and select the destination cell (by default, it’s the original cell, but you can click the icon to choose a different starting cell).
- Click Finish.
Example: If cell A1 contains “Last, First” and you split by comma, you’ll get “Last” in the first column and ” First” (with a leading space) in the second. To remove the space, use the TRIM function or select the column and use Find & Replace (Ctrl+H) to replace a space with nothing.
Using TEXTSPLIT for a Dynamic Split
If you have Microsoft 365 or Excel 2024, the TEXTSPLIT function splits text into multiple cells using a delimiter, and the result spills automatically. This is ideal when you want the split to update if the original text changes.
Syntax: =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [pad_with])
Example: With “apple,banana,cherry” in cell A1, use =TEXTSPLIT(A1, ",") to return three cells in a row: apple, banana, cherry. If you want them in a column instead, use =TRANSPOSE(TEXTSPLIT(A1, ",")).
For more control, combine with other functions: To split a full name into first and last, use =TEXTBEFORE(A1, " ") for the first name and =TEXTAFTER(A1, " ") for the last name. These functions also require Microsoft 365 or Excel 2024.
Choosing Between Text to Columns and Formulas
Which method you choose depends on your situation:
- Text to Columns is best for a one-time split where you don’t need the results to update. It’s fast and doesn’t require remembering formulas.
- TEXTSPLIT and related functions are better when the source data might change, or when you want to keep the original data intact and have the split results recalculate automatically.
- If you need to split text that has a variable structure (e.g., addresses with different numbers of commas), Text to Columns with delimiters can be tricky; formulas like TEXTSPLIT with
ignore_emptycan handle some variability. - For splitting into multiple columns based on a fixed number of characters (e.g., every 3 characters), use the Fixed width option in Text to Columns, or use MID and COLUMN functions in a formula.
Common Problems and How to Fix Them
Here are typical issues you might encounter:
- Data gets overwritten: Always ensure you have enough empty columns to the right. If not, Excel will overwrite existing cells without warning. Insert blank columns first if needed.
- Leading/trailing spaces: After splitting by a delimiter, you may get extra spaces. Use the TRIM function:
=TRIM(A1)or use Find & Replace (Ctrl+H) to replace ” ” (space) with nothing. - Delimiter not recognized: If your text uses a custom delimiter (like a pipe | or a slash /), choose “Other” in the wizard and type the character. In TEXTSPLIT, just include it in quotes:
=TEXTSPLIT(A1, "/"). - Split doesn’t update: Text to Columns is static—if you change the original data, the split results won’t change. Use formulas if you need dynamic updates.
- Numbers become text: After splitting, cells might be left-aligned and treated as text. Select the column, click the warning icon, and choose “Convert to Number,” or use the VALUE function in a formula.
Alternatives: Flash Fill and Power Query
Two other tools can split text:
- Flash Fill (Ctrl+E) is a smart fill that recognizes patterns. Type the desired result in the first cell (e.g., type “John” from “John Smith” in the next column), then press Ctrl+E to fill the rest. It works well for simple patterns but can be unpredictable with complex data.
- Power Query (Data > Get & Transform Data > From Table/Range) is a more powerful tool for cleaning data. You can split a column by delimiter using the Split Column feature in the Transform tab. This is useful when you need to repeat the same split on a refreshed dataset, as you can save the query and re-run it.
Frequently Asked Questions
Can I split text into columns without using a formula? Yes, use Text to Columns (Data > Text to Columns) or Flash Fill (Ctrl+E). Both are formula-free methods.
How do I split text by line break in a cell? In Text to Columns, choose Delimited and check “Other,” then press Ctrl+J (Windows) or Cmd+J (Mac) to enter a line break as the delimiter. In TEXTSPLIT, use =TEXTSPLIT(A1, CHAR(10)) for a line feed.
Why is my TEXTSPLIT function not working? TEXTSPLIT requires Microsoft 365 or Excel 2024. If you have an older version, you’ll get a #NAME? error. Use Text to Columns instead.
Can I split text into rows instead of columns? Yes. In TEXTSPLIT, use the row_delimiter argument: =TEXTSPLIT(A1, ",", ";") splits by commas into columns and by semicolons into rows. In Text to Columns, you can only split into columns, but you can copy and paste with transpose (Ctrl+C, then right-click > Paste Special > Transpose).
How do I split text after a specific number of characters? Use Text to Columns with Fixed width and set the break lines, or use a formula like =LEFT(A1, 3) and =MID(A1, 4, 3) for fixed-length parts.
Related
- Excel First Letter Uppercase Rest Lowercase?
- Excel Eye Center Provo: Services, Doctors & What to Know
- Excel Eye Center Spanish Fork: Services, Doctors & More
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 Insert a Straight Line in Word: 3 Easy MethodsTo insert a straight line in Word, use the Insert tab’s Shapes tool and hold Shift while you…
- Export Contacts from Outlook Web: A Practical GuideTo export contacts from Outlook on the web, open People, choose Manage contacts > Export contacts, select the…
- Excel Can’t Delete a Named Range? Causes and FixesYes, you can delete a named range in Excel for Microsoft 365 on Windows: choose Formulas > Name…
- How to Insert a New Column in Excel: Steps and TipsTo insert a new column in Excel for Microsoft 365 on Windows, click the letter at the top…
- Google Sheets Expense Tracker: Setup and FormulasCreate an expense tracker in Google Sheets by entering each purchase on its own row, adding consistent columns…
- How to Center Text in Excel Using Keyboard ShortcutsTo center text horizontally in Excel for Microsoft 365 on Windows, select the cell or cells and press…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.