HomeApplicationOffice information

How to Remove Spaces Between Characters and Numbers in Excel

This wikiHow teaches you how to remove unwanted spaces from cells in an Excel spreadsheet. Highlight the range in which you want to remove all spaces. For example, if you want to remove spaces from C2 through C30, highlight those cells.

Using Find and Replace

  1. How to Remove Spaces Between Characters and Numbers in Excel Picture 1How to Remove Spaces Between Characters and Numbers in Excel Picture 1
    Highlight the range in which you want to remove all spaces. For example, if you want to remove spaces from C2 through C30, highlight those cells.
  2. How to Remove Spaces Between Characters and Numbers in Excel Picture 2How to Remove Spaces Between Characters and Numbers in Excel Picture 2
    Click the Edit menu.
  3. How to Remove Spaces Between Characters and Numbers in Excel Picture 3How to Remove Spaces Between Characters and Numbers in Excel Picture 3
    Select Find.
  4. How to Remove Spaces Between Characters and Numbers in Excel Picture 4How to Remove Spaces Between Characters and Numbers in Excel Picture 4
    Select Replace…. A dialog box will appear.
  5. How to Remove Spaces Between Characters and Numbers in Excel Picture 5How to Remove Spaces Between Characters and Numbers in Excel Picture 5
    Click the box beneath 'Find What.'
  6. How to Remove Spaces Between Characters and Numbers in Excel Picture 6How to Remove Spaces Between Characters and Numbers in Excel Picture 6
    Press the space bar on the keyboard. Make sure to press it only once.
  7. How to Remove Spaces Between Characters and Numbers in Excel Picture 7How to Remove Spaces Between Characters and Numbers in Excel Picture 7
    Click Replace All. It's the second button at the bottom of the window. The spaces are now removed from the selected cells. A pop-up will appear, letting you know how many spaces were removed.
  8. How to Remove Spaces Between Characters and Numbers in Excel Picture 8How to Remove Spaces Between Characters and Numbers in Excel Picture 8
    Click Click OK.

Using the Substitute Function

  1. How to Remove Spaces Between Characters and Numbers in Excel Picture 9How to Remove Spaces Between Characters and Numbers in Excel Picture 9
    Click the top cell in a blank column. The cell must be on the same row as the first line of data in the column with the spaces.
    1. For example, if you want to remove spaces from column C, and C's first row of data is in row 2 (C2), click the second cell in your blank column (e.g. E2, F2, G2, etc).
  2. How to Remove Spaces Between Characters and Numbers in Excel Picture 10How to Remove Spaces Between Characters and Numbers in Excel Picture 10
    Type =Substitute.[1]
  3. How to Remove Spaces Between Characters and Numbers in Excel Picture 11How to Remove Spaces Between Characters and Numbers in Excel Picture 11
    Click the first cell in the column with spaces. For example, if you want to remove all of the spaces from the C column, click the first box (e.g. C2) in the column that isn't the title.
    1. If you clicked cell C2, the formula should now look like this: =Substitute(C2 .
  4. How to Remove Spaces Between Characters and Numbers in Excel Picture 12How to Remove Spaces Between Characters and Numbers in Excel Picture 12
    Type , (a comma). The cell should now look like this: =Substitute(C2,.
  5. How to Remove Spaces Between Characters and Numbers in Excel Picture 13How to Remove Spaces Between Characters and Numbers in Excel Picture 13
    Type " ",. There's a space between the two sets of quotes—this is important.
    1. The formula should now look like this: =Substitute(C2," ",.
  6. How to Remove Spaces Between Characters and Numbers in Excel Picture 14How to Remove Spaces Between Characters and Numbers in Excel Picture 14
    Type ''). This time, there's NO space between the sets of quotes.
    1. The formula should now look like this: =Substitute(C2,' ','').
  7. How to Remove Spaces Between Characters and Numbers in Excel Picture 15How to Remove Spaces Between Characters and Numbers in Excel Picture 15
    Press ↵ Enter or ⏎ Return. You will now see the contents of the selected cell (C2, in this example) without spaces in the new column.
    1. For example, if C2 said w ww . wikih ow .com, your new cell will say www.wikihow.com.
  8. How to Remove Spaces Between Characters and Numbers in Excel Picture 16How to Remove Spaces Between Characters and Numbers in Excel Picture 16
    Click the cell with the formula you typed. The cell should now be highlighted.
  9. How to Remove Spaces Between Characters and Numbers in Excel Picture 17How to Remove Spaces Between Characters and Numbers in Excel Picture 17
    Drag the fill handle down over the cells you want to fill. The data from each corresponding cell will now appear in your new column without spaces.
  10. How to Remove Spaces Between Characters and Numbers in Excel Picture 18How to Remove Spaces Between Characters and Numbers in Excel Picture 18
    Copy the data from the new column to the original column. Your new space-free data is now in place.
4.3 ★ | 8 Vote
columncellspacesclick

You should read it

May be interested

Office information