Trim spaces button on the ribbon

Is that this is a quick clean up task, and you don't need to write formulas, or use extra columns on your worksheet.Code: TrimExtraSpaces_App To add this Excel VBA macro to your workbook, copy the code shown below. Then, paste the code into a regular code module in your workbook.To run the macro, follow these steps: Select the cells that you want to remove extra spaces from. Next, on the Excel Ribbon, click the View tab At the right end of the tab, click Macros In the list of macros, click the macro named TrimExtraSpaces_App Click the Run button All normal spaces are removed from the selected cells, except for a single space between words. Remaining cells on the worksheet are not affected. Tip: You could add a Trim button on the worksheet, to run the macro, if data needs to be cleaned frequently.Sub TrimExtraSpaces_App() Dim rng As Range Set rng = Selection rng.Value = Application.Trim(rng.Value)End Sub Application.Trim vs WorksheetFunction.Trim In Excel VBA, there are two Trim functions: Application.Trim WorksheetFunction.Trim Function SimilaritiesBoth functions do the same task -- they remove all spaces from the input, except for a single space between words.Function DifferencesAlthough the results are the same, here are the differences betwee the two Trim functions:Application.Trim can accept a string, or a range of cells, as its input, and it returns a variant data type. In the macro shown above, the entire selection had its values trimmed with a single step in the code: rng.Value = Application.Trim(rng.Value)WorksheetFunction.Trim can only

And straightforward. Nevertheless, if someone wants to have a closer look at the formulas discussed in this tutorial, you are welcome to download the Trim Excel Spaces Workbook.Excel TRIM not workingThe TRIM function only removes the space character represented by code value 32 in the 7-bit ASCII character set. In the Unicode character set, there is one more space character called the non-breaking space, which is commonly used on web pages as the html character . The nonbreaking space has a decimal value of 160, and the TRIM function cannot remove it by itself.So, if your data set contains one or more white spaces that the TRIM function does not remove, use the SUBSTITUTE function to convert non-breaking spaces into regular spaces and then trim them. Assuming the text is in A1, the formula goes as follows:=TRIM(SUBSTITUTE(A1, CHAR(160), " "))As an extra precaution, you can embed the CLEAN function to clean the cell of any non-printable characters:=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))The following screenshot shows the difference:If the above formulas do not work for you either, chances are that your data contain some specific nonprinting characters with code values other than 32 and 160. In this case, use one of the following formulas to find out the character code, where A1 is a problematic cell:Leading space: =CODE(LEFT(A1,1))Trailing space: =CODE(RIGHT(A1,1))In-between space (where n is the position of the problematic character in the text string):=CODE(MID(A1, n, 1)))And then, supply the returned character code to the TRIM(SUBSTITUTE()) formula discussed above.For example, if the CODE function returns 9, which is the Horizontal Tab character, you use the following formula to remove it:=TRIM(SUBSTITUTE(A1, CHAR(9), " "))Trim Spaces for Excel - remove extra spaces in a clickDoes the idea of learning a handful of different formulas to deal with a trivial task sound ridiculous? Then you may like this one-click technique to get rid of spaces in Excel. Let me introduce you to Trim Spaces tool included in our Ultimate Suite.With Ultimate Suite installed in your Excel, removing spaces in Excel is as simple as this:Select the cell(s) where you want to delete spaces.Click the Trim Spaces button

Down the square on the bottom-right corner of the cell to Autofill the formula down to the entire list in column B.Your cleaned list will now look like this:Tip: Occasionally, besides spaces, there could be non-printable characters present in your text. While the TRIM function solely addresses spaces, the CLEAN function specializes in removing any non-printable characters within the text. A powerful strategy involves employing both functions in tandem to effectively eliminate both spaces and non-printable characters.How to Delete Spaces in Excel Using Find and ReplaceThe Find and Replace feature in Excel offers several advantages for removing spaces, including efficiency, precision, customization, time-saving, and the ability to target specific spaces without altering formulas. It allows users to quickly and accurately clean data throughout the spreadsheet, making it a valuable tool for maintaining data accuracy and integrity. To remove spaces in Excel using the Find and Replace feature, follow these steps:Open the spreadsheet containing the data you want to work with.Choose the range of cells where you want to remove spaces. You can select a single column, multiple columns, or the entire worksheet, depending on your needs.On Windows: Press Ctrl + H or navigate to the Home tab in the Excel ribbon, and in the Editing group, click on Find & Select, then select Replace.On Mac: Go to the Edit menu and choose Find > Replace.In the Find what field of the Find and Replace dialog box, enter a single space character by pressing the spacebar once.Ensure that the Replace with

One space character, it's important to repeat the final step, and click the Replace All button again. In some cells, there could be three or more space characters between two words, or at the start or end of the cell's text. So, click Replace All again, to see the confirmation message. Repeat this step, until you see a warning message, saying that there wasn't anything to replace. "We couldn't find anything to replace. Click Options for more ways to search. FYI: It's possible the data you're trying to replace is in a protected sheet. Excel can't replace data in protected sheets." When that message appears, click the OK button, to close the message Then, in the Find and Replace dialog box, click the Close button Video: Find and Replace with Wildcards In this video, you'll see how to use the Find and Replace command, to clean the data in a contact list. There are written steps, and more examples, on the Excel Find and Replace page. Video Timeline 00:00 List of Names to Clean Up 01:02 Make a Backup of Data 01:30 Remove Colons and Text 02:30 Remove Hyphens and Text 03:00 Remove OF and Text 5) Remove Spaces - Excel Macro Instead of using TRIM formulas to remove spaces, you can use a VBA Trim function in an Excel macro, to remove leading spaces, trailing spaces, and extra spaces. Note: There are two Trim functions in Excel VBA, described in the next section The advantage of using a macro

Cell B7 combines CLEAN with SUBSTITUTE and CHAR: =CLEAN(SUBSTITUTE(B5,CHAR(160),"")) Formula C: Finally, in cell B8, the TRIM function is added, to remove extra spaces: =TRIM(CLEAN(SUBSTITUTE(B5,CHAR(160),""))) So, in your Excel worksheets, use formulas that combine CLEAN, SUBSTITUTE and TRIM, if your data contains different types of non-printing characters, and extra spaces to remove. 4) Find and Replace Command If you need to remove extra spaces between words on an Excel worksheet, the Find and Replace command can help you do that, without the need for any formulas. Find and Replace Extra Spaces To remove extra spaces between words on an Excel worksheet, using the Find and Replace command, follow these steps: Select the cells that you want to clean up Note: It's okay to include cells that do NOT have extra spaces -- they won't be affected To open the Find and Replace window, press the keyboard shortcut Ctrl + H In the Find What box, type two space characters Press the Tab key on your keyboard, to move to the Replace With box In the Replace With box, type one space character Click Replace All Excel shows a confirmation message, with the number of replacements that were made. In the screen shot above, the message says: "All done. We made 16 replacements." Click the OK button, to close the message, but don't close the Find and Replace window! The cleanup might not be "All done". Repeat the Replace All When you're using this method, to replace two space characters with

The tutorial demonstrates a few quick and easy ways to trim Excel spaces. Learn how to remove leading, trailing, and extra spaces between words, why Excel TRIM function is not working and how to fix it.Are you comparing two columns for duplicates that you know are there, but your formulas cannot find a single duplicate entry? Or, are you adding up two columns of numbers, but keep getting only zeros? And why on earth does your obviously correct Vlookup formula return just a bunch of N/A errors? These are only a few examples of problems that you may be seeking answers to. And all are caused by extra spaces hiding before, after or between numeric and text values in your cells.Microsoft Excel offers a few different ways to remove spaces and clean up your data. In this tutorial, we will investigate the capabilities of the TRIM function as the fastest and easiest way to delete spaces in Excel.Excel TRIM function syntaxHow to use TRIM in Excel - formula examplesTrim spaces in an entire column of dataDelete leading spaces in a numeric columnRemove leading spaces only (Left Trim)Count extra spaces in cellsHighlight cells with any excess spacesExcel TRIM not working - reasons and solutionsOne-click way to trim spaces in ExcelTRIM function - remove extra spaces in ExcelYou 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.The syntax of the TRIM function is the easiest one could imagine:TRIM(text)Where text is a cell that you want to remove excess spaces from.For example, to remove spaces in cell A1, you use this formula:=TRIM(A1)And the following screenshot shows the result:Yep, it's that simple!Please note that the TRIM function was designed to remove only the space character, which has value 32 in the 7-bit ASCII code system. If in addition to extra spaces, your data contains line breaks and non-printing characters, use the TRIM function in combination with CLEAN to delete the first 32 non-printing characters in the ASCII system.For example, to remove spaces, line breaks and other