Getting individual numbers from a string

Question

I want to extract individual numbers from a string. So for:

x = "    99                1.2           99.25       "

I want to get three individual numbers: 99, 1.2, and 99.25.

Here is my current code. It extracts the first occurring number, but I do not know how to use loops to get the three individual numbers.

Sub ExtractNumber()
Dim rng As Range
Dim TestChar As String
Dim IsNumber As Boolean
Dim i, StartChar, LastChar, NumChars As Integer
For Each rng In Selection
    IsNumber = False
    i = 1
    Do While IsNumber = False And i <= Len(rng)
        TestChar = Mid(rng, i, 1)
        If IsNumeric(TestChar) = True Then
            StartChar = i
            IsNumber = True
        End If
        i = i + 1
    Loop
    IsNumber = False
    Do While IsNumber = False And i <= Len(rng)
        TestChar = Mid(rng, i, 1)
        If IsNumeric(TestChar) = False Or i = Len(rng) Then
            If i = Len(rng) Then
                LastChar = i
            Else
                LastChar = i - 1
            End If
            IsNumber = True
        End If
        i = i + 1
    Loop
    NumChars = LastChar - StartChar + 1
    rng.Offset(0, 1).Value = Mid(rng, StartChar, NumChars)
    Next rng
End Sub

My previous attempt (input is stored in cell A6):

Dim x, y, z As String

x = Range("A6")
y = Len(x)

For i = 1 To Len(x)
    If IsNumeric(Mid(x, i, 1)) Then
        z = z & Mid(x, i, 1)
    End If
Next i

MsgBox z

Answer 1

If speed is not an issue (if the task is not intensive, etc) then you can use this

Public Sub splitme()

Dim a As Variant
Dim x As String
Dim i, j As Integer

Dim b() As Double

x = "1.2 9.0  0.8"
a = Split(x, " ")

j = 0
ReDim b(100)

For i = 0 To UBound(a)
If (a(i) <> "") Then
    b(j) = CDbl(a(i))
    j = j + 1
End If

Next i


ReDim Preserve b(j - 1)
End Sub

Error checking needs to be included for b(100), to suit your particular needs - and with CDbl.

If this is to be used as part of a loop, or for large x - or both, consider other options like RegEx (previous answer) - as repeated calls to ReDim Preserve are generally best avoided.

Answer 2

Rather than writing your own code to extract the numbers, why not try using Regular Expressions? This website has a lot of great info and tutorials on regular expressions. It can be a bit baffling at first but once you get the hang of it it's a very powerful tool for solving problems of this type.

Below is an example of extracting the information you're after using a regular expression object.

Public Sub ExtractNumbers()
    'Regular Expression Objects
    Dim objRegEx As Object
    Dim objMatches As Object
    Dim Match As Object

    'String variable for source string
    Dim strSource As String

    'Iteration variable
    Dim i As Integer

    'Create Regular Expression Object
    Set objRegEx = CreateObject("VBScript.RegExp")

    'Set objRegEx properties
    objRegEx.Global = True '<~~ We want to find all matches
    objRegEx.MultiLine = True '<~~ Allow line breaks in source string
    objRegEx.IgnoreCase = False '<~~ Not strictly necessary for this example

    'Below pattern matches an integer or decimal number 'word' within a string
    '   \b matches the start of the word
    '   [+-]? optionally matches a + or - symbol
    '   [0-9]+ matches one or more digits in sequence
    '   (\.[0-9]+)? optionally matches a period/decimal point followed by one or more digits
    '   \b matches the end of the word
    objRegEx.Pattern = "\b[+-]?[0-9]+(\.[0-9]+)?\b"

    'Example String
    strSource = "x=  99     10.1    20.6  Aardvark"

    'Ensure that at least one match exists
    If objRegEx.Test(strSource) Then

        'Capture all matches in objMatches
        Set objMatches = objRegEx.Execute(strSource)

        'TODO: Do what you want to do with them
        'In this example I'm just printing them to the Immediate Window

        'Print using Match object and For..Each
        For Each Match In objMatches
            Debug.Print Match.Value
        Next Match

        'Print using numeric iteration (objMatches.Items is a 0-based collection)
        For i = 0 To (objMatches.Count - 1)
            Debug.Print objMatches.Item(i)
        Next i

    End If
End Sub

Both of the print variations shown in this example would print the following output to the Immediate window

99
10.1
20.6