TRIM Function

It removes the additional spaces between words and returns a text string with neither space at the start or end of the text string.

Author: David Bickerton
David Bickerton
David Bickerton
Asset Management | Financial Analysis

Previously a Portfolio Manager for MDH Investment Management, David has been with the firm for nearly a decade, serving as President since 2015. He has extensive experience in wealth management, investments and portfolio management.

David holds a BS from Miami University in Finance.

Reviewed By: Osman Ahmed
Osman Ahmed
Osman Ahmed
Investment Banking | Private Equity

Osman started his career as an investment banking analyst at Thomas Weisel Partners where he spent just over two years before moving into a growth equity investing role at Scale Venture Partners, focused on technology. He's currently a VP at KCK Group, the private equity arm of a middle eastern family office. Osman has a generalist industry focus on lower middle market growth equity and buyout transactions.

Osman holds a Bachelor of Science in Computer Science from the University of Southern California and a Master of Business Administration with concentrations in Finance, Entrepreneurship, and Economics from the University of Chicago Booth School of Business.

Last Updated:September 30, 2022

The TRIM function in Excel removes the additional spaces between words and returns a text string with neither space at the start or end of the text string.

Have you ever worked on Excel files with disorganized data, even in a single cell? For example, we have seen files with street addresses separate cities by hundreds of space characters.  

You would ask, wait, what's wrong with that? Isn't it still the correct required value? You can even wholly read it when the cell is highlighted.

The problem arises when you edit the cell to a different value. We must skip all those space characters before replacing the address's cities or street components.

The simple remedy for this problem is to use this function. It removes the spaces, maintains the data integrity, and improves the efficiency of data handling formulas on the same dataset.

In this article, we will see how you can use the function and use it best by incorporating it into your daily Excel activities.

TRIM function - What is it?

The function is categorized as a text function that removes all the spaces between the words, from the beginning and end of the text string, except for a single space between the words.

For example, if you have the text in the spreadsheet as "Apple Inc has more cash than the US Government. "and use the function, the result will be "Apple Inc has more cash than the US Government."

See the magic? All the additional spaces that the sentence had is completely removed with the help of the function. So even though it has quite a simple purpose, the function is vital in the Excel community.

You must have looked at numerous Excel spreadsheets with many additional spaces, which can be a nuisance when conducting financial analysis.

First and foremost, if you are making an 'apples to apple comparison' for the columns in Excel as a part of the data analysis routine, any cell that contains additional spaces will never return as 'equal' to its counterpart since Excel counts the space as an extra character. 

The VLOOKUP and INDEX MATCH can also be affected if the cell contains extra space between the words. As a result, the function forms one of the most essential 'text' functions in the community for all the Excel wizards.

The syntax for the function is:

=TRIM(text)

where,

text = (required) text string enclosed inside the quotation marks, a cell reference for the text string, or a formula that returns text.

Note

The function only takes in one argument, text, and usually takes in one cell address if referenced in the formula. This means if you try to reference multiple cells in the formula, you will get a dialog box that says, "You've entered too many arguments for this function."

Alert

Example

Now that we understand the syntax for the function, let us see a couple of examples to discuss using the function in Excel. 

Suppose that you have the data as illustrated below:

World

We will write the formula

=TRIM(B3)

In the cell C3, which will give us the result in the spreadsheet:

Words

All the spaces between the words were removed immediately using the function, so the space after the word 'low.' 

The text string only left one space between the consecutive words and returned the result in cell C3. After dragging down the formula up to cell C8, we will get the result:

Code

The function is designed to remove the space character with a value of 32 in the 7-bit ASCII code system

Once the trimmed data is returned, you can PasteSpecial the data into a new cell first by copying the cells using Ctrl + C and then using the keyboard shortcut of Ctrl + Alt + V + V. 

This would PasteSpecial the values and avoid any errors in the formula if the data in the B column were to be deleted.

Counting the additional spaces in Excel

There could be instances where you might need to determine the number of additional spaces in the text strings. In such cases, we use the LEN function that calculates the length of the enclosed line. 

The LEN function also counts the space character in its result, including those that separate two words. So, for example, let's say we have the text strings in Excel as illustrated below:

Sheet

To count the total number of characters, we will first calculate the length of the text string. Using the formula

=LEN(B3)

We get the total number of characters in cell B3 as 54. This is because even though there are only 14 letters and 1 punctuation mark, all the extra spaces make up 54 characters.

But this isn't the end of it. Next, we find the string length without all the extra characters using the function, which returns the result as 18. The calculations behind the total are 14 letters + 1 punctuation + 3 spaces that separate the four words, which equals 18.

 Now, if we subtract both the results or use the formula

=LEN(B3)-LEN(TRIM(B3))

it will give the effect of the extra spaces in our cells. The spreadsheet will look as illustrated below:

Illustrations

The function helps to remove all the additional spaces that might be present in a workbook. If you are working on large datasets obtained from third-party resources, there is a high probability you might end up working with this function a lot.

Almost all the data cleansing task involves this function and can be used in combination with other parts to provide more accurate and understandable reports.

This section will see practical examples of using the function to obtain clean data.

Practical #1

Suppose that using the VLOOKUP function, you pull in the numerical data in the spreadsheet as illustrated below:

Sheet

You need to find the sum of the data and remove the additional spaces from the cells. The formula that you will use is =TRIM(B3) which will give you the result as:

Sum

Finally, we will calculate the sum of the numbers using the formula

=SUM(C3:C8)

Giving us the result as 0. Wait, what?

Data

So actually, what happened is that Excel mistook the numbers for text strings and hence could not return the sum for the range of numbers. There are two ways you can overcome this issue in Excel:

Method #1: Multiplying the result with 1

The first method to turn the numbers from their text format into numerical values is multiplying the result from the formulas by 1.

Our formula in the spreadsheet becomes

=TRIM(B3)*1

which will give you the result in the spreadsheet as illustrated below:

Method

After changing our numbers from 'text format' into their values, we get the sum for the numbers as 210 in Excel.

Method #2: Using the VALUE function

The VALUE function in Excel changes the format numbers represented as text strings into a numerical value. 

For example, if you have the date as 1st January 1900 enclosed in the VALUE function, you will get the result as 1(since dates are serial numbers and the first date is Excel begins from 1st January 1900).

The syntax for the VALUE function is:

=VALUE (text)

where,

text = (required) text string inside the quotation marks, a cell reference for the text string, or a formula that returns text

Now, if you combine the VALUE and the function, the latter will remove all the unnecessary spaces between the text. In contrast, the former function will convert the text string into its number format if it holds any numerical values.

The formula becomes 

=VALUE(TRIM(B3))

 which will give you the result:

Sum

Two methods provide the exact solution; follow what works best for you!

Practical #2

Another function you can use in combination with function is the CLEAN function. For example, when you download files from the internet or fetch data in CSV format using a database such as Excel, the spreadsheet might import characters that are not printed when the print option is used in Excel.

Such characters cannot be removed by solely using the function. Hence, we also need to use the CLEAN function that removes non-printable characters, such as line breaks or text wraps.

Assume that you have the data in the spreadsheet as illustrated below:

Input

As we can see, our data also has line breaks, text wraps, and extra spaces in the cells. Therefore, the formula that we will use in Excel is

=TRIM(CLEAN(B3))

To give you the result in the spreadsheet as illustrated below:

Result

It looks pretty much the same as before. 

The formula has worked; all you need to do is PasteSpecial the values in column B, and the spreadsheet line breaks will return to normal. 

Copy the result in column C using the keyboard shortcut of Ctrl + C, press Ctrl + Alt + V, and then V to PasteSpecial the values in column B. You will get the result in the spreadsheet as follows:

Practical #3

What if you have two cells with extra spaces but need to combine the value for both in the final results, including removing the areas? For example, assume that you have two input text strings in Excel, as illustrated below:

Input

To get the result in a single cell, we will use the formula

=TRIM(B3&C3)

In cell D3, drag the formula down, which will give you the result:

Result

Once you drag down the formula, you will notice that the two input texts concatenate, and all the unnecessary spaces between the text strings are removed, excluding the 'standard' space between two text strings.

This way, you can concatenate numerous text strings together and perform different operations on text strings, such as removing additional spaces using TRIM, returning the text in the proper case using the PROPER function, or even the upper case using the UPPER function.

The magic of concatenation never ceases to amaze us, right

Key Takeaways

  • Using the function, you can remove all the additional spaces with only one space between two words and no space at the end or beginning of the sentence.
  • The function will not affect the non-breaking space characters commonly present in downloaded web files.
  • The function traditionally accepts only one text string. However, you can use the concatenation symbol '&' to link multiple cell references in the formula.
  • Ther function does not remove the space character called the non-breaking space character usually used in Web pages as an HTML entity. In that case, you need to use either the CODE or CHAR function.

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources: