PDF download Download Article
Use Find & Replace and simple formulas to clean up your Excel data
PDF download Download Article

Sometimes importing data into Microsoft Excel from an external source can add extra spaces to your cell values. This can really mess with your formulas! If your cells are plagued with random spaces between numbers, leading and trailing spaces, and multiple spaces between words, don't worry—there are easy ways to fix your data. This wikiHow article will teach you how to remove spaces between numbers and characters, as well as from before and after values, in your Excel spreadsheet.

Quick Guide

  1. Press Ctrl + H to bring up the "Find & Replace" function.
  2. Press Spacebar in the "Find what" field.
  3. Click "Replace all."
  4. Select "Okay."
Method 1
Method 1 of 3:

Using Find and Replace

PDF download Download Article
  1. For example, if you want to remove spaces from C2 through C30, highlight those cells.
    • This method removes any extra spaces between numbers, words, and other characters. To eliminate leading and trailing spaces, see Using the TRIM Function .
    • This method will not delete extra space at the beginning or end of the value, just spaces between characters.
  2. Press Ctrl + H to open Find and Replace . This quick keyboard shortcut works on both Windows and macOS. [1]
    Advertisement
  3. Make sure to press it only once. This adds a space to the "Find what" field.
  4. It's the second button at the bottom of the window. This searches for all spaces between numbers and other characters in the selected range and deletes them.
    • A pop-up will show how many spaces were removed. Click "'OK'" on the pop-up to return to your worksheet.
  5. Advertisement
Method 2
Method 2 of 3:

Using the SUBSTITUTE Function

PDF download Download Article
  1. To remove extra space between numbers, characters, or words in a cell, you can use the SUBSTITUTE function . Click a cell on the same row as the first cell in the column with the extra spaces.
    • For example, if you want to remove spaces from column A, and A's first row of data is in row 1 (A1), click the first cell in your blank column (e.g., B1, C1, D1, etc.).
  2. For example, if the first cell in the column that has extra spaces is A1, click it now.
    • If you clicked cell A1, the formula should now look like this: =SUBSTITUTE(A1 .
  3. The cell should now look like this: =Substitute(A1, .
  4. That's a double quotation mark, a space, another double quotation mark, and then a comma. There's a space between the two sets of double quotes—this is important.
    • The formula should now look like this: =SUBSTITUTE(A1," ", .
  5. . This time, there's NO space between the sets of quotes.
    • The formula should now look like this: =SUBSTITUTE(A1," ","" .
  6. In the end, the formula should now read =SUBSTITUTE(A1," ","") . You will now see the contents of the selected cell (A1, in this example) without spaces in the new column. [2]
    • For example, if C2 said w ww . wikih ow .com , your new cell will say www.wikihow.com .
  7. The easiest way to do this is to use Autofill:
    • Click the cell into which you typed the formula to select it.
    • Double-click the square at the bottom-right corner of the box.
    • Alternatively, drag the square down until you've reached the bottom of the column.
    • You can now easily copy the new column without spaces and paste it into the original column. Your new space-free data is now in place.
  8. Advertisement
Method 3
Method 3 of 3:

Using the TRIM Function

PDF download Download Article
  1. The TRIM function removes extra spaces from the beginning and end of a cell's value. It will also remove extra space (except for single spaces) between words. [3] Click the first cell on the same row as the first line of data in the column with the extra spaces.
  2. This adds the cell address to the TRIM formula.
    • For example, if you click A1, your formula should now look like this: =TRIM(A1
  3. Your ending formula should look like this: =TRIM(A1) . This removes any excess spaces at the beginning or end of the selected cell. It will also remove extra space between words "except" for single spaces.
  4. The easiest way to do this is to use Autofill:
    • Click the cell into which you typed the formula to select it.
    • Double-click the square at the bottom-right corner of the box.
    • Alternatively, drag the square down until you've reached the bottom of the column.
    • You can now easily copy the new column without extra space and paste it into the original column. Your new space-free data is now in place.
  5. Advertisement

Expert Q&A

Ask a Question
      Advertisement

      Video

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Thanks for submitting a tip for review!
      Advertisement

      About This Article

      Thanks to all authors for creating a page that has been read 122,955 times.

      Is this article up to date?

      Advertisement