How to Split Text into Columns in Google Sheets

Column A holds values like "Toronto, Ontario" or "Maria Lopez" in a single cell, and you need each part in its own column. Google Sheets can split the whole column at once on a comma, a space, or any character you choose.

Insert blank columns to the right of the data

Splitting writes the pieces into the columns immediately to the right and overwrites whatever is there. If column B already holds data, right-click the column B header and choose Insert 1 column left. Repeat once for each extra piece the text will split into.

Select the column to split

Click the column letter to select the whole column, or select only the cells with data, such as A2:A50, to leave the header out.

Open Data, Split text to columns

Go to Data, Split text to columns. A small Separator box appears at the bottom of the selection with Detect automatically chosen. Sheets looks at the text and, if it finds an obvious separator such as commas, splits on it right away.

Pick the separator

Click the Separator box and choose one of Detect automatically, Comma, Semicolon, Period, Space, or Custom. With Custom, type the character or characters to split on, for example a vertical bar.

With "Toronto, Ontario" in A2 and Comma selected, A2 becomes Toronto and B2 becomes Ontario. Sheets drops the space that followed the comma. With "Maria Lopez" and Space, A2 becomes Maria and B2 becomes Lopez.

The split is permanent once you click elsewhere. Press Ctrl+Z (Cmd+Z on a Mac) straight away if the result is wrong.

For a version that updates when the source changes. Enter the SPLIT function in the first empty column instead of using the menu:

=SPLIT(A2, ",")

The first argument is the cell to split and the second is the separator. The result fills B2, C2, and so on, one piece per column, and refreshes whenever A2 is edited. Copy the formula down for the rest of the rows. The original text in column A stays as it was.