Extract Numbers from a string

Hi

I am really at a loss with VBA Code. I have looked on here and whilst the code supplied may do what i want, I cannot use the code if I dont understand what it is actually doing. So...(looking at the help!!!) I tried an attempt at pulling the numbers from a cell which may contain alphanumeric chars - What a mistake!

The help say Val Function: returns the numbers contained in a string as a numeric value of appropriate type and gives an example. My attempt should loop through the cells in Column A and return the numbers in Column B)...until it gets to last row.... sooooo why wont this work..?

Sub Test()

Dim MyString
Dim a As Integer, Numrows As Integer

Numrows = Range("A65536").End(xlUp).Row               'Gets the Row count
For a = 1 To Numrows
  MyString = Val(".Cells(a + 1, 1).Value")
  .Cells(a + 1, 2).Value = MyString
Next a

End Sub

It bombs out at .Cells(a + 1, 2).Value = MyString saying invalid or unqualified reference.

I really want to learn but find that the help will tell me something but when I apply it, it doesnt work!

thanks again.. a struggling dugong!

Originally Posted by dominicb

Not sure if this code does exactly what you want. The Val function returns all the numbers up to the first non numeric character, as the Excel helpfile demonstrates. If you want to extract all numeric characters you'd need to loop through each character in the cell....DominicB

Thanks DominicB - i see what you mean. It only pulls out the numbers at the beginning!

If I was to loop through each char, I understand I can use the LEN function to set an amount to loop through to but am unsure how to tell VBA how to recognise the first char in the cell to start with - does that make sense?

Actually thinking aloud, maybe I could use the mid function to pull each char out and test it against 0 and 1-9 but how would I return the value in the next column..?

dugong

try

Sub test()
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+(\.\d+)?"
    .Global = False
    For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
        If .test(r.Value) Then r.Offset(,1).Value = .execute(r.Value)(0)
    Next
End With
End Sub

Originally Posted by jindon

try

Sub test()
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+(\.\d+)?"
    .Global = False
    For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
        If .test(r.Value) Then r.Offset(,1).Value = .execute(r.Value)(0)
    Next
End With
End Sub

Thanks Jindon - but (if you have a minute of time ) can you explain what each part is doing..?
As mentioned, I can't use the code - or adapt it - if I dont understand what it is actually doing.

dugong

Originally Posted by dugong

Thanks Jindon - but (if you have a minute of time ) can you explain what each part is doing..?
As mentioned, I can't use the code - or adapt it - if I dont understand what it is actually doing.

dugong

Then you can forget about my code...

Try search the web with the keywords

vba Regular Expression

will give you the idea...
Is this more understandable ?

Sub test()
Dim r As Range, i As Long
For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
    If Len(r.Value) Then
        For i = 1 To Len(r.Value)
            If Val(Mid$(r.Value, i)) <> 0 Then
                r.Offset(,1).Value =  Val(Mid$(r.Value, i))
                Exit For
            End If
        Next
    End If
Next
End Sub

Thanks Simon - I will look at that too!