Remove Spaces Between Numbers in Excel Easily

Dealing with numbers in Excel can often lead to formatting issues, especially when there are unintended spaces between digits or at the start or end of numeric values. These spaces can cause problems with sorting, calculations, or data import processes. In this detailed guide, we'll explore multiple methods to remove spaces between numbers in Excel, ensuring your data is clean, uniform, and ready for analysis or further processing.

Understanding Spaces in Excel

Before diving into the methods, it’s essential to understand how Excel handles spaces:

• Leading spaces: Spaces at the beginning of the text.
• Trailing spaces: Spaces at the end of the text.
• Internal spaces: Spaces within a text string, particularly between numbers.

Spaces can be inserted unintentionally through data entry, copy-pasting from other sources, or during data exports/imports.

Method 1: Using TRIM Function

The TRIM function in Excel is primarily designed to remove extra spaces from text, but it can also be handy for numbers:

=TRIM(A1)

Here, A1 is the cell containing the number with unwanted spaces. However, TRIM will remove leading and trailing spaces but not necessarily all internal spaces if they are consistent.

✨ Note: The TRIM function in Excel does not remove spaces within words or numbers if there's only one space between them.

Method 2: SUBSTITUTE Function for Specific Spaces

If the spaces are consistently within numbers, you can use the SUBSTITUTE function to replace these spaces:

=SUBSTITUTE(A1," ","")

This formula will replace all spaces in the text with an empty string, effectively removing them.

Method 3: Text to Columns

Another versatile approach is using the “Text to Columns” feature:

1. Select the column with the numbers.
2. Go to Data > Text to Columns.
3. Choose ‘Delimited’ and click Next.
4. Select ‘Space’ as the delimiter, then Finish.
5. Use the formula bar to concatenate the resulting columns:

=A1 & B1 & C1...

Here, A1, B1, C1, etc., are columns where the numbers were split.

Method 4: VBA Script for Advanced Space Removal

For a more automated and sophisticated approach, you can use VBA:

Sub RemoveSpacesInNumbers()
    Dim cell As Range
    For Each cell In Selection
        cell.Value = Replace(cell.Value, " ", "")
    Next cell
End Sub

This script will remove all spaces within the selected range. Here's how to run it:

• Press Alt + F11 to open the VBA editor.
• Insert a new module and paste the code.
• Close the VBA editor and select your data.
• Press Alt + F8, select "RemoveSpacesInNumbers", and run.

Method 5: Power Query for Data Transformation

Power Query is Excel's ETL (Extract, Transform, Load) tool, perfect for dealing with data preparation:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Trimmed Text" = Table.TransformColumns(Source, {{"Column1", Text.Trim, type text}}),
    #"Remove Spaces" = Table.TransformColumns(#"Trimmed Text", {{"Column1", each Text.From(Text.Combine(Text.Split(_, " ")))}}
in
    #"Remove Spaces"

Here's how to use Power Query:

• Select your data and load it into Power Query (Data > From Table/Range).
• Transform the data with the above script or manually by replacing steps for splitting and combining text.
• Click Close & Load to return the clean data to Excel.

Things to Keep in Mind

• Data Backup: Always save a copy of your data before performing cleaning operations.
• Data Validation: Ensure that the removal of spaces does not change the meaning of your data.
• Consistency: Check your entire dataset for consistency after removing spaces to avoid any calculation or sorting errors.
• Macro Security: If using VBA, enable macros and ensure your system's security settings allow running scripts.

By implementing these methods, you can efficiently remove spaces from numbers in Excel, ensuring data consistency and accuracy for further analysis or processing. The selection of a method depends on the complexity of your dataset, your familiarity with Excel, and the context in which the data will be used. Whether through simple formulas, data transformation, or scripting, you now have the tools to clean up your numeric data with ease.

What are the different types of spaces in Excel?

+

Excel distinguishes between leading, trailing, and internal spaces within cells:

• Leading Spaces: Spaces at the beginning of the text.
• Trailing Spaces: Spaces at the end of the text.
• Internal Spaces: Spaces between characters or numbers within a cell.

Can Excel formulas remove all types of spaces between numbers?

+

While the TRIM function effectively removes leading and trailing spaces, it does not remove spaces within words or numbers if there’s only one space between them. For removing all spaces, including internal ones, the SUBSTITUTE function or VBA scripting is necessary.

How can I ensure my data doesn’t change when removing spaces?

+

To ensure data integrity, perform the following steps:

• Always keep a backup of your original data before making changes.
• Verify the results of your space removal operation by visually inspecting or using data validation tools in Excel.
• Ensure that any calculation or sorting you perform after space removal produces expected results.

Is it possible to remove spaces from numbers in Excel without using VBA?

+

Yes, it is possible to remove spaces from numbers in Excel without VBA:

• Using the TRIM function for leading and trailing spaces.
• Employing the SUBSTITUTE function to remove all spaces.
• Leveraging Excel’s “Text to Columns” feature to split and then join text strings.
• Using Power Query, which doesn’t require VBA but offers advanced data transformation capabilities.