3 Ways to Remove Leading, Trailing, & Extra Spaces in Excel

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
  4. Make sure to press it only once. This adds a space to the "Find what" field.
  5. 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.
    6. 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.).
  3. 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 .
  4. The cell should now look like this: =Substitute(A1, .
  5. 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," ", .
  6. . This time, there's NO space between the sets of quotes.
    • The formula should now look like this: =SUBSTITUTE(A1," ","" .
  7. 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 .
  8. 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.
    9. 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.
  3. This adds the cell address to the TRIM formula.
    • For example, if you click A1, your formula should now look like this: =TRIM(A1
  4. 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.
  5. 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.
    6. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

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

      You Might Also Like

      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      How to
      Make a Spreadsheet in Excel
      New to Excel? Here's Super Easy Tricks to Get You Started
      How to
      Link Sheets in Excel
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-au/office/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90#OfficeVersion=macOS
      2. ↑ https://support.microsoft.com/en-au/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332
      3. ↑ https://support.microsoft.com/en-gb/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 122,935 times.

      Is this article up to date?

      Advertisement