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.
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).
How to Remove Spaces Between Characters and Numbers in Excel Picture 10

Type =Substitute.^{[1] X Research source}
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.
1. 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.
1. 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.
1. 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.
1. 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.

column cell spaces click

Lesley Montoya

Update 08 June 2020

You should read it

Maybe you are interested