Hello guys in this guide, I am gonna tell you about How To Remove Blank Spaces in MS Excel. So read this article, step by step so you may understand it in a better way.
Guide: How To Remove Blank Spaces in MS Excel
Excel cells can have many blank spaces if you copy and paste data from websites or other documents into your spreadsheets. Of course, you can manually edit any of the cells to remove the empty space as needed. However, that may take a while if there are many cells with a large empty space. Better to remove spaces in cells with some of Excel’s functions and tools. In this way, you can remove spaces in Excel spreadsheets with TRIM, SUBSTITUTE, Find and Replace tool and Kutools add-on.back to menu ↑
The TRIM function
TRIM is an Excel function that removes most spaces from a text string, except some spacing. So this is a good function to include all leading and trailing spaces in cells with. However, it does not work with non-breaking spaces ().
For example, open a blank Excel spreadsheet and type “5634 525626” in cell B2 with two leading spaces, three spaces between the numbers, and two trailing spaces at the end. Then click on cell C2 and press the fx button to open the Insert Function window. Select TRIM and press OK to open the window below.
Press the cell reference of the text field button and select B2. Press OK button to close the window. That removes all leading and trailing spaces and two of the spaces between the numbers.
To remove spacing in cells with multiple columns, you can copy the TRIM function to adjacent cells. First select C2 and press the shortcut Ctrl + C. Then left click the bottom right corner of C2 and drag the rectangle over the other cells to copy the function to it as below.
The SUBSTITUTE function
SUBSTITUTE is a better function if you need to remove all spaces between cells. This function replaces text or numeric values with alternative data, but you can also remove all empty spaces with it. The SUBSTITUTE syntax is: SUBSTITUTE (text, old_text, new_text, ).
To add SUBSTITUTE to your spreadsheet, select cell D2 and press fx button. Select SUBSTITUTE in the Insert Function window. Hit OK to open the window directly below.
Press the cell reference of the text field button. Select the cell you entered 5634 525626, it was B2. Then enter “” in the Old_text box and enter “” in the New_text box, as shown in the shot below.
Now press OK button to add the function to a spreadsheet. That will remove all distances from the text in cell B2 as below. You can copy the function to other cells in the same way as TRIM.
back to menu ↑
The Find and Replace tool
Find and Replace is a tool similar to the SUBSTITUTE function. It finds and replaces text in cells. You can also use the tool to remove leading, trailing, and excess spacing between text within a range of selected cells. The advantage of the Find and Replace tool is that you can remove the spaces directly from the cells without adding additional spreadsheet columns for functions.
First, enter the numbers ‘435 789’ in cell B3 with two leading and two trailing spaces plus three spaces between the values. Select B3 and press the Ctrl + H keyboard shortcut to open the Find and Replace window shown directly below. Typically, you type the text to replace in the Find What box, and then enter the replacement in Replace with. To remove the distance between the selected cell, press the spacebar twice in the Find what text box.
Press Replace All button. A dialog box will appear up to inform you that Excel has made some replacements. Hit OK to close that window. Now B3 will contain only the only space between the two numbers with no leading or trailing spacing.
To clear all cell spaces with the Find and Replace tool, select B3 again and press Ctrl + H. Now enter one space in the Find What text box. The Replace with field cannot contain spaces. That removes any remaining space in B3 when you press Replace All.
back to menu ↑
Delete spaces with Kutools
Kutools is one of the best Excel add-ons, selling for $ 39 on this website. Kutools also includes a Remove Spaces tool to remove cell spacing. If you have that add-on installed, select a range of cells from which you want to remove the spacing. Then click the Kutools tab in Excel, Text and select Delete Spaces from the menu. That will open a Remove Spaces window from which you can select All Spaces to remove the spacing. Alternatively, you can also select options to delete leading, trailing distance or excess spacing.
So Excel has a few functions, tools and add-ons to remove spacing between spreadsheet cells. They will certainly come in handy for removing space markup from pasted cell contents. For more information about how to remove extra spaces in cells, watch this YouTube video.back to menu ↑
How To Remove Blank Spaces in MS Excel: benefits
- The How To Remove Blank Spaces in MS Excel tutorial is free .
- This guide already helps so many users follow up with interest in a timely manner.
- The price of the How To Remove Blank Spaces in MS Excel guide is free.
Tutorial summary of How To Remove Blank Spaces in MS Excel
How this tutorial helping you?
What is actual time in which this method complete?
What are the supported Device?
back to menu ↑
I hope you like the guide How To Remove Blank Spaces in MS Excel. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.
For our visitors: If you have any queries regards the How To Remove Blank Spaces in MS Excel, then please ask us through the comment section below or directly contact us.
Education: This guide or tutorial is just for educational purposes.
Misinformation: If you want to correct any misinformation about the guide “How To Remove Blank Spaces in MS Excel”, then kindly contact us.
Want to add an alternate method: If anyone wants to add more methods to the guide How To Remove Blank Spaces in MS Excel, then kindly contact us.
Our Contact: Kindly use our contact page regards any help. You may also use our social and accounts by following us on Whatsapp, Facebook, and Twitter for your questions. We always love to help you. We answer your questions within 24-48 hours (Weekend off).
Channel: If you want the latest software updates and discussion about any software in your pocket, then here is our Telegram channel.