Excel TRIM Function


The TRIM function is one of the (TEXT) functions of Excel. It removes all spaces from a text

string except for single spaces between words.

We can find this function in TEXT of insert function Tab.

How to use TRIM function in excel

  1. Click on empty cell (like F5 )
1

2. Click on fx on the below of font word (or press shift+F3)

3. In insert function tab you will see all functions

4. Select TEXT category

5. Select TRIM function

6. Then select ok

7. In function arguments Tab you will see TRIM function

8. Text is the text from which you want spaces removed

9. You will see the results in formula result section

Examples of TRIM function in Excel

  1. Removing leading and trailing spaces from a cell: =TRIM(" text with spaces ") which returns “text with spaces” without leading or trailing spaces.
  2. Removing extra spaces between words: =TRIM("text with extra spaces") returns “text with extra spaces” with only 1 space between each word.
  3. Removing non-breaking spaces: =TRIM(SUBSTITUTE("text with non-breaking spaces"," "," ")) replaces non-breaking space with normal space and then trims the resulting text.
  4. Cleaning up imported data: =TRIM(A2) where A2 is a cell containing imported data that may have leading or trailing spaces that need to be removed.
  5. Removing leading or trailing spaces from a range of cells: =TRIM(B2:B10) removes leading and trailing spaces from all cells in the range B2:B10.
  6. Removing multiple types of whitespace characters: =TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(9)," "), CHAR(160)," ")) replaces tab and non-breaking space with normal space, and then trims the resulting text.
  7. Using TRIM with CONCATENATE to clean up concatenated data: =TRIM(CONCATENATE(A2," ",B2)) concatenates two cells with a space in between and removes any leading or trailing spaces.
  8. Removing spaces from a specific location within a text string: =LEFT(A2,5)&TRIM(MID(A2,6,LEN(A2))) removes leading and trailing spaces from the text starting at position 6.
  9. Removing spaces from a specific location within a text string using SUBSTITUTE: =SUBSTITUTE(A2,MID(A2,6,1),"") removes the space at position 6 within the text.
  10. Removing extra spaces in a column of data: =TRIM(IFERROR(REGEX.REPLACE(A2," {2,}"," "),"")) removes any extra spaces between words in the text in cell A2, and then trims the resulting text. This is done for all cells in the column using copy-paste or the fill handle.

How does the TRIM function work in Excel?

The TRIM function in Excel is used to remove any leading or trailing spaces from a text string. It can also be used to remove extra spaces between words and other types of whitespace characters like tabs and non-breaking spaces.

The syntax for the TRIM function is simple: =TRIM(text), where “text” is the cell or text string that you want to trim.

Here are some examples of how the TRIM function works in Excel:

  1. Removing leading and trailing spaces: =TRIM(" text with spaces ") This formula returns “text with spaces” without any leading or trailing spaces.
  2. Removing extra spaces: =TRIM("text with extra spaces") This formula returns “text with extra spaces” with only one space between each word.
  3. Cleaning up imported data: =TRIM(A2) This formula removes any leading or trailing spaces from the text in cell A2, which is useful when working with imported data that may have inconsistent formatting.
  4. Removing non-breaking spaces: =TRIM(SUBSTITUTE("text with non-breaking spaces"," "," ")) This formula replaces non-breaking spaces with normal spaces and then trims the resulting text.
  5. Using TRIM with CONCATENATE: =TRIM(CONCATENATE(A2," ",B2)) This formula concatenates the text from cells A2 and B2 with a space in between and then removes any leading or trailing spaces.
  6. Removing extra spaces in a column of data with REGEX.REPLACE: =TRIM(IFERROR(REGEX.REPLACE(A2," {2,}"," "),"")) This formula removes any extra spaces between words in the text in cell A2 using REGEX.REPLACE, and then trims the resulting text. This can be done for all cells in the column using copy-paste or the fill handle.

What is the syntax of the TRIM function in Excel?

The syntax for the TRIM function is simple: =TRIM(text)

Here’s a breakdown of each part of the syntax:

  • =: This is the equal sign that starts all formulas in Excel.
  • TRIM: This is the name of the function that you want to use.
  • text: This is the argument that you want to apply the TRIM function to. It can be a cell reference or a text string enclosed in double quotes.

Here are a few examples to illustrate the syntax:

  1. =TRIM(A2) This formula applies the TRIM function to the text in cell A2.
  2. =TRIM(" example text with spaces ") This formula applies the TRIM function to the text string “example text with spaces”.
  3. =TRIM(B2:B10) This formula applies the TRIM function to all cells in the range B2:B10.

It’s worth noting that the TRIM function only removes spaces and other common whitespace characters (like tabs and line breaks). If you need to remove other types of characters, like non-breaking spaces, you may need to use additional functions like SUBSTITUTE or CLEAN.

Can the TRIM function remove non-breaking spaces in Excel?

Yes, the TRIM function in Excel can remove non-breaking spaces along with other leading and trailing white spaces. Non-breaking spaces are represented by the ASCII character code 160, which is different from regular space character represented by ASCII code 32.

Here’s an example of how to use the TRIM function to remove non-breaking spaces:

  1. Start by opening a new or existing Excel worksheet.
  2. In any cell, enter some text that includes non-breaking spaces. For example, you could enter ” This text contains non-breaking spaces ” (with non-breaking spaces between each word).
  3. Next, select an adjacent cell to the right or left of the cell with the text you just entered.
  4. In this cell, enter the following formula: =TRIM(cell_reference)

Replace “cell_reference” with the reference to the cell containing the text you want to clean up. For example, if you entered the text in cell A1, the formula would be “=TRIM(A1)”.

  1. Press Enter to apply the formula.

The TRIM function will remove all leading and trailing spaces, including non-breaking spaces, leaving only a single space between each word.

In our example, the formula would convert the original text ” This text contains non-breaking spaces ” to “This text contains non-breaking spaces”.

So, yes, the TRIM function can remove non-breaking spaces in Excel along with other types of spaces.

Does the TRIM function remove all leading and trailing spaces in Excel?

Yes, the TRIM function in Excel is used to remove all leading and trailing spaces (including multiple spaces) from a text string. When you have large data sets with inconsistent spacing, using TRIM can help you clean up your data and make it easier to work with.

Here’s an example of how to use the TRIM function to remove leading and trailing spaces:

  1. Start by opening a new or existing Excel worksheet.
  2. In any cell, enter some text with leading and/or trailing spaces. For example, you could enter ” This is an example “.
  3. Next, select an adjacent cell to the right or left of the cell with the text you just entered.
  4. In this cell, enter the following formula: =TRIM(cell_reference)

Replace “cell_reference” with the reference to the cell containing the text you want to clean up. For example, if you entered the text in cell A1, the formula would be “=TRIM(A1)”.

  1. Press Enter to apply the formula.

The TRIM function will remove all leading and trailing spaces from the text string, leaving only single spaces between words. In our example, the formula would convert the original text ” This is an example ” to “This is an example”.

It’s important to note that the TRIM function only removes leading and trailing spaces. It won’t remove spaces between words within the text string. If you need to remove all spaces, including those between words, you may need to use a combination of functions such as SUBSTITUTE and TRIM.

Can the TRIM function remove extra spaces between words in Excel?

The TRIM function in Excel can remove leading and trailing spaces from a text string, but it cannot remove extra spaces between words. To remove extra spaces between words, you can use the SUBSTITUTE function along with the TRIM function.

Here’s an example of how to use the TRIM and SUBSTITUTE functions together to remove extra spaces:

  1. Start by opening a new or existing Excel worksheet.
  2. In any cell, enter some text with extra spaces between words. For example, you could enter “This is an example”.
  3. Next, select an adjacent cell to the right or left of the cell with the text you just entered.
  4. In this cell, enter the following formula: =TRIM(SUBSTITUTE(cell_reference,” “,” “))

Replace “cell_reference” with the reference to the cell containing the text you want to clean up. For example, if you entered the text in cell A1, the formula would be “=TRIM(SUBSTITUTE(A1,” “,” “))”.

  1. Press Enter to apply the formula.

Can the TRIM function be used to remove all types of whitespace characters in Excel?

The TRIM function in Excel can be used to remove some types of whitespace characters, but not all. The TRIM function removes regular spaces (ASCII code 32), non-breaking spaces (ASCII code 160) and all other ASCII control characters from the beginning and end of a text string. However, it cannot remove other Unicode characters that are classified as whitespace.

Here’s an example of how to use the TRIM function to remove regular spaces and non-breaking spaces:

  1. Start by opening a new or existing Excel worksheet.
  2. In any cell, enter some text with different types of spaces. For example, you could enter ” This is an example with extra spaces “.
  3. Next, select an adjacent cell to the right or left of the cell with the text you just entered.
  4. In this cell, enter the following formula: =TRIM(cell_reference)

Replace “cell_reference” with the reference to the cell containing the text you want to clean up. For example, if you entered the text in cell A1, the formula would be “=TRIM(A1)”.

  1. Press Enter to apply the formula.

The TRIM function will remove all regular spaces and non-breaking spaces from the beginning and end of the text string, leaving only single spaces between words. In our example, the formula would convert the original text ” This is an example with extra spaces ” to “This is an example with extra spaces”.

However, the TRIM function cannot remove other Unicode whitespace characters such as em spaces, en spaces, thin spaces, or zero width spaces. To remove these types of spaces, you would need to use other functions such as SUBSTITUTE or a combination of functions.

Does the TRIM function affect the formatting of cells in Excel?

The TRIM function in Excel is a text function that allows you to remove all leading and trailing spaces from a given text string. The function does not affect the formatting of cells in Excel, but it can be useful in cleaning up text data before further analysis or use.

For example, let’s say you have a list of product names in an Excel worksheet. Some of the product names have extra spaces at the beginning or end of the text, which could cause issues if you want to use them in a database or perform calculations based on the product names. You can use the TRIM function to remove these spaces.

To use the TRIM function, simply enter “=TRIM(cell)” into an empty cell, where “cell” is the cell containing the text you want to trim. For instance, if you have a product name in cell A1, you can use the formula “=TRIM(A1)” in a different cell to remove the extra spaces.

Before using the TRIM function:

Product Name
Widget
Gadget
Product Name

After using the TRIM function:

Product Name
Widget
Gadget
Product Name

As you can see in the above examples, the TRIM function has removed all of the unnecessary spaces from the text strings, without affecting the cell formatting.

How can I use the TRIM function with other functions in Excel?

The TRIM function in Excel can be used in combination with other functions to further manipulate text data. Here are some examples of how you can use the TRIM function with other functions in Excel:

  1. CONCATENATE: The CONCATENATE function lets you combine multiple text strings into one. If you have text strings that contain extra spaces, you can use the TRIM function to remove the spaces before combining them with other strings using the CONCATENATE function. For example, if you have a first name and last name in separate cells, you can use the following formula to combine them into one cell:

=CONCATENATE(TRIM(A1)," ",TRIM(B1))

This formula will remove any extra spaces from the first name and last name before combining them with a space in between.

  1. SUBSTITUTE: The SUBSTITUTE function lets you replace all instances of a specific text string within another text string. If you have a text string that contains unnecessary spaces, you can use the TRIM function to remove the spaces before replacing the string using the SUBSTITUTE function. For example, if you have a text string in cell A1 that contains extra spaces before and after the word “apple”, you can use the following formula to replace all instances of “apple” while removing the extra spaces:

=SUBSTITUTE(TRIM(A1)," apple ","orange")

This formula will replace all instances of the word “apple” with the word “orange”, while removing any extra spaces before or after the word “apple”.

  1. LEFT/RIGHT: The LEFT and RIGHT functions let you extract a certain number of characters from the beginning or end of a text string. If you have a text string that contains extra spaces at the beginning or end, you can use the TRIM function to remove the spaces before extracting a certain number of characters using the LEFT or RIGHT function. For example, if you have a text string in cell A1 that contains extra spaces before and after the word “apple”, you can use the following formula to extract the first three characters while removing the extra spaces:

=LEFT(TRIM(A1),3)

This formula will extract the first three characters from the text string in cell A1, while removing any extra spaces before or after the text.

Are there any limitations or drawbacks to using the TRIM function in Excel?

While the TRIM function can be a useful tool for cleaning up text data in Excel, there are some limitations and drawbacks to keep in mind:

  1. The TRIM function only removes leading and trailing spaces: The TRIM function is designed to remove extra spaces from the beginning and end of a text string. It will not remove spaces within the string unless they are at the beginning or end. If you need to remove all spaces within a string, you may need to use another function such as SUBSTITUTE.
  2. The TRIM function does not work with non-breaking spaces: Non-breaking spaces are a special type of space that is used to prevent line breaks between words. The TRIM function will not remove these spaces. If your text data contains non-breaking spaces, you may need to use another function to remove them.
  3. The TRIM function is case sensitive: The TRIM function will only remove spaces that are typed as regular spaces (ASCII code 32). If your text data contains other types of spaces, such as tabs or non-breaking spaces, you may need to use a different function.
  4. The TRIM function can slow down large datasets: If you have a large dataset with many rows and columns, using the TRIM function can cause your workbook to slow down or freeze. This is because the function needs to process each cell individually. To avoid this issue, it’s recommended to use the TRIM function on smaller sections of your dataset at a time.

Leave a Reply

Your email address will not be published. Required fields are marked *