A text function used to remove spaces from a text string, except for a single space between words.
Removes extra spaces in the data Show
What is the TRIM Function?The TRIM Function[1] is categorized under Excel Text functions. TRIM helps remove the extra spaces in 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. Formula=TRIM(text)
A few notes about the TRIM Function:
How to use the TRIM Function in Excel?TRIM is a built-in function that can be used as a worksheet function in Excel. To understand the uses of the function, let’s consider a few examples: Example 1Suppose 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: Example 2Let 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: Example 3To 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 four 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 Additional ResourcesThanks for reading CFI’s guide to this important Excel function. 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 CFI resources:
Article Sources
Which text function would remove all spaces from a string except for single spaces between words?The TRIM function is relatively straightforward. Simply give it some text and it removes all spaces from the text except for single spaces between words. The TRIM function was designed to trim only the ASCII space character from text.
How do I remove spaces from a text string?If you just want to remove the extra space, leading spaces and the trailing spaces from strings, you can apply this formula: Select a cell where you will put the result, and type this formula =TRIM(A2), press Enter key, and then the drag the auto fill handle over the cells which need this formula.
What function would you use to remove blank spaces from the beginning of a text string?The TRIM Function
The TRIM function removes all spaces in a text string, except for single spaces between words.
Which of this function is used to remove more than one space?You use the TRIM function in Excel removes extra spaces from text. It deletes all leading, trailing and in-between spaces except for a single space character between words. Where text is a cell that you want to remove excess spaces from.
|