CODEWITHSUNDEEP
vbams-access
sockpuppetmonkey
sockpuppetmonkey

Reputation: 51

MS Access VBA - Pulling a partial string from semi-consistent data in a field

I've looked around but wasn't able to find an exact solution...

I have an identifier-type field that generally follows the format of XXX-YY-ZZZZZZZZ. Sometimes the number of X's and Z's will vary, but the YY's are always enclosed between the two hyphens. If I wanted to create another field using just the "YY", what would be the best function to use? There's also another reference table with the YYs listed that I intend to relate it to.

Thanks in advance.

Upvotes: 0

Views: 38

Answers (1)

Ryan Wildry
Ryan Wildry

Reputation: 5677

Sounds like you want to split the string into an array. Try this out

Public Sub Example()
    Dim ExampleStr As String: ExampleStr = "XXX-YY-ZZZZZZZZ"
    Dim StrArray() As String

    StrArray = Split(ExampleStr, "-")

    Debug.Print StrArray(1) ' Return the second element
End Sub

Upvotes: 1

Related Questions