Remove Text Keep Numbers Excel Formula

When working with data in Excel, it's common to encounter situations where you need to remove text from cells that contain a mix of numbers and text. This can be necessary for various reasons, such as cleaning up data for analysis or preparing it for use in formulas that only accept numerical values. Excel provides several ways to achieve this, including using formulas, the "Text to Columns" feature, and VBA scripts. Here, we'll focus on using Excel formulas to remove text from cells.

Understanding the Problem

Before diving into the solutions, let's understand the problem. Suppose you have a column of data where each cell contains a combination of numbers and text, and you want to extract just the numbers. For example, a cell might contain "123abc" or "xyz456", and you want to end up with just "123" or "456", respectively.

Using Formulas to Remove Text

Excel offers several formulas that can be used to remove text from cells. Here are a few approaches:

1. Using the VALUE and SUBSTITUTE Functions

If the text in your cells follows a predictable pattern, you might be able to use a combination of the VALUE and SUBSTITUTE functions to remove the text. However, this method can be cumbersome if the pattern is complex or variable.

2. Using the TEXTJOIN and FILTERXML Functions (Excel 2019 and Later)

For more complex scenarios, especially in newer versions of Excel, you can leverage the FILTERXML function in combination with TEXTJOIN to extract numbers from text.

=TEXTJOIN("",TRUE,FILTERXML(""&SUBSTITUTE(A1," ","")&"","//d[.*0=0]"))

This formula works by first replacing each space in the text with a closing and opening XML tag (</d><d>), effectively wrapping each character in an XML tag. It then uses FILTERXML to parse this XML string and select only the nodes (//d) that contain a digit ([*0=0]). However, this formula is primarily designed to filter out non-numeric characters based on their position or other characteristics, not directly to remove all text.

3. Using the REGEX Function (Excel 2019 and Later with Office 365)

Excel's REGEX function, available as part of the FILTERXML function or directly in some versions, allows for regular expression pattern matching. You can use it to extract numbers from a string.

=TEXTJOIN("",TRUE,FILTERXML(""&SUBSTITUTE(A1,"","")&"","//d[matches(., '\d+')]"))

However, this formula is more about filtering than directly extracting numbers and might require adjustments based on the specifics of your data.

4. Using VBA User-Defined Function (UDF)

For more flexibility and power, especially when dealing with complex patterns or large datasets, creating a VBA User-Defined Function can be the most effective approach. Here’s a simple example of a UDF that removes all non-numeric characters from a string:

Function ExtractNumbers(s As String) As String
    Dim i As Long
    For i = 1 To Len(s)
        If IsNumeric(Mid(s, i, 1)) Then
            ExtractNumbers = ExtractNumbers & Mid(s, i, 1)
        End If
    Next i
End Function

To use this UDF, follow these steps:

1. Open the Visual Basic Editor (VBE) by pressing Alt + F11 or navigating to Developer > Visual Basic in the ribbon.
2. In the VBE, insert a new module by right-clicking on any of the objects for your workbook in the "Project" window and choosing Insert > Module.
3. Copy and paste the UDF into the module window.
4. Save the workbook as a macro-enabled file (.xlsm).
5. You can now use =ExtractNumbers(A1) in any cell to extract numbers from the text in cell A1.