Download Article
Use Find & Replace and simple formulas to clean up your Excel data
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.
Things You Should Know
- To remove spaces between numbers and words, press Ctrl + H, press the Spacebar in the "Find what" field, then click "Replace all."
- You can also use the SUBSTITUTE function to remove extra space between characters.
- To remove extra space from the beginning or end of a value, use the TRIM function.
Steps
-
Highlight the range where you want to remove all spaces. 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.
-
Press Ctrl + H to open Find and Replace . This quick keyboard shortcut works on both Windows and macOS. [1] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to sourceAdvertisement
-
Click the box next to "Find What."
-
Press the space bar on the keyboard. Make sure to press it only once. This adds a space to the "Find what" field.
-
Click Replace All . 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.
Advertisement
-
Click the top cell in a blank column. 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.).
-
Type =SUBSTITUTE into the cell.
-
Click the first cell in the column with spaces. 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 .
-
Type , (a comma). The cell should now look like this: =Substitute(A1, .
-
Type " ", . 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," ", .
-
Type "" . This time, there's NO space between the sets of quotes.
- The formula should now look like this: =SUBSTITUTE(A1," ","" .
-
Type a closed parenthesis ) and press ↵ Enter or ⏎ Return . 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] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- For example, if C2 said w ww . wikih ow .com , your new cell will say www.wikihow.com .
-
Apply the changes to the entire column. 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.
Advertisement
-
Click the top cell in a blank column. 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] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source Click the first cell on the same row as the first line of data in the column with the extra spaces.
-
Type =TRIM( into the cell.
-
Click the first cell that contains extra spaces. This adds the cell address to the TRIM formula.
- For example, if you click A1, your formula should now look like this: =TRIM(A1
-
Type a closed parenthesis ) and press ↵ Enter or Return . 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.
-
Apply the changes to the entire column. 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.
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
- If the capitalization in your data is off, you can use the UPPERCASE, PROPER, and LOWERCASE functions to clean it up.Thanks
-
Thanks
Submit a Tip
All tip submissions are carefully reviewed before being published
Thanks for submitting a tip for review!
Advertisement
References
- ↑ https://support.microsoft.com/en-au/office/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90#OfficeVersion=macOS
- ↑ https://support.microsoft.com/en-au/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332
- ↑ https://support.microsoft.com/en-gb/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9
About This Article
Thanks to all authors for creating a page that has been read 108,224 times.
Advertisement
