Extract First Number From String In Excel

Extracting the first number from a string in Excel can be a useful skill, especially when working with data that contains a mix of numbers and text. Whether you're dealing with product codes, phone numbers, or any other type of data that includes numbers embedded within text, Excel provides several functions and methods to help you extract these numbers. Below, we'll explore how to extract the first number from a string using Excel formulas and functions.

Using the LEFT, MID, and FIND Functions

If the first number is always in the same position, or if it's preceded by a specific character, you might be able to use a combination of Excel's text manipulation functions like LEFT, MID, and FIND. However, this method is less flexible if the position of the number varies.

Using Regular Expressions (RegEx) with VBA

A more robust method involves using Regular Expressions (RegEx) in a VBA macro. RegEx is powerful for pattern matching and can be used to find and extract the first number from a string.

1. Enable the Visual Basic Editor: Press Alt + F11 or navigate to Developer > Visual Basic in the ribbon.
2. Add a Reference to RegEx:
   • In the Visual Basic Editor, go to Tools > References.
   • Check if "Microsoft VBScript Regular Expressions 1.0" is listed. If not, browse to find it and check it.
3. Create a Function:
   • Insert a new module by right-clicking on any of the objects in the Project Explorer and choosing Insert > Module.
   • Paste the following function:

Function ExtractFirstNumber(str As String) As Variant
    Dim regEx As Object
    Set regEx = CreateObject("VBScript.RegExp")
    
    With regEx
       .Pattern = "\d+"
       .Execute str
    End With
    
    If regEx.Test(str) Then
        ExtractFirstNumber = regEx.Execute(str)(0).Value
    Else
        ExtractFirstNumber = "No number found"
    End If
    
    Set regEx = Nothing
End Function

4. Use the Function: You can now use =ExtractFirstNumber(A1) in your worksheet, assuming the string you want to extract the number from is in cell A1.

Using Power Query (M Language)

For those using Excel 2010 or later, especially if you have large datasets or prefer a non-VBA approach, Power Query can be a powerful tool.

1. Load Your Data into Power Query:

   • Go to Data > From Table/Range.
   • Select your table.

2. Add a Custom Column:

   • In the Power Query Editor, go to Add Column > Custom Column.
   • Use the formula: Text.Before(Text.After([YourColumn], {0}?? ""), {"0".."9"}{1}) and adjust [YourColumn] to your actual column name.
   • This formula tries to find the first digit by taking the text after the first non-digit character. It's a workaround since Power Query's M language doesn't natively support RegEx.

3. Load Your Data:

   • Click Close & Load to return the data to your worksheet.

Conclusion

Each of these methods has its advantages and may be more suitable depending on the specifics of your data and your comfort with VBA or Power Query. The key to efficiently extracting the first number from a string in Excel is finding the right balance between flexibility and the complexity of your data.