Extracting numbers from a string (1 Viewer)

[w11184]

Hi,

Does anyone know how I go about extracting numbers from a string?

At the moment I use a loop to go through a string and then use the IsNumeric function to extract any numbers in that string and put it in another variable. However, this only works if it was a single digit. However, what if I have numbers such as 12 or 11.5 in my string?

Thanks for your help.

 

[plog]

How would you do it manually? Write that process down and then translate it into code. It would look something like this:

Start at left most position of string.
Move right character by character until you find a character that is a number
Save that position.
From there, move right character by character until you come to a character that is neither a decimal nor a number or is the last position of the string.
Save that position.
You now have the starting and ending positions of the number and can extract it from the string.

If you have multiple numbers in a string, repeat the above process until you've come to the end of the string.

 

[mtn]

Will this work for you?

Public Function ExtractNumeric(strInput) As String
'http://www.utteraccess.com/forum/Creating-Unique-Property-t1964859.html

' Returns the numeric characters within a string in
' sequence in which they are found within the string

Dim strResult As String, strCh As String
Dim intI As Integer
If Not IsNull(strInput) Then
For intI = 1 To Len(strInput)
strCh = Mid(strInput, intI, 1)
Select Case strCh
Case "0" To "9"
strResult = strResult & strCh
Case Else
End Select
Next intI
End If
ExtractNumeric = strResult

End Function

 

[dpw204]

Select Case strCh
Case "0" To "9"
strResult = strResult & strCh
Case Else
End Select

MTN's code should serve as a good starting point, but it doesn't handle decimal numbers. A minor change will cover that:

Select Case strCh
Case "0" To "9"
strResult = strResult & strCh
Case "." ' ALSO append if a 'period' is used.  (Note: for European users a comma may need to be watched for as well.)
strResult = strResult & strCh
Case Else
End Select

But... This can be a gigo situation. What is a typical input string? Are there any controls in place to insure that the input has some integrity?

The test code below will 'work', but will it do what you want? What if there is more than one set of numbers embedded within the string? Any language punctuation? In that case even more error checking will be needed.

MsgBox "   Input: ABC123.DEF456" & vbNewLine & "  Output: " & ExtractNumeric("ABC123.DEF456")
MsgBox "   Input: ABC.123DEF456" & vbNewLine & "  Output: " & ExtractNumeric("ABC.123DEF456")
MsgBox "   Input: The End. ABC123DEF456" & vbNewLine & "Output: " & ExtractNumeric("The End.  ABC123DEF456")
MsgBox "   Input: ABC123DEF456 The End." & vbNewLine & "Output: " & ExtractNumeric("ABC123DEF456 The End.")

Good Luck!

 

[vbaInet]

It would be worth showing us some sample strings. We would like to see if the numbers start or end at a particular point in your string. You may not need a loop afterall.

 

[w11184]

Hi everyone,

Thank you for your suggestions. I guess the problem I am having is how to get the second number to append to a new variable.

As en example the strings always looks like this: "12 To 40"
or in some cases there's a decimal in it: "2.4 To 5"

 

[tehNellie]

if it always that format you could use Split()

ie

dim strTest() as string -- note this is a string datatype, not a number (yet)

strTest = split("2.5 to 5", " to ")

You now have a two element array, the first contains "2.5" the second "5" as string values.

If there is a chance that the format wont always be "nn to nn" then it's not going to work, but you can now test the two array strings to see if you have an integer or decimal value and treat accordingly having thrown away the "junk" you aren't interested in.

 

[w11184]

Hi,

Sorry to bring this up again but the previous solution doesn't quite work for my new problem.

Basically I have strings that are not in a consistent format. For example, I would like to be able to extract the numbers from the phrase "1 Day I went to see 5 elephants at the zoo. It was 25 miles away from my 4 bedroom house" and put each of those numbers in an array variable. Like this...

x(1) = 1, x(2) = 5, x(3) =25, x(4) = 4

So that I can perform calculations on them.

Can you point me in the right direction about how that can be done?

Thanks!

 

[vbaInet]

First of all loop through each character in the string. Once you have that working we can forge ahead.

 

[mdlueck]

First of all loop through each character in the string..

Some sample code for the OP...

Public Function conversion_PrepReportNameString(ByVal strIn As String) As String

  Dim lMaxStep As Long
  Dim lStep As Long
  Dim strThisChar As String
  Dim strOut As String

  'Insure strOut is empty
  strOut = vbNullString

 'Size up how many characters / digits in the variable we were passed
  lMaxStep = Len(strIn)

  'Go through one character at a time, sanatize
  For lStep = 1 To lMaxStep
    'Grab the next character...
    strThisChar = Mid(strIn, lStep, 1)

    ...
  Next lStep
End Function

 

[w11184]

Ok. I've done that and this is what I have at the moment...

Public Sub ExtractIntFromString2()

Dim Phrase As String
Phrase = "1 Day I went to see 5 elephants at the zoo. It was 25 miles away from my 4 bedroom house"

Dim str As String, i As Integer

For i = 1 To Len(Phrase)
    If IsNumeric(Mid(Phrase, i, 1)) Then
    str = str & Mid(Phrase, i, 1)
    
    End If
    
Next i

Debug.Print str

End Sub

 

[vbaInet]

OK, next step is to create an array and dimension it to be the length of the string.

Keep a track of the index of the current array position and concatenate the number to the array index until you encounter a non-numeric character.

At the end of it all, redim your array to be the exact number of positions you've filled.

 

[mdlueck]

For i = 1 To [B]Len(Phrase)[/B]

This LOC will needlessly compute the Len for each iteration of the loop. Note the suggestion to compute it once and refer to the variable it gets stored to once it has been calculated.

 

[vbaInet]

Good catch Michael.

At the end of it all, redim your array to be the exact number of positions you've filled.

By the way that should have been redim preserve.

 

[w11184]

OK, next step is to create an array and dimension it to be the length of the string.

Keep a track of the index of the current array position and concatenate the number to the array index until you encounter a non-numeric character.

At the end of it all, redim your array to be the exact number of positions you've filled.

Hi, thanks very much for your advice but I am struggling with this. How do I put each number into a variable? And any 2 digit numbers would still be separated?

 

[vbaInet]

I didn't mention putting each number into a variable. Have you done what I mentioned in my first sentence?

 

[mtn]

Not exactly what you want but almost there...

Public Sub ExtractIntFromString2()

    Dim X As Long
    Dim strTemp As String
    Dim Phrase As String
    
    Phrase = "1 Day I went to see 5 elephants at the zoo. It was 25 miles away from my 4 bedroom house"

    Dim str As String, i As Integer

    For i = 1 To Len(Phrase)
        If IsNumeric(Mid(Phrase, i, 1)) Then
            str = str & Mid(Phrase, i, 1)
        End If
    Next i

    'Debug.Print str
    
    For X = 1 To Len(str)
        strTemp = strTemp & "x(" & X & ") = " & Mid(str, X, 1) & ", "
    Next X
    Debug.Print strTemp
    
End Sub

 

[w11184]

I didn't mention putting each number into a variable. Have you done what I mentioned in my first sentence?

Umm...ok.

Yes I think I've done that. This is what I have at the moment.

Public Sub ExtractIntFromString2()

Dim Phrase As String
Phrase = "1 Day I went to see 5 elephants at the zoo. It was 25 miles away from my 4 bedroom house"

Dim PhraseLength As Integer
PhraseLength = Len(Phrase)

Dim OutputArray() As Integer
ReDim OutputArray(1 To PhraseLength) As Integer


Dim str As String, i As Integer

For i = 1 To PhraseLength
    If IsNumeric(Mid(Phrase, i, 1)) Then
    str = str & Mid(Phrase, i, 1)
    
    End If
    
Next i

Debug.Print str

End Sub

Apologies if this is wrong. My skills' a bit limited.

 

[mdlueck]

I guess the problem I am having is how to get the second number to append to a new variable.

Will the data always be space character delimited?

If so, could implement a "scan for space character / consider that word between two space characters if it is numeric" rather than going a character at a time.

Please confirm my question, sample air code to follow only if you choose "yes".

 

[vbaInet]

Aircode:

prevIsNumeric as boolean
pos as long
 
pos = 1
 
if isnumeric() then
    if prevIsNumeric then
        outputarray(pos) = outputarray(pos) & mid()
    else
        pos = pos + 1
        outputarray(pos) = outputarray(pos)
    end if
    prevIsNumeric = true
else
    prevIsNumeric = fase
end if
 
redim preserve ...