VBA equivelant of ParseInt() ?

[swisstoni]

Is there a quick and easy way to extract an integer from a string in VBA?

 

[DJkarl]

Is there a quick and easy way to extract an integer from a string in VBA?

In short no, there is nothing in VBA equivelant to ParseInt(). You could use IsNumeric to determine if the value is a number, if it is you can just use it with the Cint() function to return an integer, or if it is not a number you would need to pass it to a custom function and iterate through each character to find an integer contained in the string.

 

[swisstoni]

No wonder google wasn't bringing up much!

Hmm okay. Well I'm expecting strings in the format "1234-A" or "1234", where -A might be replaced with -B or -C, etc, so I just need the first X numerical digits.

Any idea where I might find such a function?

 

[DJkarl]

No wonder google wasn't bringing up much!

Hmm okay. Well I'm expecting strings in the format "1234-A" or "1234", where -A might be replaced with -B or -C, etc, so I just need the first X numerical digits.

Any idea where I might find such a function?

You would need to write one most likely.

If the - is always the character where the numbers end and the letter begin I would go with something simple like.

Left$("1234-A",instr("1234-A","-")-1)

However if the character isn't always going to be a "-" then you would need to write a more robust routine to weed out all non-numeric characters.

 

[swisstoni]

Thanks! With a bit of modificaton, that was exactly what I needed!

 

[chergh]

You quite possibly know this already but just in case you don't keep an eye on data types in VBA as they can be different. The integer data type for example is equivalent to the short data type in Java.

 

[gemma-the-husky]

if the number is at the begining of a string then

val (mystring) returns everything up to the first non-numeric character

or if you have something like

Brook House, 34 Mill Street, then split(mystring, " "), will give you an array of segments - you could then examine the segments to find a numeric one - depends on the data structure i suppose

split will split into segments based on any delimiter

 

[raskew]

Hi -

Copy/paste this to a standard module, then call it as shown:

Public Function GetNumer2(pstr As String) As Currency
'*******************************************
'Purpose:   Returns the first numerical
'           sequence in a string
'Coded by:  raskew
'Inputs:    ? getNumer2("ABC123")
'Output:    123
'*******************************************
Dim n       As Integer
Dim strHold As String
Dim strKeep As String

   strHold = Trim(pstr)
   n = Len(strHold)
   Do While n > 0
      If val(strHold) > 0 Then
         strKeep = val(strHold)
         n = 0
      Else
         strHold = Mid(strHold, 2)
         n = n - 1
      End If
   Loop
   
   GetNumer2 = val(strKeep)
        
End Function

HTH - Bob