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.
mkobeyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
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)
0
Glenn RayExcel VBA DeveloperCommented:
You can find out the leftmost numeric characters with an array function.  

If your first value is in cell A2, enter this array formula (using [Ctrl]+[Shift]+[Enter):
=LEFT(A2,SUMPRODUCT(--(ISNUMBER(VALUE(LEFT(A2,ROW($1:$100)))))))

It will produce the left-most digits only but as text, in case there are leading zeroes.  See the attached example file.

Regards,
-Glenn
EE-ExtractNumericText.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Are they always just one or two digits?

If so this will suffice:

=IFERROR(LEFT(G30,2)*1,LEFT(G30,1)*1)

Where string of characters is in G30.

Thanks
Rob H
0
Glenn RayExcel VBA DeveloperCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.