Split Text to Columns: When working with a huge dataset in Google Sheets, it’s common to squish the data. Split text to columns is one of the most useful features which was recently added in Google Sheets, that helps to split the data into columns. You can split data in the Google Sheets by any of the two methods via Formula or through an in-built function. On this page, let’s explore how to split the data from one column to multiple columns with important Google Sheet tips. Read on to find out more.
Table of Contents
Split Cells in Google Sheets
When you need to quickly split the first and last name, or the username and domain name from an email id or the domain name from URLs, the Split Text to Columns tool come in handy. Let’s understand how this can be done with the help of examples.
Split Cells in Google Sheets to Multiple Columns
Now, let’s split the first name and last name from one column to multiple columns with an example.
- Step 1: Select the cells where you would like to split the text into columns.
- Step 2: Click on “Data” from the menubar and select “Split text to columns” from the drop-down menu.
- Step 3: A separator dialog box will open the screen. Now click on the separator dialog box.
- Step 4: Select “space” from the dropdown menu as the delimiter.
Now you will see the cells results as shown in the image below.
- When you use the ‘Split Text to Column’ command, the original data set is overwritten. Create a copy of the data set and use Split Text to Column on that data set if you wish to preserve the original dataset intact.
- The above method gives static results. This means that if you update the source dataset, your data will not be updated. Use the split function if you want this to be dynamic
- Each space character is viewed as a distinct separator. If there is a double space between names and the space character has been used as a delimiter, the name will be split into three columns. In such circumstances, use the TRIM function to remove the extra space.
Split Email ID into User Name & Domain Name in Google Sheets
With the help of the Split text to columns feature in Google Sheets, we can easily split the user name and domain name from the email id. The steps to split the username and domain name from the email are given below:
- Step 1: Select the email ids where you would like to split the usernames and domain names.
- Step 2: Now click on “Data” from the menubar and select “Split text to columns” from the drop-down menu.
- Step 3: A Separator dialog box will open on the screen. Click on the Separator dialog box and select “Custom” from the drop-down menu.
- Step 4: Now in the custom field, enter the “@” symbol. You will instantly see the domain name and username separated as shown below.
- How to Quickly Merge Cells in Google Sheets: Merge & Unmerge Without Losing Data
- How to Quickly Transpose Data in Google Sheets: TRANSPOSE, Paste Special Method
- How to Create a Dependent Drop Down List in Google Sheets: Multiple Drop Down List
Split Domain Name from URL to Cells in Google Sheets
This inbuilt split feature also helps us to split the domain name from the URL in Google Sheet. Let’s consider the following dataset to split the domain name from URL.
The steps to split or divide the domain name from the URL are given below:
- Step 1: Select the cell range where you would like to split the domain name from the URL.
- Step 2: Now click on the “Data” from the toolbar and select “Split text into Columns“.
- Step 3: In the Separator Dialog box, select “Custom” from the drop-down menu.
- Step 4: Now in the “Custom field” enter “/“. The results will be displayed on the screen as shown below.
If you’re wondering why column B is blank, it’s because, in the URLs, there are two forward slashes following HTTP. Each forward slash is considered a separate separator.
Also, keep in mind that this strategy only works if the domain names are in the same format. If you have one with HTTP and one without, for example, the domain names may appear in different columns.
If you wish to split the text into rows rather than columns, one simple method is to use text to columns first, then transpose the data in Google Sheets.
Google Sheets Split Text to Columns Formula
We can also use the Split formula to split or divide the text into columns in Google Sheets. The steps to split the text into columns using the Split formula are given below:
- Step 1: Select the cells where you want to split the data.
- Step 2: Now enter the formula as =SPLIT(A2,” “).
- Step 3: Press the “Enter” button. You will see the results.
In order to split the data into all the cells, just drag the cell where you have applied the formula. The formula will be applied to all the cells and data will be split into all the cells.