Written by Jane Hames
This article is aimed at people who use Google Sheets instead of Microsoft Excel as their main spreadsheet application. If you’d like to know more about Glide Training’s on-site training for users of Google Sheets and other Google G Suite products please click here.
Sometimes you might find that the content of text cells in a spreadsheet isn’t laid out the way you’d like it; for example a column may contain whole names where you’d prefer forename and surname to be in separate columns, or it contains full addresses where you’d want the first line, city, county etc to each be in its own column.
The “Split text into columns” feature in Google Sheets, makes it quick and easy to split the text across multiple columns. All you need is a marker to identify where the data should be split. This could be a comma, a space or any other character.
For example I have this data, where the town and county are combined in one location cell.
I will want to analyse my customers by county, so in preparation for my analysis I am going to split the town and county in to separate columns.
Step 1:
Check that you have enough blank columns next to the data you want to split. If you already have data in the adjacent columns, that will be replaced by the split data. In my example I need a blank column for the county to go into, as shown in green here:
Step 2:
Select the data that needs to be split. As you can see, I have selected the data in column B here:
Step 3:
From the Data menu, select Split text into columns.
Step 4:
Sheets will try to detect where you want the split to occur. In my example, I have a comma between each town and county, so this is where it splits.
Step 5:
If you want the split to occur in a different position or if Sheets cannot detect automatically, click on “detect automatically”. Select the separator that suits your data or click on Custom and type the character you require as your separator.
And that’s it! In just a few seconds you have your data organised how you want it.
If you’d like to know more about Glide Training’s on-site training for users of Google Sheets and other Google G Suite products please click here.
