ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week #436 - Trimming excess spaces redux

Author: David Lyford-Tilley

Published: 08 Mar 2022

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.
Hello all and welcome back to the Excel Tip of the Week! This week we are taking a deep dive into a very annoyingly necessary data cleaning task – removing excess spaces.

Why are excess spaces an issue?

Unnecessary spaces are often added by human users when typing – either by accident or by habit of typing a space after a full stop or other character. However, these characters can mess with all kinds of things. For example they will cause Excel to categorise two apparently identical cells as being different – a real pain for future analysis. See here where we’ve tried to create a pivot summary of invoices by customer:

Excel screenshot
The invisible additional spaces cause trouble. A similar issue can also mess up sorting of data:
Excel community

So removing these spaces is a common step in data cleaning. Luckily, there are several easy methods for it.

The TRIM function

Excel’s TRIM function automatically removes all excess spaces from a cell. “Excess” means:

  • Any spaces before the first non-space character
  • Any spaces after the last non-space character
  • Any interior spaces in excess of one

So for example “  spaced    out text    ” would be changed to “spaced out text”. The syntax is simply:

=TRIM(cell)

This only accepts one cell as an input, unless you are on Excel 365 in which case a range input will create a spilled range output.

Normally you would use TRIM to create a trimmed version of your text, and then paste the resulting trimmed text as values over the originals.

Further trimming considerations

Webpages often include a character called a “non-breaking space”; this appears identical to a regular space to a human user, but computers use these to prevent unintuitive presentation of text. For example the clause “100 km” might use a non-breaking space so that it is never split over a line indent:

Excel screenshot

But the existence of these characters can cause TRIM to fail on text copied or imported from web sources.

To fix this, you need to change those non-breaking spaces back into regular spaces before trimming, with a formula such as:

=TRIM(SUBSTITUTE(text, CHAR(160), " "))

CHAR(160) is the Excel identification for the non-breaking space; a regular space would be CHAR(32).

You can also use Power Query to perform this task. If you have a column of data with extra spaces in it, then Transform => Format => Trim will remove any leading and trailing spaces. Annoyingly, unlike the Excel TRIM function, interior spaces are not sorted out this way. If you want to do that, you instead will need to perform a series of column operations. Here’s some sample input data:

Excel screenshot

First we will use this custom function to split the text at each space:

Excel screenshot
We can then use List.Select to remove the blanks corresponding to the multiple spaces:
Excel screenshot

Finally, we can use Text.Combine to recombine the data, with a single space reinserted between each remaining entry:

Excel screenshot

We’ll look at these functions in more detail in a couple of weeks, when we’ll be discussing how to write Power Query custom functions in a little more detail.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250