Buzz
In an Excel spreadsheet, data often contains unnecessary extra spaces within words or between numbers. To ensure that the data in your spreadsheet is clean and tidy, it's important to remove these unnecessary spaces. If you're unsure how to do this, read on for a helpful guide.
This article provides various methods for removing spaces in Excel. Follow along to learn more.
REMOVING SPACES BETWEEN WORDS
Utilizing the TRIM Function to Remove Spaces
The purpose of the TRIM function is to eliminate all spaces from text, leaving only a single space between words.
The syntax for the TRIM function: =TRIM(text)
For example, if you need to remove spaces between words in the 'First Name' column as shown below:
First, create an auxiliary column. Enter the formula into the first cell of the auxiliary column =TRIM(B7) where B7 is the cell you want to remove spaces from.
By doing so, you've removed spaces between words in the first cell. Copy the formula down to subsequent cells to remove spaces from the entire column.
Now, you can copy the data from the auxiliary column to the main column by selecting the auxiliary column and pressing Ctrl + C to copy. Then, place your mouse pointer in the first cell of the main column and right-click to select Value (V) under Paste Options.
Once done, you can delete the auxiliary column.
So, using the TRIM function helps you remove spaces between words and leading spaces. However, for large datasets, using TRIM can be time-consuming.
Utilizing Find & Replace
Using the Find & Replace feature will help you remove spaces between words faster. However, leading spaces will still remain and cannot be removed.
Step 1: Select the data range you want to remove spaces from, then press Ctrl + H to open the Find & Replace dialog box.
Step 2: In the Replace tab, press the space key twice in the Find what box, and press it once in the Replace With box (this will replace two consecutive spaces with one space). Then, click on Replace All to replace all instances, when prompted, click OK.
As there might be instances with more than two consecutive spaces, continue pressing Replace All until you see the message We couldn’t find anything to replace. Then, click OK and close the Find & Replace dialog.
By doing so, all spaces will be removed, leaving only one space between words.
REMOVING SPACES BETWEEN NUMBERS
Utilize the Substitute Function
The TRIM function helps you remove spaces between words and retains one space between words. However, if you want to completely remove spaces between numbers without leaving any space, you need to use the SUBSTITUTE function.
You only need to use the SUBSTITUTE function formula to remove spaces.
=SUBSTITUTE(C7;' ';'')
Where C7 represents the cell containing the number you want to remove all spaces from.
Copying the formula down to the remaining cells will remove all spaces between numbers.
Utilizing Find & Replace
Step 1: On the Excel spreadsheet, select the range of numbers where you want to remove spaces, then press the Ctrl + H key combination to open the Find & Replace dialog box.
Step 2: In the Find & Replace dialog, press the space key once in the Find What box and leave the Replace with box empty. Press Replace All to remove spaces between the numbers.
If there are multiple spaces between numbers, continue pressing Replace All until no spaces are found, then close Find & Replace. All selected ranges will have spaces removed.
Above are the methods to help you remove spaces between words and numbers in Excel. Hope this article will be helpful for you when working with Excel. Wish you success!
