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:

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:

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