Extract Characters from Left through pattern "||"

[jimdltn]

I have a key text field (created in Cache)i.e., 1||987, 2345||2, 567||24. What I want to extract are the number (characters) after the || pattern. So I want to return 987, 2, 24. A little help would be appreciated.

 

[Remou]

You could Split at the comma and then use InStr on each element of the array.

 

[EBGreen]

Regular expressions would handle this quite well also.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[JerryKlmns]

Use Split() at the comma and then Split() at the || and grab the 2nd element

 

[HarleyQuinn]

Ahahaha! Regular Expressions

Seriously though, this UDF will work (paste it into a module):

Function FindStringNumbers(strInput As String) As String
Dim objReg As Object
Dim objMatches As Object
Dim objMatch As Object

Set objReg = CreateObject("VBScript.RegExp")

With objReg
    .IgnoreCase = True
    .Multiline = False
    .Global = True
    .Pattern = "\|\|[0-9]*"
    Set objMatches = .Execute(strInput)
    
        For Each objMatch In objMatches
            FindStringNumbers = FindStringNumbers & Replace(Trim(objMatch), "||", "") & " "
        Next objMatch
End With

FindStringNumbers = Replace(Trim(FindStringNumbers), " ", ",")

Set objReg = Nothing

End Function

Call it like this:

Results: FindStringNumbers([YourFieldName]

Please bear in mind I've knocked this up very quickly so some of the replacing etc. could be done better but it is working on your test data.

If you would like an explanation of how and why it works I'll be more than happy to oblige.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Oops, thought I was in the Jet SQL forum, the function will still work fine, just don't call it like a field in a query

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.