How To Use Google Sheets’ Split Text to Columns Tool (with Examples)

By

Watch Video – How to use Google Sheets’ Split Text to Columns tool

Are you wondering how to use Google Sheets’ split text to columns tool? The answer is simple, you can quickly split the contents of a cell (or a range of cells) into columns, using the Split Text to Columns feature. This is handy when you want to quickly split the first name and the last name, the username and domain name from email ID, or the domain name from URLs.

In this tutorial, I will show you multiple examples of using Google Sheets’ split text to columns tool. Soon you will know exactly how to split cells in Google Sheets like a pro.

How To Use Google Sheets’ Split Text to Columns Tool

Example 1 – Split the Full Name into First Name and Last Name

Below is a dataset with the names of some of my favorite superheroes:

Google Sheets split text to columns - Full names

Here are steps to split the full name into the first and last names:

  • Select the data that you want to split.
  • Go to the “Data” tab.Split Text to Columns in Google Sheets - data tab
  • Click on the “Split text to columns” option from the drop-down.Split Text to Columns in Google Sheets - split text to columns
  • In the “Separator” dialog box that appears at the bottom right of the data, select “Space” as the delimiter.Split Text to Columns in Google Sheets - space

 

That’s it! It will instantly split the names into the first name and the last name.

Split Text to Columns in Google Sheets - result

Note:

  • When you use the “Split text to column” option, it overwrites the original data set. If you want to keep the original dataset intact, copy the data set and use the “Split text to column” option on that data set.
  • This will give you a static result, meaning your data will not update unless you update the original dataset. If you want this to be dynamic, use the split function.
  • Every space character is considered a different separator. In case you have a double space between names and you use the space character as the delimiter, it will split the name into three columns. In such cases, remove the double space by using the TRIM function [there is a text-to-column functionality in Excel to consider consecutive delimiters as one, which Google Sheets has also adopted].

Example 2 – Split the Email ID into Username and Domain Name

Suppose you have a dataset with emails as shown below:

Split Text to Columns in Google Sheets - emails

 

Here are the steps to use the “Split text to columns” option to separate the username and domain name:

  • Select the data that you want to split.
  • Go to the “Data” tab.Data option in the ribbon
  • Click on the “Split text to columns” option from the drop-down.Split text to columns menu
  • In the “Separator” dialog box that appears at the bottom right of the data, select “Custom.”Split Text to Columns in Google Sheets - custom
  • In the “Custom” field, enter the At (@) symbol.Split Text to Columns in Google Sheets - at the rate

 

When you enter the At (@) symbol, Google Sheets will instantly split the text into the username and domain name.

Again, remember that this will overwrite the original dataset. If you want to keep the original data set intact, create a copy and then use the “Split text to columns” feature.

Example 3 – Get the Domain Name from the URL

Suppose you have a dataset as shown below:

Split Text to Columns in Google Sheets - urls

 

Note: There is a mix of URLs where only some have the root domain, and some have links to a specific page/post.

Here are the steps to get the domain name from URLs using the “Split text to columns” feature:

  • Select the data that you want to split.
  • Go to the “Data” tab.Data Tab
  • Click on the “Split text to columns” option from the drop-down.
  • In the “Separator” dialog box that appears at the bottom right of the data, select “Custom.”Split Text to Columns in Google Sheets - url custom
  • In the “Custom” field, enter the forward slash (/) symbol.Split Text to Columns in Google Sheets - forward slash

 

Note: As soon as you enter forward slash (/), the URLs will spit, and the domain name will be in column C.

Now if you’re wondering why column B is empty, it’s because there are two forward slashes after HTTP in the URLs. Each forward slash is treated as an individual separator.

Also, note that this technique works when you have the domain names in the same format. For example, if you have one with HTTP and one without it, then it may give you the domain names in different columns.

Pro Tip: If you want to split the text into rows (and not columns), an easy way is to first get the result using text to columns and then transpose the data.

Conclusion

So, this is how you can use the Google Sheets’ split text into columns tool. You can also use a similar formula to move text to a column (such as RIGHT, LEFT, MID, etc.). but in most cases, I find using this a lot easier.

I hope you found this tutorial useful!

You May Also Like the Following Google Sheets Tutorials:

Popular Posts

1 thought on “How To Use Google Sheets’ Split Text to Columns Tool (with Examples)”

  1. I need to split a two digit code that is coming over as a string of numbers in one cell with no delimiter.

    So A1 has 022325, I need to delimit the field as 02,23,25.

Comments are closed.

Access All

Free Templates

Enjoy our custom-made Google Sheets templates for free via email access