CODEWITHSUNDEEP
ms-accessmodulevbaexpressionms-access-2007
Jenny Benavides
Jenny Benavides

Reputation: 1

extract numbers from string in access

I need help creating a VB code or expression in Access 2010 that will group numbers from a string where each set starts with number 6 and is always 9 characters long.

Example of strings:

  1. Order Confirmation # 638917872-001 Partial Order/$23.74 RECEIVED

  2. Order Confirmation - Multiple Orders - Order Confirmation#639069135-001/$297.45 - Order Confirmation#639069611-001/$32.08.

I'm using a VB code to remove all the alpha characters but that just leaves me with:

All I care about is the order number that starts with 6 and is 9 characters long. Any help would be greatly appreciated, I know there's an easier way.

Upvotes: 0

Views: 6056

Answers (3)

Tom Collins
Tom Collins

Reputation: 4069

OK, here's a VBA solution. You'll need to add Microsoft VBScript Regular Expressions to your references.
This will match every 9 digit number it finds and return an array of strings with the order #s.

Function GetOrderNum(S As String) As String()
   Dim oMatches As Object
   Dim aMatches() As String
   Dim I As Integer
   Dim RE As Object
   Set RE = CreateObject("vbscript.regexp")
   ReDim aMatches(0)
   RE.Pattern = "\d{9}"
   RE.Global = True
   RE.IgnoreCase = True
   Set oMatches = RE.Execute(S)

   If oMatches.Count <> 0 Then
     ReDim aMatches(oMatches.Count)
       For I = 0 To oMatches.Count - 1
         aMatches(I) = oMatches(I)
       Next I
   End If
   GetOrderNum = aMatches
End Function

Upvotes: 0

maxedev
maxedev

Reputation: 941

VB.NET Solution:

If you just need the first 9 numbers from your resulting strings you could use String.Substring, ie:

Dim numberString as String = "6389178720012374"
Dim newString As String = numberString.Substring(0, 9)
MessageBox.Show(newString)

shows 638917872

MSDN Link

EDIT:

Maybe you would want to use a RegEx - something like this perhaps can get you started:

Private Sub Input()
    Dim numberString As String = "Order Confirmation # 638917872-001 Partial Order/$23.74 RECEIVED"
    Dim numberString2 As String = "Order Confirmation - Multiple Orders - Order Confirmation#639069135-001/$297.45 - Order Confirmation#639069611-001/$32.08"

    GiveMeTheNumbers(numberString)
    GiveMeTheNumbers(numberString2)
End Sub

Function GiveMeTheNumbers(ByVal s As String) As String
    Dim m As Match = Regex.Match(s, "6\d{8}") 'get 9 digit #s begin w/6
    Do While m.Success
        MessageBox.Show(m.Value.ToString)
        m = m.NextMatch()
    Loop
    Return False
End Function

Results - MessageBox1: 638917872 MessageBox2: 639069135 MessageBox3: 639069611

Upvotes: 2

matzone
matzone

Reputation: 5719

You can use this function ... tested in VB.NET

Function NumOnly(ByVal s As String) As String
    sRes = ""

    For x As Integer = 0 To s.Length - 1           
        If IsNumeric(s.Substring(x, 1)) Then sRes = sRes & s.Substring(x, 1)
    Next
    return sRes
End Function

Little modif for ms-access

Upvotes: 0

Related Questions