Removing Spaces from Certain Numbers in Excel Text Cells Containing Letters

To remove spaces from specific numbers within Excel text cells, use the REGEXREPLACE function in Google Sheets or a VBA macro in Excel. These methods target numeric sequences while preserving surrounding letters and punctuation, which is essential for cleaning historical data like wills without altering the overall structure of the text string.

Understanding the Data Challenge

When working with digitized historical documents, such as early modern wills from the Internet Archive, text extraction often introduces inconsistent spacing. You might encounter entries where numbers are separated by spaces (e.g., "10 00 pounds") while other parts of the text remain intact. Standard space removal functions like SUBSTITUTE(A1, " ", "") are too aggressive because they strip all spaces, merging words together and rendering the text unreadable. The goal is to identify patterns where digits are followed by a space and another digit, then remove that internal space while leaving single-digit numbers and alphabetic characters untouched. This distinction is critical when dealing with large datasets where manual correction is impossible. You need a programmatic approach that recognizes numerical boundaries. For instance, you want to convert "Item 5 00" to "Item 500" but leave "Item 5" alone. This requires looking ahead in the string to see if a space is sandwiched between two numbers.

Why Simple Functions Fail

Many users initially try using the TRIM function. However, TRIM only removes leading and trailing spaces and collapses multiple internal spaces into one. It does not remove spaces between digits. Similarly, SUBSTITUTE replaces every instance of a space character. If your cell contains "John Smith 10 00", applying SUBSTITUTE results in "JohnSmith1000", which destroys the semantic meaning of the name. Therefore, a more sophisticated pattern-matching tool is required.

Solution for Google Sheets: REGEXREPLACE

Google Sheets provides a powerful built-in function called REGEXREPLACE that allows you to search for complex patterns using regular expressions. This is the most efficient method if you have access to Google Sheets. The syntax is =REGEXREPLACE(text, regular_expression, replacement). To remove spaces between digits, you need a regular expression that looks for a digit (\d), followed by a space ( ), followed by another digit (\d). The replacement should be an empty string, effectively deleting the space. The formula is:

=REGEXREPLACE(A1, "(\\d) (\\d)", "$1$2")

Here, (\\d) captures a digit. The parentheses create a capture group. The space matches the literal space character. The second (\\d) captures the next digit. In the replacement field, $1$2 inserts the first captured digit immediately followed by the second, removing the space in between. Note that backslashes must be escaped with another backslash in Google Sheets formulas. If you also need to handle cases where there might be multiple spaces between digits, you can modify the regex to (\\d) + (\\d). This ensures robustness against irregular formatting. For related text manipulation tasks, you might also find it useful to learn how to Remove Numbers from Text in Excel, which complements this technique by isolating alphanumeric components.

Solution for Microsoft Excel: VBA Macro

Excel does not have a native REGEXREPLACE function in its standard interface, although newer versions may introduce it via dynamic arrays. For now, the most reliable method is using Visual Basic for Applications (VBA). This involves creating a User Defined Function (UDF) that applies regular expressions to your cells. First, press Alt + F11 to open the VBA editor. Insert a new module and paste the following code. This code correctly declares variables and uses the RegExp object to perform the replacement.

Function RemoveSpacesBetweenNumbers(rng As Range) As String
    Dim regEx As Object
    Set regEx = CreateObject("VBScript.RegExp")
    
    With regEx
        .Global = True
        .IgnoreCase = True
        .Pattern = "(\d) (\d)"
        RemoveSpacesBetweenNumbers = .Replace(rng.Value, "$1$2")
    End With
    
    Set regEx = Nothing
End Function

Once saved, you can use this function in your worksheet just like any other formula: =RemoveSpacesBetweenNumbers(A1). This approach is highly flexible and can be modified to handle other patterns, such as removing spaces after commas or before periods. It is important to ensure that the VBA script references the correct library. In older Excel versions, you might need to enable the "Microsoft VBScript Regular Expressions 5.5" reference under Tools > References in the VBA editor. However, the CreateObject method used above typically works without explicit reference settings, making it portable across different Excel installations. For those managing complex workflows, understanding how to Move Rows Based on Matching Cells Across Two Worksheets can help automate the process of cleaning and organizing your historical data after the text has been standardized.

Alternative Manual Approaches

If you cannot use scripts or advanced functions, you might consider a multi-step manual process. First, use the Find and Replace feature (Ctrl + H) to replace common number-space-number patterns. For example, replace " 0 " with "0". Repeat this for " 1 " through " 9 ". This is tedious and error-prone but requires no coding. Another alternative is using Power Query in Excel Pro. You can load the data into Power Query, add a custom column with a M formula that uses Regex.Replace, and then load the cleaned data back into Excel. This is ideal for repeatable data cleaning pipelines.

Frequently Asked Questions

Can I use this method on entire columns at once?

In Google Sheets, you can apply the REGEXREPLACE formula to a range by dragging it down or using an array formula wrapped in {}. In Excel, the VBA UDF can be dragged down a column just like a standard function. For bulk processing in Excel, consider using Power Query to apply the transformation to the entire table source automatically.

What if the numbers have decimal points?

The current regex (\d) (\d) will treat the dot as a non-digit separator, so "10. 5" would become "10.5" correctly if the space is between the digit and the dot? No, it targets digit-space-digit. To handle decimals like "10 .5", you might need a more complex pattern like (\d+) \.? ?(\d+). Test your specific format carefully before applying to large datasets.

Does this work for negative numbers?

Standard patterns do not include the minus sign. If you have "-10 00", the regex won't match the space after the minus unless you adjust the pattern to include optional signs. You would need to expand the regex to ([-]?\d) (\d) to capture negative signs as part of the first number group.

How do I revert changes if I make a mistake?

If you use formulas in new columns, simply delete those columns to revert. If you use VBA to overwrite original data, always save a backup copy of your workbook before running macros. Excel’s undo history is limited for VBA operations, so prevention is key.

Are there any performance issues with large datasets?

VBA functions can slow down Excel if applied to tens of thousands of rows because they execute row-by-row. Google Sheets handles REGEXREPLACE more efficiently in cloud environments. For very large Excel files, Power Query is significantly faster than VBA UDFs as it processes data in batches.