Finding substring in text field with a specific format (1 Viewer)

umpscott

New member
Local time
Today, 01:31
Joined
Dec 10, 2018
Messages
1
Hello

Brand new to the group. (This looks like a great group...wish I found it earlier in my career)

Thank you in advance for helping or attempting to help.

I have an access DB with a memo (Long Text) field.
This memo field contains lots of information, which is hand-typed and not consistent.

I need to pull from this field a string the matches the following format

####-####

(That being 4 numbers, a dash, then 4 numbers.)

I would prefer to use a built in function, but am comfortable with using VBA.

Examples:
123 text more text account 1234-5678 text more text
test more text 12321324234 acct: 2345-6789 text more text
test-moretext 1234 5678 Account Number 3456-7890 text more

Results would return:
1234-5678
2345-6789
3456-7890
 

ByteMyzer

AWF VIP
Local time
Today, 01:31
Joined
May 3, 2004
Messages
1,409
There is no built-in Access-VBA function for this of which I am aware, but since you say you are comfortable using VBA, you could try something like:
Code:
[COLOR="Navy"]Public Function[/COLOR] MyFunction([COLOR="navy"]ByVal[/COLOR] strArg [COLOR="navy"]As String[/COLOR]) [COLOR="navy"]As String

    Dim[/COLOR] lPos [COLOR="navy"]As Long[/COLOR]

    strArg = " " & strArg & " "

    [COLOR="navy"]For[/COLOR] lPos = 1 [COLOR="navy"]To[/COLOR] Len(strArg) - 10
        [COLOR="navy"]If[/COLOR] Mid(strArg, lPos, 11) [COLOR="navy"]Like[/COLOR] " [0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9] " [COLOR="navy"]Then[/COLOR]
            MyFunction = Mid(strArg, lPos + 1, 9)
            [COLOR="navy"]Exit For
        End If
    Next[/COLOR] lPos

[COLOR="navy"]End Function[/COLOR]
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:31
Joined
May 21, 2018
Messages
8,463
If your string can have multiple instances of your pattern you can use regexp to return all of them in a collection. If you look at the example I put in three of the pattern and it returned all three.
1234-7890
3456-1789
1234-5678
Code:
Public Function ReturnMatches(strWord As String) As VBScript_RegExp_55.MatchCollection
  'This function returns a collection of emails found in a string
  'Need Microsoft VBScript Regular Expressions
   Dim objRegExp As VBScript_RegExp_55.RegExp
   Dim objMatch As VBScript_RegExp_55.match
   Dim myPattern As String

   myPattern = "(\d{4}\-\d{4})"
  'Create a regular expression object.
   Set objRegExp = New RegExp
  'Set the pattern by using the Pattern property.
   objRegExp.Pattern = myPattern
  'Set Case Insensitivity.
   objRegExp.IgnoreCase = False
  'Set global applicability. Not sure what that does
   objRegExp.Global = True
  'Test whether the String can be compared. Not sure what that does
  If (objRegExp.test(strWord) = True) Then
     Set ReturnMatches = objRegExp.Execute(strWord)   ' Execute search.
     'lets assume you only get one match
   Else
     Debug.Print "Could not compare string"
   End If
 '
End Function
Public Sub ExtractPattern()
  Dim FoundStrings As VBScript_RegExp_55.MatchCollection
  Dim I As Integer
  Set FoundStrings = ReturnMatches("A 1234-7890 and 12-12-12 and 3456-1789 and (123)-456-7890 1234-5678")
  If Not FoundStrings Is Nothing Then
   For I = 0 To FoundStrings.Count - 1
     Debug.Print FoundStrings(I)
   Next I
 End If
 End Sub
I am not very good at regexp, but I think that is the correct pattern myPattern = "(\d{4}\-\d{4})" some other people on this forum are very good at them.
 

Users who are viewing this thread

Top Bottom