So if you want to use the formula, the just copy it from above and replace “B5” with you cell reference.
If you have many cells with spaces in the end or you are not sure if there is, you should consider using a formula. For further details on how to remove extra spaces in cells, check out this YouTube video.Remove the last character if it is blank with a formula in Excel They’ll certainly come in handy for removing space formatting from pasted cell content. So Excel has a few functions, tools and add-ons to remove spacing from spreadsheet cells with. Alternatively, you can also select options to erase leading, trailing or excess spacing. That opens a Remove Spaces window from which you can select All spaces to delete the spacing. I know how to get the merge to remove empty lines when fields are empty, but I cannot get it to remove empty spaces when there are more than one field on a line. Then click the Kutools tab in Excel, Text and select Remove Spaces from the menu. I have set up a mail merge using Publisher, with Excel as the data source.
If you’ve installed that add-on, select a range of cells to remove spacing from. Syntax: TRIM(text) Excel TRIM function takes the cell reference (or text) as the input. Here is a neat way to get rid of these extra spaces Use TRIM Function. While you may somehow spot the extra spaces between words or numbers, trailing spaces are not even visible. Kutools also includes a Remove Spaces tool to delete cell spacing with. Extra spaces are painfully difficult to spot. Kutools is one of Excel’s best add-ons, which is retailing at $39 on this website. That will remove the one remaining space in B3 when you press Replace All. The Replace with field should not including any spacing. Now enter the one space in the Find what text box. To erase all cell spacing with the Find and Replace tool, select B3 again and press Ctrl + H. Now B3 will include just the one space between the two numbers with no leading or trailing spacing. A dialog window pops up informing you that Excel has made some replacements. To remove spacing from the selected cell, press Space twice in the Find what text box. It is very useful when cleaning up text from other applications or environments. Let's say we want to remove those spaces for uniformity's sake. Thus, it will leave only single spaces between words and no space characters at the start or end of the text.
Below is an example of a report where some of the cells look like they are indented but the indentation is actually just extra spaces. Usually, you would enter text to replace in the Find what box and then input the replacement in Replace with. The TRIM function removes all spaces in a text string, except for single spaces between words. Select B3 and press the Ctrl + H hotkey to open the Find and Replace window shown directly below.
The advantage Find and Replace tool is that you can remove the spaces directly from the cells without adding extra spreadsheet columns for functions.įirst, input the numbers ‘ 435 789 ‘ in cell B3 with two leading and two trailing spaces plus three spaces between the values. You can also delete leading, trailing and excess spacing between text within a range of selected cells with the tool. Find and Replace is a tool that’s similar to the SUBSTITUTE function.