Glide Training Logo

Google Sheets – how to split text into separate columns

  

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.


previous post: Raise your spreadsheet skills with our new online Excel training course

next post: Work from home with Glide Training during the Coronavirus crisis

Why choose Glide?

Our Unique Approach

Our Unique Approach

We work on a bespoke, requirements-led basis rather than offer a set range of courses. We will work with you to identify your specific requirements and objectives before any training is delivered. That way you can be sure that training will always deliver exactly what you're looking to achieve.

View details »

Our Low Prices

Our Low Prices

We deliver all our on-site training at our customers' premises. Not only does that avoid any travel inconvenience for your team, but also it means we don't have the overheads required to maintain a dedicated training centre - and in turn we are able to pass these savings on to you.

View details »

Our Expertise

Our Expertise

Our trainers have designed and delivered IT training for a wide range of organisations and people including accountants, lawyers, data analysts, sales consultants and buyers. Clients benefit from our our ability to find creative solutions to their problems based on many years of experience.

View details »

Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo
Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo