Last week, I wrote an article on how to replace spaces with a dash or underscore in Excel but what if you simply want to remove spaces from words or numbers in a spreadsheet? That’s what we’re going to explore in this article.
You’re in complete control of how data is formatted when you create a spreadsheet from scratch, however, sometimes you’ll need to import existing data from another source.
You can import (or copy and paste) data from text files, PDF documents, or web pages. While being able to use text and numbers from various files is convenient, there are no guarantees that the data will be formatted correctly or consistently.
Imported data may have leading or trailing spaces, or the data could contain additional spaces that must be removed before working with the text or performing calculations.
Here are 3 ways to remove spaces from words or numbers in Excel.
1) Use Find and Replace to Remove Spaces from Text in Excel
The Find and Replace dialog box is a quick and convenient way to remove spaces from text in Excel. This dialog box lets you edit the contents of cells directly, removing all spaces with no additional steps required.
The Find and Replace feature won’t be the ideal choice in every situation but, as long as your goal is to remove all spaces from a range of cells, it’s definitely up to the task!
Let’s look at an example.
The spreadsheet (below) contains several numbers with embedded spaces. Excel treats numbers with spaces as text, so you won’t be able to perform calculations using these values until you remove the spaces.
Here is how to get rid of spaces using Find and Replace.
- Select the range of cells containing spaces.
- Press Ctrl + H to display the Find and Replace dialog box with the Replace tab selected.
- Click in the Find what: box and press the Space bar once.
- Verify that the Replace with: box is empty.
- Click Replace All.
This screenshot shows the same spreadsheet with all of the spaces removed. Notice that the values have been updated in place.
2) Use the TRIM Function to Remove Spaces from Text in Excel
The TRIM function removes all spaces from text except for single spaces between words. This one function removes leading spaces, trailing spaces, and additional spaces between words.
In the example (below), I added spaces before, after, and between Karen Connor’s first and last names.
To remove the additional spaces:
- Click in cell C2 and type =TRIM(A2) then press Enter.
- Copy the formula to cells C3:C5.
Use the SUBSTITUTE Function to Remove Spaces from Text in Excel
The SUSBSTITUTE function can be used to replace one string of characters with another. It can easily remove all spaces from text in Excel.
SUBSTITUTE Function Syntax
Here is the syntax of the SUBSTITUTE function:
=SUBSTITUTE(Text,Old_text,New_text,[Instance_num])
- Text is the cell address containing the text you want to update.
- Old_text is the existing character (a space).
- New_text is the revised text (an underscore).
- Instance_num identifies which instance of Old_text should be updated. If this parameter is omitted, all instances of Old_text are updated.
SUBSTITUTE Function Example
In the example (below), I clicked in cell B2 and typed =SUBSTITUTE(A2, “ “, “”) and pressed Enter. After entering the formula, you can copy it to the rest of the cells in column B.
Use Paste Values to Replace the Original Data
The Find and Replace dialog box updates the contents of cells “in place”. Once you remove the spaces, they’re gone. Your original data no longer has spaces.
The TRIM and SUBSTITUTE functions remove spaces from text or numbers, but your original data is unchanged.
In the example (below), I double-clicked cell C3. Like every instance of Karen Conners’ name in column C, you’re seeing the results of a formula.
If you need to manipulate data (not formula results), you’ll need to overwrite the original data with the formula results’ values.
Don’t worry. It’s not difficult.
By default, when you copy and paste data in Excel, everything in the source cell (or range of cells) is pasted into the destination cells – including formulas, formatting, validation rules, and comments.
If you simply copied the cells from column C (data with the spaces removed) and pasted them into column A, you would overwrite your original data with a bunch of formulas.
To overwrite cells in column A with new data, you need to leverage paste options.
Here is what you would do.
- Select the range C2:C5.
- Click the Copy button in the Home > Clipboard group.
- Select cell A2.
- Click the down-arrow under the Paste button and select Values.
Consistent Data is Key
Participants in my Excel courses often tell me that they need to clean up data imported from other sources before they can analyze it using various calculations, charts, and PivotTables.
Hopefully the methods outlined in this article will save you some time and make life easier!
