How to use Text to Columns in Excel and Google Sheets

On the Data tab, in the Data Tools group. On Windows the keyboard way is Alt, then A, then E, pressed one after another. The Mac ribbon doesn't show group names: the button sits on the Data tab after Advanced, and the menu bar has it under Data > Text to Columns.

If the button is grayed out, the sheet is protected, several sheets are grouped (the title bar says Group), or you are still typing in a cell. Unprotect or ungroup the sheet, or press Enter, and try again. Selecting more than one column doesn't gray it out: Excel opens it, then says it can only convert one column at a time, so select a single column.

The example splits a column of addresses like "Boston, MA, 02134" into city, state and ZIP code.

Select the column and go to Data > Split text to columns. Sheets guesses the separator and splits at once, and a small Separator box appears below the data: change it to Comma, Semicolon, Period, Space or Custom if the guess was wrong. It fills the columns to the right and, unlike Excel, doesn't ask before writing over what is there, so insert empty columns first. If it overwrites something, Edit > Undo puts it back.

Sheets also turns pieces that look like numbers into numbers, so a ZIP code like 04101 comes out as 4101, and there is no step to set a column to text. When a piece has to keep its leading zero, split it in Excel with the column set to Text, or format the result in Sheets with Format > Number > Custom number format and 00000.

To keep the original column, use the SPLIT function instead. =SPLIT(A2, ",") in B2 splits A2 on commas and fills the cells to its right, and updates when A2 changes. If those cells aren't empty, you get a #REF! error until you clear them.

SPLIT treats each character in the delimiter as a separator of its own, so =SPLIT(A2, ", ") splits on commas and on spaces. To split on the comma and space together, add FALSE: =SPLIT(A2, ", ", FALSE). SPLIT turns number-like pieces into numbers too, so a ZIP code loses its leading zero here as well.

A formula leaves the original column as it is and updates when it changes, which suits a sheet that keeps getting new rows.

In Microsoft 365, and Excel 2024 or later, use TEXTSPLIT. =TEXTSPLIT(A2, ", ") splits A2 on a comma followed by a space and spills the pieces into the cells to the right. To split on more than one character, put them in braces: =TEXTSPLIT(A2, {",", ";"}).

In older versions, LEFT, MID and FIND do it one piece at a time. With "Boston, MA" in A2, =LEFT(A2, FIND(",", A2) - 1) returns Boston, everything before the comma. =TRIM(MID(A2, FIND(",", A2) + 1, 100)) returns MA, everything after it, with the space trimmed.

The other way round, joining first and last names or city and state into one cell, takes a formula. There is no Columns to Text button.

The & operator joins cells: =A2 & " " & B2 puts a space between them. =CONCAT(A2, " ", B2) does the same. To join several columns with the same separator, TEXTJOIN is shorter: =TEXTJOIN(", ", TRUE, A2:C2) joins A2 to C2 with a comma and a space, and TRUE skips empty cells so you don't get a double comma. CONCAT and TEXTJOIN are in Excel 2019 and later; older versions have CONCATENATE.

Google Sheets has the & operator and TEXTJOIN too. Its CONCAT takes only two values, so =CONCAT(A2, B2) works but =CONCAT(A2, " ", B2) doesn't; use & or CONCATENATE there.

The result is a formula, so it breaks if you delete the columns it reads from. Copy the results and paste them back as values (Paste Special > Values) before you delete the originals.

For a column of names like "Jane Cole", select it, go to Data > Text to Columns, choose Delimited, check Space and click Finish. The first name lands in one column and the last name in the next.

That works when every name has two parts. "Mary Ann Lee" spreads over three columns, so her last name sits where everyone else's is empty. Flash Fill or a formula handles mixed names better.

Each method treats names that aren't two words differently, so look at the longest names in your list before you pick one. Flash Fill, given Jane for Jane Cole, takes Billy from Billy Ray Duncan and Smith from "Smith, John", so check those rows by hand.

A CRM import wants first and last names in separate fields, or both in one Name field for a CRM like Pipedrive. Operelio's CRM Formatter does the split as part of formatting the file for import: "John Smith" in one column becomes First Name and Last Name, "Smith, John" is handled, and separate columns are joined back into one Name for a CRM that has only one. Names typed in lowercase are capitalized; names already in capitals stay as they are.

Most of these come from Excel guessing what each piece is. Setting the column format in step 3 of the wizard fixes them.

Operelio's Column Manager splits and merges columns in a CSV or Excel file without opening it in a spreadsheet. To split, pick the column and what to split on: comma, space, semicolon, dash, underscore, pipe, slash, tab or a character you type. Auto creates as many columns as the busiest cell needs, or set a number and the last column keeps the rest. Check the box to keep the original column alongside the new ones.

To merge, pick the columns in the order they should join and a separator: space, comma and space, comma, dash, underscore or nothing. Rename, reorder and remove columns in the same job, and preview the result before it runs. Every operation is on every plan, including Free, and your original file is never changed.