Extract the nth number from a text string

Extract the nth number from a text string

To extract the nth number from a text string, use a formula based on the INDEX, TOROW, and TEXTSPLIT functions.

How to get the nth number from a text string

  1. Select cell F3
  2. Type =INDEX(TOROW(TEXTSPLIT(B3, {” “,”,”})+0, 2), n)
  3. Press Enter.
  4. The formula returns the nth number from a text string.

Example

In this example, we have a dataset in the range D3:D8 containing text strings with embedded numerical values separated by commas and spaces. The goal is to create a formula, extract the second number from each text string and display it in column F.

The formula combines text processing and array manipulation in Excel. It is handy when dealing with mixed data strings (text and numbers) where we want to extract specific numeric values.

Formula:

=INDEX(TOROW(TEXTSPLIT(D3, {” “,”,”})+0, 2), 2)

Extract the nth number from a text string

The formula in cell F3 combines the TEXTSPLIT and INDEX functions to separate the text into components based on delimiters and retrieve the desired numeric value. The result is a list of the second numbers corresponding to each row in the dataset.

Explanation

Evaluate the formula:

TEXTSPLIT(D3, {” “,”,”}): TEXTSPLIT splits the text in cell D3 into separate elements based on two delimiters: space (” “) and comma (,). It identifies and isolates each element, whether it is a name, number, or blank, by breaking the text wherever these delimiters appear. For example, “Peter, 120, John, 340, Steve 320” becomes the array {“Peter”; “120”; “John”; “340”; “Steve”; “320”}.

TEXTSPLIT(D3, {” “,”,”})+0: The +0 operation converts text-formatted numbers like “120” into actual numeric values. It applies numeric conversion to each element in the array, turning numbers into their numeric equivalents and leaving non-numeric elements (like “Peter”) as #VALUE! errors. For the input {“Peter”; “120”; “John”; “340”; “Steve”; “320”}, the output becomes {#VALUE!; 120; #VALUE!; 340; #VALUE!; 320}.

TOROW(…, 2): TOROW reshapes the vertical array {#VALUE!; 120; #VALUE!; 340; #VALUE!; 320} into a single horizontal row. It uses the second argument (2) to exclude errors (#VALUE!), leaving only valid numbers. The result is the cleaned numeric row [120, 340, 320].

INDEX(…, 2): INDEX retrieves the second element from the numeric row [120, 340, 320]. This step directly extracts the desired value from the array. In this case, INDEX returns 340.

Download the practice file.

Get the nth number from a cell using GETNUMBERS

You can use custom functions to extract the nth number from a text. GETNUMBERS is a powerful UDF (user-defined function). The function extracts the nth number from a text string. If you are working with data cleansing, this function is yours.

Get the nth number from a cell using GETNUMBERS

If you want to implement the code manually, you can do that:

Function GETNUMBERS(str As String, Optional n As Variant) As String
    Dim numArray() As String
    Dim rgx As Object, matches As Object
    Dim result As String
    Dim i As Integer

    If Len(str) = 0 Then
        GETNUMBERS = ""
        Exit Function
    End If

    Set rgx = CreateObject("VBScript.RegExp")

    With rgx
        .Global = True
        .pattern = "-?\d+(\.\d+)?([eE]-?\d+)?"
        Set matches = .Execute(str)
    End With

    If matches.count = 0 Then
        GETNUMBERS = ""
        Exit Function
    End If

    ReDim numArray(matches.count - 1)
    For i = 0 To matches.count - 1
        numArray(i) = matches(i).Value
    Next i

    Select Case True
        Case IsMissing(n)
            result = Join(numArray, ", ")
        Case n = "F"
            result = numArray(LBound(numArray))
        Case n = "L"
            result = numArray(UBound(numArray))
        Case IsNumeric(n) And n > 0 And n <= matches.count
            result = numArray(n - 1)
        Case Else
            result = ""
    End Select

    GETNUMBERS = result
End Function