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

Does it have to be VBA? If your numbers are seperated by spaces, you could use [Text-to-columns](https://support.office.com/en-gb/article/Split-names-by-using-the-Convert-Text-to-Columns-Wizard-39f7b055-6b39-4cb5-9512-13cc19b3a807) — Chrismas007, Jan 08 '15 at 15:13

Rusan Kax · Answer 1 · 2015-01-22T16:18:14.023

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.

edited Jan 22 '15 at 16:18

answered Jan 22 '15 at 13:02

Rusan Kax

1,894
2
13
17

Without having tested it, I'd argue this is faster than regex. – Tomalak Jan 22 '15 at 13:57
I also think that `CDbl()` throws when it can't successfully convert, so there ought to be some error handling in your code. – Tomalak Jan 22 '15 at 14:11

Aiken · Answer 2 · 2015-01-22T14:31:50.203

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

There's actually a bit of a blip in this regex in that it messes up if the source string contains a sequence like 2.03.2.2 (i.e. a string of digits with multiple periods) due to the fact that regex treats a period as a word boundary (\b) so bear that in mind. — Aiken, Jan 22 '15 at 13:39

Getting individual numbers from a string

2 Answers2

Linked