Easy Ways On How To Remove Space In Excel

Thinking about how to remove space in Excel? There are many ways to do this easily. Let us learn about them in this article.

How often does it happen to you that you are searching for particular data in your Excel sheet and have tried every way to find it but have been unsuccessful? 

Well, this usually happens when there are some extra spaces before or after any data you have entered, making it difficult for you to search for them. This is most common when you copy and paste some information. These errors are easily missed and hence this problem occurs. 

There are many ways you can avoid this problem, there several ways to remove space in excel and this is how you can do it too.

Easy Ways On How To Remove Space In Excel

Use the Trim Formula to Remove Spaces In Excel

With the help of the TRIM function, all spaces except a single space between words can be eliminated. Let us find out how this works.

1. Add the helper column to the end of your data. You can name it “Trim”.

2. In the first cell of the helper column (C2), enter the formula to trim excess spaces =TRIM(A2)

3. Copy the formula across the other cells in the column. You can use some tips by entering the same formula into all selected cells.

4. Replace the original column with the cleaned data. Select all cells in the helper column and press Ctrl + C to copy data.

Pick the first cell in the original column and press Shift + F10 or the menu button. Then just press V.

5. Remove the helper column and done. You have now deleted all excess blanks with the help of the TRIM formula. 

Using A Formula To Remove Space In Excel

You may need to delete all blanks, like in a formula chain. You can do this by creating a helper column and entering the formula: 

=SUBSTITUTE(A1,” “,””)

Here A1 is the first cell of the column with numbers or words where all spaces must be deleted.

Then follow the steps from the above-mentioned step.

Using Find & Replace to remove space In Excel between words

This allows for deleting only excess spaces between words. Leading and trailing spaces will also be trimmed to 1, but will not be removed.

1. Select one or several columns with the data to delete spaces between words.

2. Press Ctrl + H for the “Find and Replace” dialogue box.

3. Press the Space bar twice in the Find What field and once in Replace With

4. Click on the “Replace all” button, and then press Ok to close the Excel confirmation dialogue.

5. Repeat step 4 until you see the message “We couldn’t find anything to replace.”

Using the Trim Spaces Tool

This is the best option to remove extra spaces in Excel because it clears all unwanted spaces such as excess blanks between words, leading and trailing spaces, non-breaking spaces, non-printing symbols, and line breaks, and that too in just three clicks.

1. Download and install a trial version of Ultimate Suite for Excel.

2. Select the range in your table, or press CTRL+A to select all cells.

3. Go to the Ablebits Data tab which is next to the Data tab. Click on the Trim Spaces icon.

4. You’ll see the add-in pane open on the left side of your worksheet. Select the needed checkboxes and click the Trim button to have a perfectly cleaned table.

It is an easy job to remove extra spaces in an Excel sheet, especially with the help of the TRIM method. This is great especially when you are working in large sheets with vast data. It will make your work more efficient and error-free. 

How Lio can Help You?

Lio is a great platform that can help entrepreneurs, homemakers, students, businessmen, managers, shop owners, and many others. This mobile application helps to organize business data and present them in an eye-catching manner. 

Lio is a great platform for small business owners and can track a wholesome record of employee information for better employee management, customer data, etc. You can handle those data with ease.

If you want to be a professional, then you must save time, you need to learn to arrange all the business strategies in one place. In that case, Lio can be your partner.

Entrepreneurs can also allow multiple authorized users of their office to access the information from various locations within minutes.

Lio is definitely for the win and using it for your business is only going to make your journey smooth and easy to track.

Step 1: Select the Language you want to work on. Lio on Android

Step 2: Create your account using your Phone Number or Email Id.

Verify the OTP and you are good to go.

Step 3: Select a template to which you want to add your data.

Add your Data with our Free Cloud Storage.

Step 4: All Done? Share and Collaborate with your contacts.