[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

extract numbers from text string

Posted on 2014-08-04
4
Medium Priority
?
171 Views
Last Modified: 2014-08-14
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.
0
Comment
Question by:mkobey
  • 2
4 Comments
 
LVL 37

Expert Comment

by:Kimputer
ID: 40240071
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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1000 total points
ID: 40240111
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
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 total points
ID: 40240924
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40241663
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question