How to find numbers from a string

Question

I need to find numbers from a string  How does one find numbers from a string in VBA Excel

User · Answer

Regular expressions are built to parse  While the syntax can take a while to pick up on this approach is very efficient  and is very flexible for handling more complex string extractions replacements  Sub Tester        MsgBox CleanString  3d1fgd4g1dg5d9gdg   End Sub  Function CleanString strIn As String  As String     Dim objRegex     Set objRegex   CreateObject  vbscript regexp       With objRegex       Global   True       Pattern       d        CleanString    Replace strIn  vbNullString      End With End Function

User · Answer

This a variant of brettdj s  amp  pstraton post   This will return a true Value and not give you the  NUM  error  And  D is shorthand for anything but digits  The rest is much like the others only with this minor fix   Function StripChar Txt As String  As Variant With CreateObject  VBScript RegExp        Global   True      Pattern     D      StripChar   Val  Replace Txt        End With End Function

User · Answer

Alternative via Byte Array  If you assign a string to a Byte array you typically get the number equivalents of each character in pairs of the array elements  Use a loop for numeric check via the Like operator and return the joined array as string   Function Nums s     Dim by   As Byte  i amp   ii amp    by   s  ReDim tmp UBound by                        assign string to byte array  prepare temp array   For i   0 To UBound by  - 1 Step 2                 check num value in byte array  0  2  4     n-1        If Chr by i   Like     Then tmp ii    Chr by i    ii   ii   1   Next i   Nums   Trim Join tmp  vbNullString                 return string with numbers only   End Function   Example call  Sub testByteApproach     Dim s   s    a12bx99y    3 14159                     1  define original string   Debug Print s  amp      gt     amp  Nums s                      2  display original string and result End Sub   would display the original string and the result string in the immediate window     a12bx99y    3 14159   gt  1299314159

User · Answer

I was looking for the answer of the same question but for a while I found my own solution and I wanted to share it for other people who will need those codes in the future  Here is another solution without function   Dim control As Boolean Dim controlval As String Dim resultval As String Dim i as Integer  controlval    A1B2C3D4   For i   1 To Len controlval  control   IsNumeric Mid controlval  i  1   If control   True Then resultval   resultval  amp  Mid controlval  i  1  Next i   resultval   1234

User · Answer

This is based on another answer  but is just reformated   Assuming you mean you want the non-numbers stripped out  you should be able to use something like       Skips all characters in the input string except digits   Function GetDigits ByVal s As String  As String     Dim char As String     Dim i As Integer     GetDigits          For i   1 To Len s          char   Mid s  i  1          If char  gt    0  And char  lt    9  Then             GetDigits   GetDigits   char         End If     Next i End Function   Calling this with   Dim myStr as String myStr   GetDigits  3d1fgd4g1dg5d9gdg   Call MsgBox myStr    will give you a dialog box containing   314159   and those first two lines show how you can store it into an arbitrary string variable  to do with as you wish

User · Answer

Assuming you mean you want the non-numbers stripped out  you should be able to use something like   Function onlyDigits s As String  As String       Variables needed  remember to use  option explicit            Dim retval As String      This is the return string             Dim i As Integer          Counter for character position           Initialise return string to empty                             retval             For every character in input string  copy digits to               return string                                               For i   1 To Len s          If Mid s  i  1   gt    0  And Mid s  i  1   lt    9  Then             retval   retval   Mid s  i  1          End If     Next        Then return the return string                                 onlyDigits   retval End Function   Calling this with   Dim myStr as String myStr   onlyDigits   3d1fgd4g1dg5d9gdg   MsgBox  myStr    will give you a dialog box containing   314159   and those first two lines show how you can store it into an arbitrary string variable  to do with as you wish

User · Answer

Use the built-in VBA function Val  if the numbers are at the front end of the string   Dim str as String Dim lng as Long  str    1 149 xyz  lng   Val str    lng   1149  Val Function  on MSDN

User · Answer

Based on  brettdj s answer using a VBScript regex ojbect with two modifications   The function handles variants and returns a variant  That is  to take care of a null case  and Uses explicit object creation  with a reference to the  quot Microsoft VBScript Regular Expressions 5 5 quot  library  Function GetDigitsInVariant inputVariant As Variant  As Variant     Returns          Only the digits found in a varaint      Examples          GetDigitsInVariant Null    gt  Null         GetDigitsInVariant  quot  quot     gt   quot  quot          GetDigitsInVariant 2021- 05-May -18  Tue    gt  20210518         GetDigitsInVariant 2021-05-18    gt  20210518     Notes          If the inputVariant is null  null will be returned          If the inputVariant is  quot  quot    quot  quot  will be returned      Usage          VBA IDE Menu  gt  Tools  gt  References                gt   quot Microsoft VBScript Regular Expressions 5 5 quot   gt   OK       With an explicit object reference to RegExp we can get intellisense     and review the object heirarchy with the object browser      VBA IDE Menu  gt  View  gt  Object Browser     Dim regex As VBScript RegExp 55 RegExp   Set regex   New VBScript RegExp 55 RegExp      Dim result As Variant   result   Null      If IsNull inputVariant  Then     result   Null        Else     With regex        Global   True        Pattern    quot    d   quot        result    Replace inputVariant  vbNullString      End With   End If      GetDigitsInVariant   result End Function  Testing  Private Sub TestGetDigitsInVariant     Dim dateVariants As Variant   dateVariants   Array Null   quot  quot    quot 2021- 05-May -18  Tue quot                quot 2021-05-18 quot    quot 18 05 2021 quot    quot 3434    sdf sfd 444 quot        Dim dateVariant As Variant   For Each dateVariant In dateVariants     Debug Print dateVariant  amp   quot    quot     GetDigitsInVariant dateVariant    Next dateVariant   Debug Print End Sub

User · Answer

Expanding on brettdj s answer  in order to parse disjoint embedded digits into separate numbers   Sub TestNumList       Dim NumList As Variant   Array      NumList   GetNums  34d1fgd43g1 dg5d999gdg2076        Dim i As Integer     For i   LBound NumList  To UBound NumList          MsgBox i   1  amp        amp  NumList i      Next i End Sub  Function GetNums ByVal strIn As String  As Variant   Array of numeric strings     Dim RegExpObj As Object     Dim NumStr As String      Set RegExpObj   CreateObject  vbscript regexp       With RegExpObj          Global   True          Pattern       d            NumStr    Replace strIn           End With      GetNums   Split Trim NumStr        End Function

[string] How to find numbers from a string?

Examples related to string

Examples related to vba

Examples related to excel