Google Sheets: Analysing Information – Formulas to work with text

Hi [accessally_user_firstname],

A common task in the office is tidying up a spreadsheet in order to clean up names and contact details. There are a range of different functions that can really make life easier when creating and working with text.

This includes splitting pieces of text up inside a cell (e.g. first and last name), changing the case of text, taking out unnecessary spaces and identifying only the unique values in a list, to name a few of the more commonly used functions.

This series of tutorials will show you some of the common functions for speeding these processes up.

You will learn:

How to use SPLIT to separate two pieces of text contained in the same cell.
How to use TRIM to get rid of unwanted spaces and other characters.
How to alter the case of text in your spreadsheet using the PROPER function.
How to show a range of UNIQUE values.
Successfully complete the quiz

Using the SPLIT function to separate values inside a cell…

[accessally_user_firstname], Learn how to separate the values in a cell based on the delimiters you choose. This video is 3 minutes 48 seconds long:

Examples where the SPLIT function will be useful include:

  • Separating a list of student names into two columns (so you can sort by either first or last name).
  • Separating the different parts of an address into separate columns.

Using the TRIM function to get rid of unwanted spaces…

[accessally_user_firstname], Learn how to get rid of unnecessary spaces at the start or end of the data in your cell. This video is 2 minutes 14 seconds long:

Examples where the TRIM function might be useful include:

  • Cleaning up a list of names which have been exported from your database and then re-importing them to clean it up.


Using the PROPER function to capitalise the first letter of each piece of data…

[accessally_user_firstname], Learn to capitalise the first letter of pieces of data with this 1 minute 49 second video:

Examples where the PROPER function might be useful include:

  • Preparing information in cells to be a title.
  • Fixing names of people in your sites (eg. cleaning up data from Google Form submits).


Using the UNIQUE function to identify only the unique values in a list…

[accessally_user_firstname], The UNIQUE function is really useful when you know you’ve got repeated data in subsequent cells in your Sheet.This video is 2 minutes 14 seconds long:

Examples where the UNIQUE function will be useful include:

  • Preparing a list of parent emails for your newsletter email list.
  • Checking the names/emails of respondents from a survey.

My private notes...
Click here to enter your note (this is private to you)...

What does the TRIM function do inside a formula?
Next >

Social Sharing Course

[accessally_user_firstname], If you’re enjoying this course, please share it with your friends!

Skip to toolbar