How to Split Text into Columns in Google Sheets

Google Sheets is a powerful and versatile spreadsheet application that allows you to manipulate and analyze data in various ways. One of the features that Google Sheets offers is the ability to split text into columns, which can be useful when you have data that is separated by a common delimiter, such as a comma, a space, a period, or a custom character. In this article, we will show you how to use this feature to split text into columns in Google Sheets, and also how to use a built-in function called SPLIT to achieve the same result with more flexibility.

Using the Split Text to Columns Feature

The Split Text to Columns feature is a simple and straightforward way to split text into columns in Google Sheets. You can use this feature when you have data that is clearly defined by a single delimiter, such as the first and last names of a contact, or the username and domain of an email address. Here are the steps to use this feature:

On your computer, open a spreadsheet in Google Sheets. If the data is already in the sheet, select the cells that you want to split. If the data is not in the sheet yet, paste it into the cells.
At the top, click Data > Split text to columns.
A small menu will appear beside your text, where you can select the delimiter that your data uses. You can choose from comma, space, semicolon, period, or custom character. For example, if your data is separated by spaces, choose Space from the menu.
The data will be immediately split into columns based on the delimiter that you selected. You can adjust the width of the columns by dragging the column borders.

Note: Make sure that the cells to the right of your data do not have any existing data, as this feature will overwrite those cells without any warning. If you want to undo the splitting, press Ctrl+Z (Windows) or Command+Z (Mac).

Using the SPLIT Function

The SPLIT function is another way to split text into columns in Google Sheets. You can use this function when you have data that is separated by multiple delimiters, or when you want more control over how the text is split. The SPLIT function takes two arguments: the text that you want to split, and the delimiter (or delimiters) that you want to use. The syntax of the SPLIT function is:

=SPLIT(text, delimiter)

For example, if you have an email address in cell A1, and you want to split it into three columns: username, subdomain, and top-level domain, you can use this formula:

=SPLIT(A1,"@.")

The formula will return three values in three adjacent cells: the username before the @ sign, the subdomain after the @ sign and before the period, and the top-level domain after the period.

Note: The SPLIT function treats each character in the delimiter argument as an individual delimiter, unlike the Split Text to Columns feature, which looks for the exact delimiter in the text. For example, if you use =SPLIT(A1,"@."), it will split the text by both @ and . characters. If you use =SPLIT(A1,"@."), it will look for “@.” as a single delimiter and return an unchanged text if it does not find it.

Here are some tips and tricks for using the SPLIT function:

You can use more than one character as a delimiter by putting them inside quotation marks. For example, if you want to split a text by comma and space, you can use =SPLIT(A1,", ").
You can use regular expressions as delimiters by using a backslash () before them. For example, if you want to split a text by any number of spaces, you can use =SPLIT(A1,"\s+").
You can use TRUE or FALSE as a third argument to indicate whether your text has quotation marks around it. For example, if your text is "John Smith", and you want to split it by space without including the quotation marks, you can use =SPLIT(A1," ",TRUE).
You can drag the formula down or across to apply it to other cells with similar data.

Conclusion

In this article, we have shown you how to split text into columns in Google Sheets using two methods: the Split Text to Columns feature and the SPLIT function. Both methods have their advantages and disadvantages depending on your data and needs. You can choose whichever method suits your situation best and enjoy working with your data in Google Sheets.

For more information and examples on how to split text in Google Sheets, you can check out these resources:

How to Split Text into Columns in Google Sheets