How to Split Text to Columns in Google Sheets (with Examples)

Watch Video – How to Split Text to Columns in Google Sheets

You can use the Split Text to Columns in Google Sheets to quickly split the contents of a cell (or a range of cells).

Split Text to Columns in Google Sheets

Split Text to Columns feature comes in handy when you want quickly split the first name and the last name, or the user name and domain name from an email id, or the domain name from URLs.

In this tutorial, I will show you multiple examples on how to split text to columns in Google Sheets.

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:

Split Text to Columns in Google Sheets - Fulll names

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

  • Select the data that you want to split.
  • Go to the data tab.Split Text to Columns in Google Sheets - data tab
  • Click on Split Text to Columns 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, it overwrites the original data set. If you want to keep the original dataset intact, create a copy of the data set and use Split Text to Column on that data set.
  • It would give you static result. This means, that your data would not update in case you update the original dataset. If you want this to be dynamic, use the split function.
  • Every space character is considered as 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 3 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. I hope that is also adopted by Google Sheets].

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 Split Text to Columns to separate username and domain name:

  • Select the data that you want to split.
  • Go to the data tab.
  • Click on Split Text to Columns 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 - custom
  • In the Custom field, enter @. Split Text to Columns in Google Sheets - at the rate

As soon as you enter @, Google Sheets will instantly split the text into username and domain name.

Again, remember that this will overwrite on the original dataset. If you want to keep the original data set intact, create a copy and then use 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 that there is a mix of URLs where some only 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 Split Text to Columns:

  • Select the data that you want to split.
  • Go to the data tab.
  • Click on Split Text to Columns 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 /Split Text to Columns in Google Sheets - forward slash

Note that as soon as you enter /, the URLs will spit and the domain name would 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.

You May Also Like the following Google Sheets Tutorials:

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X