Where is the TRIM spaces button in Excel

This Excel tutorial explains how to use the Excel TRIM function with syntax and examples.

The Microsoft Excel TRIM function returns a text value with the leading and trailing spaces removed. You can also use the TRIM function to remove unnecessary spaces between words in a string.

The TRIM function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) and a VBA function (VBA) in Excel. As a worksheet function, the TRIM function can be entered as part of a formula in a cell of a worksheet. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

The syntax for the TRIM function in Microsoft Excel is:

TRIM( text )

Parameters or Arguments

text The text value to remove the leading and trailing spaces from.

The TRIM function returns a string/text value.

Applies To

• Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)
• VBA function (VBA)

Let's look at some Excel TRIM function examples and explore how to use the TRIM function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following TRIM examples would return:

=TRIM(A1) Result: "Tech on the Net" =TRIM(A2) Result: "1234" =TRIM(A3) Result: "alphabet soup" =TRIM(A4) Result: "www.techonthenet.com" =TRIM("  apples ") Result: "apples"

Clean Up Spacing Between Words

You can also use the TRIM function in Excel to remove unnecessary spaces between words in a string. The TRIM function will reduce the spacing between words down to a single space.

Let's look at an example of how to use the TRIM function to clean up the spacing between words in Excel.

Based on the spreadsheet above:

=TRIM(A1) Result: "techonthenet.com is a great resource for Excel" =TRIM("123 Main St.") Result: "123 Main St."

In these two examples, the TRIM function will reduce the spacing between words down to a single space. This is a great way to clean up data that may not be formatted properly with unnecessary extra spaces.

The TRIM function can also be used in VBA code in Microsoft Excel.

Let's look at some Excel TRIM function examples and explore how to use the TRIM function in Excel VBA code:

Dim LResult As String LResult = Trim ("   Alphabet   ")

The variable LResult would now contain the value of "Alphabet".

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less

This article describes the formula syntax and usage of the TRIM function in Microsoft Excel.

Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

Important: The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this nonbreaking space character. For an example of how to trim both space characters from text, see Top ten ways to clean your data.

TRIM(text)

The TRIM function syntax has the following arguments:

• Text    Required. The text from which you want spaces removed.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Formula	Description	Result
=TRIM(" First Quarter Earnings ")	Removes leading and trailing spaces from the text in the formula (First Quarter Earnings)	First Quarter Earnings

• Create new column in Excel for clean text. Use TRIM function to copy and paste clean text into original column.
• Ex.: In cell A6, type =TRIM(A4) > Enter > right-click A6 > Copy. Right-click A4 > Paste Special > Values > OK.
• Use CTRL+H to search the problem column and quickly remove all extra spaces.

This article explains how to use Excel's TRIM function and the CTRL+H shortcut key to remove unnecessary spacing from your worksheet.

To remove extra spaces with the TRIM function, follow this procedure:

1. Click cell A6 to make it the active cell – this is where the function will go.
2. Type =TRIM(A4).
3. Press Enter.
4. The text from A4 with extra spaces will appear in A6, but with all of the extra spaces removed.

The problem at this point is that both A4 and A6 exist in the sheet when you only want the clean text (in A6) to replace the messy text (in A4).

Replacing the content from A6 to A4 is simple to accomplish:

1. Right-click A6.
2. Select Copy.
3. Right-click A4.
4. Select Paste Special.
5. Select Values and then click OK.
6. Delete the text from A6.

Now only the clean text, without spaces, exists on the sheet.

If you're using Excel Online, disregard steps 4 and 5 above. Instead, click the down arrow under Paste on the Home ribbon, then select Paste Values.

The method above works well if you're only cleaning spaces from one cell, but what if you want to remove spaces from an entire column?

The procedure for dealing with a column isn't that different from dealing with one cell. In this example, the column with too many spaces starts with A2.

1. Create a new empty column beside the column you want to remove spaces from (in this example this new column starts with B2).
2. Click the top empty cell in your new column (B2).
3. Type =TRIM(B2) and press Enter.
4. Hold down the Shift key, and hover your mouse at the bottom of cell B2 until the cursor changes to two horizontal lines with an arrow above and below them. (This feature isn't available in Excel Online; you'll have to use the Fill Handle to copy the formula.)
5. Double-click the right mouse button to auto-fill the entire column

Now you'll have column A with the extra spaces, and column B with the same text cleaned up.

Perform the same copy and paste (values only) procedure in the previous step, but copy and paste the entire columns rather than just individual cells.

Finally, right click at the top of column B to highlight the entire column, and delete it, shifting all other cells on the spreadsheet to the left.

If the text in your column has extra spaces within the text, not right before or right after, you can quickly clean that text by using Excel's Search and Replace feature.

1. Select the entire column you want to clean.
2. Press the CTRL+H shortcut to open Excel's Search and Replace window.
3. In the Find what field, type two spaces.
4. In the Replace with field, type one space.
5. Keep pressing the Replace All button until all extra spaces are gone.

The benefit of using this approach is that you don't have to go through the entire copy-and-paste procedure that you need to with the TRIM function.

The method you use to remove extra spaces in Excel depends on what your original data looks like, and where the spaces are.

Either way, one of these two methods should work well to help you clean up that messy text in your spreadsheet.

The syntax for the TRIM function is:

=TRIM(Text)

The Text, in this case, refers to the data with the extraneous spacing. This argument can be:

• The actual text enclosed in quotation marks
• A cell reference to the location of the text data in the worksheet

Many people think that the TRIM function removes only the spaces at the beginning and the end of the text. In actuality, it gets rid of all extra spaces.

For example, if the text in cell A1 is:

This is a really bad sentence.

The Excel function =TRIM(A1) will provide the following result:

This is a really bad sentence.

Thanks for letting us know!

Tell us why!