TRIM Function
Removes extra spaces in the data
Removes extra spaces in the data
The TRIM function is categorized under Text functions. TRIM helps remove the extra spaces in the data and thus clean up the cells in the worksheet.
In financial analysis, the TRIM function can be useful in removing irregular spacing from data imported from other applications.
=TRIM(text)
TRIM is a built-in function that can be used as a worksheet function in Excel. To understand the uses of the function, let us consider a few examples:
Suppose we are given the following data from an external source:
For trimming the spaces, we will use the following function:
We get the results below:
Let us see how the TRIM function works for numbers. Suppose we are the given the data below:
The formula to use is:
Using the TRIM function, we will get the following result:
As you can see above, the trimmed values are text strings, while we want numbers. To fix this, we will use the VALUE function along with the TRIM function, instead of the above formula:
The above formula removes all leading and trailing spaces, if any, and turns the resulting value into a number, as shown below:
To join multiple cell values with a comma, we can use a formula based on the SUBSTITUTE and TRIM functions. Suppose we are given the following data:
The formula to use is:
The formula first joins the values in the 4 cells to the left using the concatenation operator (&) and a single space between each value. The TRIM function is used to “normalize” all spacing. TRIM automatically strips space at the start and end of a given string and leaves just one space between all words inside the string. It takes care of extra spaces caused by empty cells. SUBSTITUTE is used to replace each space (” “) with a comma and space (“, “). We used a delimiter to join cells.
We get the results below:
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!