asked on

extract numbers from text string

I have a table of data and one of the column contains a string. The first set of charachters are always a number. The second set of charachters are always letters. I am trying to extract the first set of numeric charachters. Using a simple left() function does not work becasue sometimes the numbers are one digit an sometimes they are two. For example, the string may look like the following:

11A6

or

1A6.

In the example above, I need to extract the '11' and the '1' respectively.

Thanks for your assistance.

Kimputer

Will this work for you ?

Sub test()

Dim str As String
str_in = "09s8asdf098sd0985"
str_out = ""
For i = 1 To Len(str_in)
    If IsNumeric(Mid(str_in, i, 1)) Then
        str_out = str_out + Mid(str_in, i, 1)
    End If
Next i
MsgBox str_out

End Sub

Open in new window

There's also a formula instead:

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$20),1)),0),COUNT(1*MID(A1,ROW($1:$20),1)))

Open in new window

assuming the string is in A1, and you put this formula in B1 (and finish with CTRL+SHIFT+ENTER, and sometimes regional settings make your formula use ; instead of , so change that if you see an error)
One slight flaw though, this only detects one number sequence (in essence, returns 11 when cell A1 is 11AA, but returns N/A when cell A1 is 11AA11)

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Glenn Ray

If indeed there are always one or two leading digits and you want the number value, Rob's solution is best. It's simple and faster than the array function I provided.

If there could be more than two characters - or if you need leading zeroes - or if there are no numbers - the array function will work.

-Glenn