Extracting Numbers from a Text String

Bob inherited a workbook that includes several hundred text values in column C. Many of these include, within the text, a number that is surrounded by parentheses, such as "Falcon (123) Vector". Bob needs to extract those numbers. The number can have different numbers of digits, it can be in different places in the text, and not all cells may include a number or parentheses. Bob wonders if this can be done with a formula, or if a macro would work best.

This can be done with a formula, but the formula you choose will need to depend on the characteristics of the data in column C. Assuming your data begins in cell C2, a simple approach is to use the TEXTBEFORE and TEXTAFTER functions, available in Excel 2024 and Microsoft 365:

=TEXTBEFORE(TEXTAFTER(C2,"("),")")

This returns a text string composed of the numbers within the parentheses. If you want it to be a numeric value instead, then you can wrap it in a VALUE function:

=VALUE(TEXTBEFORE(TEXTAFTER(C2,"("),")"))

The value of this approach diminishes if your data includes cells that either contain no parentheses; just an opening or closing parenthesis; or that do contain parentheses, but no numbers between those parentheses. In that case, you may want to wrap the formula in an IFERROR function:

=IFERROR(VALUE(TEXTBEFORE(TEXTAFTER(C2,"("),")")),"")

This results in the numeric value being returned unless one of the conditions already described occurs, in which case an empty string ("") is returned. If you prefer that something else be returned, such as the text "no number," then you can place the desired text within the final empty string ("").

If you are using Microsoft 365, you might think that the best approach is to use the new REGEXTRACT or REGEXREPLACE functions, in this manner:

=IFERROR(VALUE(REGEXEXTRACT(C2,"\d+")),"")
=IFERROR(VALUE(REGEXREPLACE(C2,"\D+","")),"")

These work just as well as the TEXTBEFORE and TEXTAFTER approach, with a couple of exceptions. First, they mess up if there is a missing beginning or ending parenthesis. (For instance, "Falcon (123 Vector" or "Falcon 123) Vector" still return the value 123 but shouldn't.) A better REGEXTRACT approach that even spills for the full length of column C is this:

=IFNA(REGEXEXTRACT(C.:.C, "\((\d+)\)", 2), "")

None of the REGEX approaches work as well as the TEXTBEFORE and TEXTAFTER approach if your cells contain negative numbers within the parentheses or if there is an exponent (such as "Falcon (7E2) Vector").

If you are using an older version of Excel, then you can rely on the FIND function. Even though the formula is a bit longer, it works essentially the same as using TEXTBEFORE and TEXTAFTER:

=IFERROR(VALUE(MID(C2,FIND("(",C2)+1,FIND(")",C2)-FIND("(",C2)-1)),"")

If you want to forego formulas all together and your data is rather simple in nature, you can rely on Flash Fill. In the cell to the right of your first value, type the correct result. Thus, if cell C2 contains "Falcon (123) Vector", you would type "123" in cell D2. Select cell D2, and then press Ctrl+E. Excel fills every cell in column D with the number extracted from the text in column C.

Notice I said that your data needs to be rather simple. The Flash Fill approach doesn't work well if your data has cells that don't include parentheses, don't have an opening or closing parenthesis, or have parentheses that contain letters instead of numbers. In that case, you are better off with picking a formulaic approach that best matches the nature of your data.

Also, Flash Fill may not be the way to go if your data in column C changes. It produces static results, and if your source data changes, you will need to delete the contents of column D and again use the Flash Fill steps to generate new results.

If you prefer to go the route of a macro, then the following creates a user-defined function that you can use:

Function ParenNum(sTarget As Range)
    Dim sRaw As String
    Dim iStart As Integer
    Dim iEnd As Integer
    Dim J As Integer
    Dim sNum As String
    Dim bValid As Boolean

    ParenNum = ""
    If sTarget.Cells.Count = 1 Then
        sRaw = sTarget.Text
        iStart = InStr(1, sRaw, "(", vbTextCompare)
        If iStart > 0 Then
            iEnd = InStr(iStart, sRaw, ")", vbTextCompare)
            If iEnd > 0 Then
                sNum = Mid(sRaw, iStart + 1, iEnd - iStart - 1)
                If Len(sNum) > 0 Then
                    bValid = True
                    For J = 1 To Len(sNum)
                        If Asc(Mid(sNum, J, 1)) < 48 _
                          Or Asc(Mid(sNum, J, 1)) > 58 Then
                            bValid = False
                            Exit For
                        End If
                    Next J
                    If bValid Then ParenNum = CLng(sNum)
                End If
            End If
        End If
    End If
End Function

To use the function, simply reference the cell to be used in the extraction in this manner:

=ParenNum(C2)

The ParenNum user-defined function is very strict about what can be between the parentheses. If there is a negative sign or an E (for an exponent), then the function treats those characters as text and, therefore, returns an empty string. If you want the function to accept negatives or exponents, then the following variation should work just fine:

Function ParenNum2(sTarget As Range)
    Dim sRaw As String
    Dim iStart As Integer
    Dim iEnd As Integer
    Dim J As Integer
    Dim sNum As String

    ParenNum = ""
    If sTarget.Cells.Count = 1 Then
        sRaw = sTarget.Text
        iStart = InStr(1, sRaw, "(", vbTextCompare)
        If iStart > 0 Then
            iEnd = InStr(iStart, sRaw, ")", vbTextCompare)
            If iEnd > 0 Then
                sNum = Mid(sRaw, iStart + 1, iEnd - iStart - 1)
                If Len(sNum) > 0 Then
                    If IsNumeric(sNum) Then ParenNum = CLng(sNum)
                End If
            End If
        End If
    End If
End Function

In almost every approach discussed in this tip, if the cell contains two sets of parentheses—such as "Falcon (123) Vector (456) Tango"—then the value of the first set of parentheses is returned. I said "almost every approach" because, interestingly enough, the Flash Fill approach doesn't do that. It returns whatever is in the second set of parentheses.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13977) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.