Excel TRIM, SUBSTITUTE, and Removing Spaces

The Excel TRIM function makes removing spaces from the ends of words very easy. In fact, it is frequently necessary to add or remove spaces from strings of text in Excel. Using the Excel TRIM function or the SUBSTITUTE function makes adding (and especially removing) these spaces very easy to do. If you know how to use both of these functions, you will never have to go through a spreadsheet and manually remove the extra spaces in cells ever again!

The Excel TRIM Function

Excel TrimThe Excel TRIM function is very simple to use. The syntax is simply =TRIM(Cell). When you use TRIM, it will remove all of the extra spaces on either side of a string of text in a cell. So, if you have some data in a cell that is preceded by a space or followed by a space, you can use TRIM to remove those spaces quickly. However, note that TRIM is somewhat limited, in that it will not remove spaces that occur between words. So, if your string contains a series of words separated by spaces, TRIM will only remove the spaces that occur before the first word and after the last word.

The Excel SUBSTITUTE Function

The SUBSTITUTE function is similar to the TRIM function, but it is much more flexible. With SUBSTITUTE, you can remove spaces from anywhere within a string of text (including between different words, unlike TRIM). You can also use SUBSTITUTE to add/remove more than just spaces. You can use it to swap out letters, numbers, or even whole words! The syntax is also very simple. It is =SUBSTITUTE(Cell,Old-Text,New-Text). So, if you wanted to remove all of the spaces from cell A1, you would use =SUBSTITUTE(A1,” “,””). If you wanted to remove all of the “A”s from A1 and replace them with “B”s, you would change it to =SUBSTITUTE(A1,”A”,”B”). Note that capitalization counts here.

Using the Excel TRIM and SUBSTITUTE functions makes adding and removing spaces, letters, numbers, and words quick and easy. Used properly, these two functions can make cleaning up your data a breeze.

Learn even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.