Wednesday, September 28, 2022
Google search engine
HomePRODUCTIVITYHow to remove spaces in Excel

How to remove spaces in Excel

You will likely find many unneeded spaces when you import data from web pages and external sources. Here are some ways to get rid of them.

Excel, a powerful data analysis tool, has been around for over 30 years. Excel is an excellent tool to increase your productivity. These functions are also very useful when you need to fix minor errors like removing extra spaces or other issues.

Sometimes you may get additional spaces when you copy data from another source (web pages, text reports). The table becomes difficult to use and disorderly.

How to delete spaces in Excel

Excel offers three options to eliminate extra spaces. There are three options to remove extra spaces in Excel: the Trim formula, the Find & Replace option and the Trim space tool. Let’s look at the steps involved.

Use the Find and Replace option

This is the easiest method to delete Excel spaces, but it can only be used to delete spaces between words. You can reduce the number of leading and trailing spaces by using this method, but they won’t be completely removed.

  1. Choose one column or more columns to which you wish to remove extra spaces.
  2. Click on Replace, or click CTRL+H.
  3. Double-press the spacebar in the Search for box and once in the Replace With box.
  4. Click here All can be replaced Press OK Save your changes.
  5. Continue repeating step 4 until you get the message “We couldn’t find anything to substitute.”

RELATED: How to Use the Find and Replace Search Feature in Excel

Trim Formula

This Excel formula will remove spaces. Although it takes a bit of time, it is very efficient and best for leading and trailing spaces.

  1. Add the Helper Column at the end of your data and give it the name “Trim.”
  2. Enter the Excel formula to eliminate spaces in the first cell of column Helper. =TRIM (A2) .
  3. As needed, copy the formula to all other cells.
  4. Replace the column that contains clean data with the new one. To select all cells within the Helper column, press CTRL+C to copy them to your clipboard.
  5. Once you are done, select the first cell of the Helper column and press Shift+F10. Then, copy the contents by pressing CTRL+V.
  6. You can remove the Helper column and all spaces will be gone.

 

RELATED: How to Use the TRIM Function in Excel

Use the Trim Spaces tool

Trim Spaces is an Excel add-in that can remove extra spaces. It removes any unwanted spaces such as leading and trailing blanks, excess words between words, nonbreaking spaces, line breaks, and non-printing symbols. All this in just three clicks.

  1. Download and install Ultimate Suite for Excel to get started.
  2. To select the range from your table, press CTRL+A.
  3. Next, visit the Tab Ablebits Data That’s right next door to the Data tab . Click here Trim Spaces icon .
  4. The add-in’s pane will be visible on the left side. To clean up your table, select the checkboxes you need and click the Trim button.

 

How to remove spaces between numbers

These methods only work in cases where there are spaces between words. To remove extra spaces in Excel between numbers, Find & Replace works best again. Excel treats digits that are separated by spaces as text.

  1. Open the Replace dialog box.
  2. Enter a single space in Find the field, and leave the Replace tab blank.
  3. Next, click Replace all and to confirm your changes. Voila! You will see all empty spaces gone. You will get a clean spreadsheet.

It’s easy to remove extra spaces in Excel

Especially when working with large worksheets, the TRIM formula can take some time. We recommend the Trim Spaces tool. It’s the fastest and most efficient way to get your work done.

 

The Find & Replace option will do the trick for all your minor requirements. It doesn’t matter what option you choose to remove extra spaces from Excel.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments