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
-
How 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. -
How to Remove Spaces Between Characters and Numbers in Excel Picture 2
Click the Edit menu. -
How to Remove Spaces Between Characters and Numbers in Excel Picture 3
Select Find. -
How to Remove Spaces Between Characters and Numbers in Excel Picture 4
Select Replace…. A dialog box will appear. -
How to Remove Spaces Between Characters and Numbers in Excel Picture 5
Click the box beneath 'Find What.' -
How 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. -
How 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. -
How to Remove Spaces Between Characters and Numbers in Excel Picture 8
Click Click OK.
Using the Substitute Function
-
How 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.- 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).
-
How to Remove Spaces Between Characters and Numbers in Excel Picture 10
Type =Substitute.[1] -
How 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.- If you clicked cell C2, the formula should now look like this: =Substitute(C2 .
-
How to Remove Spaces Between Characters and Numbers in Excel Picture 12
Type , (a comma). The cell should now look like this: =Substitute(C2,. -
How 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.- The formula should now look like this: =Substitute(C2," ",.
-
How to Remove Spaces Between Characters and Numbers in Excel Picture 14
Type ''). This time, there's NO space between the sets of quotes.- The formula should now look like this: =Substitute(C2,' ','').
-
How 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.- For example, if C2 said w ww . wikih ow .com, your new cell will say www.wikihow.com.
-
How 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. -
How 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. -
How 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
You should read it
- How to separate text strings by commas or spaces in Excel
- How to name, comment and protect cells in Excel
- How to Move Columns in Excel
- How to Make a Spreadsheet in Excel
- How to name a cell or Excel data area
- How to Copy Formulas in Excel
- How to Compare Data in Excel
- How to remove extra spaces and spaces in Word
- How to Use Spaces on Mac OS X
- How to remove spaces in Excel
- How to create spaces between letters and cell borders in Excel
- How to Add a Column in a Pivot Table