Get Long From String

Logic

Loop through each character of a string that is passed and test to see if it is a digit. If it is, then concatenate it to the end of sNumber, which is a string variable. If it isn't a digit and sNumber doesn't yet have any digits, keep looking. If sNumber has digits and the next character is not a digit, see if pBooKeepLooking is true; if it is then get anything that is a digit even if it is not next to other numbers; otherwise stop looking. When done, if sNumber has anything, convert it (from string to long) and return a Long Integer.

If the string didn't have any digits, 0 is returned. If this is not what you want, then set the return data type to be Variant instead of Long so you can return Null.

This code will work in Excel too -- as does much of what you'll find on this site ... just want to specifically point it out here so you keep that in mind ...

Parameters

pString is the string to find digits in

Optional:

pBooKeepLooking is true if you want to find all digits even if they are not next to each other. Default is false so that the first set of contiguous digits found is returned.

Code

'*************** Code Start *****************************************************'---------------------------------------------------------------------------------------
' Purpose  : Loop through characters of a string and save character digits
' Author   : crystal (strive4peace) 
' Return   : Long
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'---------------------------------------------------------------------------------------
'                              GetLongFromString'---------------------------------------------------------------------------------------
' 
Public Function GetLongFromString( _
   pString As String _
   , Optional pBooKeepLooking As Boolean = False _
   ) As Long
'140827 strive4peace, 181204   
   Dim i As Integer _
      , sNumber As String
   sNumber = ""
   For i = 1 To Len(pString)
      Select Case Asc(Mid(pString, i))
         Case 48 To 57  '0-9 
            sNumber = sNumber & Mid(pString, i, 1)
         Case Else
            If sNumber <> "" Then            
               If Not pBooKeepLooking Then                              
                  GetLongFromString = CLng(sNumber)
                  Exit Function
               End If
            End If
      End Select
   Next i
   If sNumber <> "" Then  
      GetLongFromString = CLng(sNumber)
   End If      
End Function
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

VBA > Number > Get Long From String

Examples