As an Excel user, you have likely encountered the problem of having unwanted spaces within your cell contents.
Whether it’s due to importing data from another source, copy-pasting, or simply typing too quickly, it’s important to know how to swiftly remove those blank spaces to avoid errors in your work.
In this article, we will provide you with 7 methods to quickly remove spaces within Excel cell contents.
Table of Contents
Method 1: Using the TRIM Function to Remove Blank Spaces in Excel
The TRIM function is a quick method to remove spaces from the beginning and end of a cell’s contents. It can be used to remove all extra spaces in a cell, leaving only a single space between words as shown in the example below:
Boost Your Website Speed!
If you want your website to run as fast as ours, consider trying Cloudways. Their powerful cloud infrastructure and optimized stack deliver exceptional performance. Free migration!
This function is particularly useful for correcting text data that contains irregular spacing, such as leading, and trailing.
Method 2: Using Find and Replace
Find and Replace is another great tool to remove spaces within cells. Follow the instructions below:
- Press Ctrl + F or go to the “Home” tab on the ribbon and select “Find & Replace”.
- In the “Find what” field, enter a single space ” “.
- Leave the “Replace with” field blank.
- Click “Replace All.”
Method 3: Using SUBSTITUTE Function to Remove blank Spaces in Excel
You may also use SUBSTITUTE Function to remove spaces cell-by-cell. Here’s an example that can be copied down:
=SUBSTITUTE(A1,” “,””)
The SUBSTITUTE function in Excel is a versatile and powerful tool designed to replace specific text in a cell with new text.
It works by searching for a specified substring within a given cell and then replacing all occurrences of this substring with a new string. The SUBSTITUTE function is ideal for tasks such as removing unwanted characters, correcting misspellings, or standardizing text formats.
Method 4: Using Excel’s TEXT to Columns feature
The “Text to Columns” feature in Excel is a powerful tool that can be used to split the contents of a cell into separate columns. Follow the instructions below:
- Select the range of cells that contains the data you want to split.
- Go to the “Data” tab on the ribbon, then click “Text to Columns”.
- Select “Delimited” and click “Next”.
- Uncheck all delimiter options such as tabs and commas, then check the “Space” option.
- Click “Finish.”
Method 5: Using VBA to remove spaces
For advanced users, a VBA macro can be created to remove spaces within Excel cell contents programmatically. Here’s an example code that can be run in a new module:
Sub RemoveSpaces()
Dim cell As Range
For Each cell In Selection
If InStr(cell.Value, " ") > 0 Then
cell.Value = Replace(cell.Value, " ", "")
End If
Next cell
End Sub
Method 6: Using Flash Fill to remove spaces
Flash Fill is a tool in Excel that automatically fills in values based on patterns it recognizes. This is an alternative to using Find and Replace or functions. Here’s how you can use it:
- Type the corrected text in a new column next to the original text. For example, if cell A2 has “Ea st” and you want to remove the spaces, type “East” next to it in cell B2.
- Excel should recognize the pattern after you type the first few cells. Press Ctrl + E to fill in the rest of the column.
Method 7: Using Power Query to remove spaces
Power Query is an add-in for Excel that allows you to import, transform, and clean data easily. It has a variety of options for removing spaces within Excel cell contents. Here’s how you can use it:
- Select the range of cells containing the data you want to transform.
- Go to the “Data” tab on the ribbon, then click “From Table/Range” to open the Power Query editor.
- Select the columns you want to transform by clicking on their column headers.
- Go to the “Transform” tab on the ribbon, then click “Replace Values”.
- In the “Replace Values” dialog box, in the “Value To Find” field, enter a single space ” “.
- Leave the “Replace With” field blank, then click “OK”.
Conclusion
In conclusion, there are several methods available to remove spaces within Excel cell contents. Whether you need to remove spaces cell-by-cell or on a larger scale, Excel offers multiple options to get the job done quickly and efficiently. Try out these methods for yourself to find the best one that works for you.
#N/A error in XLOOKUP: Understanding the concept and exploring 5 ways to fix it - howtouselinux
Monday 27th of November 2023
[…] Unintentional spaces before, after, or within the lookup values or in the cells of the lookup array can cause mismatches. […]