How To Split Text and Data into Columns in Google Sheets (Real-Life Examples!)

An organized spreadsheet looks great, but there’s a lot of effort behind it. For instance, if you want to separate a list of names into columns by first and last name, it would literally take you a lot of time if you follow the general copy/paste approach.

However, Google has made things easier, and you can split text and data into columns just with a few clicks. From addresses to names and even URLs, you can split almost anything to make the data look more organized. 

Here is a detailed article on how to split text and data into columns in Google Sheets. I’ve explained several methods with screenshots to make it easier for you to follow them.

Two Different Ways to Split Text in Google Sheets 

There are two different ways you can use to split text and data into columns. Below I have explained both methods, with one complimentary hack (use of power tools for bigger tasks).

Split Text into Columns Using Options Menu 

Step 1: Select the range of cells you want to split the text for.

Step 2: Now click on Data from the toolbar, and choose Split Text to Columns. 

Step 3: Now, choose the separator between the text. For example, if the text values have space as a separator, you can choose space from the drop-down menu. Google Sheets even lets you choose a custom separator if there is any.

Step 4: Right after you select the separator, the text will split into two columns, as shown in the below image.

Split Text into Columns Using Split Function 

Using the SPLIT function, you can split any text value into columns. Suppose you have a list of names in a single column, and you want to split the names into first and last names.

You can use the SPACE character as the delimiter and the SPLIT function to do this. The formula would be =SPLIT(Cell number, " ").

Here is a demonstration of how this function works:

Step 1: Below is the text value that I want to split into two columns. 

Step 2: Select the cell where you want to split the value, and type the formula: =SPLIT(cell number,” “)

Step 3: Copy this formula for all the text values that you want to split. Make sure to change the cell number as you move down, selecting multiple values.

Step 4: Press Enter and the text value will split into two columns.

Additional Method: Split Text Using Power Tools 

If you want to use the split function for a bulk task, you can use power tools. Below I have explained how to add an extension to your Google Sheets and use it to split text values.

Step 1: Open the spreadsheet, and click on Extensions from the toolbar.

Step 2: Click on Add-ons, and Get Add-ons. A pop-up box will open, type “Split” into the search bar. Finally, click on Split Names.

Step 3: Click on Install to add the extension to your account. It’ll ask for permissions, so make sure to read all details before you allow the extension to access your data.

Step 4: Now select the range of cells you want to split the text values for.

Step 5: After installing the add-on, click on Extensions from the toolbar, and choose Split Names under click on “Start” to activate it.

Step 6: Choose how you want to split the data, such as first name, middle name, and last name. Click on Split. 

Step 6: It’ll show you a confirmation that you have selected “n” names or values.

Step 7: The final result will look something like this.

4 Real-Life Examples of Splitting Text into Columns

Here are four real-life examples of how you can split the text into columns. I’ve demonstrated these examples in detail to help you understand the concept.

Example1: Split Address (by comma) 

Step 1: Select the cell where you want the values to split, and enter =SPLIT(cell number,”,”) The comma is the separator here.

Step 2: Copy/paste the function in all cells and make sure to change the cell numbers accordingly.

Step 3: Press Enter and all the values will split as you want.

Example 2: Split URL to Get Domain Name 

To split the URL, you can use the REGEXREPLACE function. The function helps you eliminate the extra values and split the URLs into the column you want. 

You can use the basic SPLIT function (=SPLIT(cell number, “delimiters”) to split URLs to get domains. However, that is a bit hectic procedure, especially if you just want to extract the domains and don’t need the extensions such as HTTPS//. Below I have explained the REGEXREPLACE method to split the domain and eliminate the delimiters. 

The primary syntax of REGEXREPLACE is REGEXREPLACE(text, regular_expression, replacement)

• Text: Required. The text string or cell reference containing the text is to be searched and replaced.
• Regular_expression: Required. The regular expression pattern to search for.
• Replacement: Required. The text string to replace the search string.

Here’s how the function works.

Step 1: Below you can find a few URLs.

Step 2: Choose the cell where you want to split the value, and enter: =REGEXREPLACE(Cell number,"http\:\/\/|https\:\/\/|\/.*|\?.*|\#.*","") 

Don’t want to go too deep into this regular expression but if you are curious it is looking for the following strings:

• http://
• https://
• and whatever comes after either ‘?’, ‘#’ or ‘/’

Press Enter and you’ll get the split domain name into the desired cell. 

Step 3: Paste the formula in all cells and replace the cell numbers. Now tap on Enter again to get the domain names split from other values.

Example 3: Split Emails in Google Sheets 

To split emails in Google Sheets, you can use the SPLIT function and the “@” character as the delimiter.

For example, suppose you have a list of email addresses in a single column, and you want to split the email addresses into the username and domain parts.

The formula would be: =SPLIT(A2, "@")

This formula would split the email address in cell A2 at the “@” character and return the split parts in a row. If the email address in cell A2 is “[email protected]”, the formula would return “john.smith” and “gmail.com” in two separate cells.

Here’s how it works:

Step 1: Select the cell where you want to split the email, and enter =SPLIT(cell number, ”@”)

Step 2: Press Enter and the values will split into two columns.

Example 4: Split Text by Multiple Delimiters 

To use this feature, you need to install the Power Tools extension. Click on Extensions, select Add-ons, and click on Get Add-ons. Now search for Power Tools and add the extension to your Google account.

Step 1: Select the values that you want to split, and click on Extensions, choose Power Tools. 

Step 2: Select Split from the list of options available, and click on Split Text. 

Step 3: Choose Custom, and enter the delimiters you want to separate the text value with. Here I have used “space, and comma), as separators or delimiters. Click on Split, and you’ll get the output. 

How to Split Text to Columns In Google Sheets Mobile App (iPhone, iPad, Android)

You can use the =SPLIT(cell number, “delimiter”) to split the text into columns using the mobile Google Sheets app. Enter the formula, and you’ll get the desired results. It works just like you use it on a PC.

Some Questions You May Still Have 

Conclusion 

That’s how you can split values in Google Sheets using the SPLIT function. For more complex tasks you can use add-ons like Power Tools as they make the job really easy, and you can quickly split as many text values as you want.