How To Remove Blank Spaces in MS Excel

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.

Excel distance 2

back to menu ↑

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.

Excel spacing3

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.

Excel distance 4

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.

Excel distance 5

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.

Excel distance 6

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.

Excel distance 7

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.

back to menu ↑

Faq

Tutorial summary of How To Remove Blank Spaces in MS Excel

In this guide, we told you about the How To Remove Blank Spaces in MS Excel; please read all steps so that you understand How To Remove Blank Spaces in MS Excel in case if you need any assistance from us, then contact us.

How this tutorial helping you?

So in this guide, we discuss the How To Remove Blank Spaces in MS Excel, which undoubtedly helps you.

What is actual time in which this method complete?

The time to complete the How To Remove Blank Spaces in MS Excel tutorial is 10+ minutes.

What are the supported Device?

PC Laptop or Desktop


back to menu ↑

Final note

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.

Compsmag
Logo