How to Split Text into Columns in Excel

Column A holds values like "Ruiz,Dana,Toronto" and you need each part in its own column. Text to Columns splits a column at a character you choose.

Select the column to split

Click the column letter, or select just the cells, for example A2:A50. Do not include cells that should stay whole.

Make sure the columns to the right are empty, or set a different destination in the last step, because the split parts overwrite whatever is there.

Open Text to Columns

Click the Data tab, then Text to Columns in the Data Tools group. The Convert Text to Columns Wizard opens. On Mac, the wizard is the same and opens from the same button on the Data tab.

Choose Delimited

Select Delimited and click Next. Delimited means the parts are separated by a character such as a comma, a space, or a tab.

Choose Fixed width instead when every row lines up at the same character positions, such as a report exported from an older system. You then click in the preview to place the break lines, and drag them to move or double-click to remove them.

Pick the delimiter

Check the character that separates the parts: Tab, Semicolon, Comma, Space, or Other with a character typed in the box. For "Ruiz,Dana,Toronto" check Comma. For a full name like "Dana Ruiz" check Space.

The preview at the bottom shows how the data will split, with a vertical line between each part. Click Next.

Set the destination and finish

In the Destination box, enter the top-left cell where the results should go, such as $B$2, so the original column is left intact. Leave it unchanged to overwrite the source column. To keep leading zeros in postal codes or phone numbers, click that column in the preview and set its format to Text. Click Finish.

With these values in A2:A4 and Comma selected, the three parts land in B, C, and D:

Ruiz,Dana,Toronto
Okafor,Sam,Calgary
Lee,Min,Vancouver

Row 2 becomes Ruiz in B2, Dana in C2, and Toronto in D2.

To split with a formula instead. In Microsoft 365, =TEXTSPLIT(A2, ",") puts each part in its own column to the right, and the result updates when A2 changes. Use " " as the delimiter for names separated by a space.

To split by example. Type the first result by hand in B2, for example Ruiz, then select B3 and press Ctrl + E for Flash Fill. Excel fills the column by matching the pattern. The same command is on the Data tab as Flash Fill, which is the reliable route on Mac.