VBA: position of substring in a comma-delimited string

Hi X-perts,

I have a comma delimited string like

mystring = "(number1,number2,number3, number4)

or (2343, 7765, 9928, 3354)

I need a function that returns a relative position of a given number inside that string, i.e.

myFunction(mystring, mynumber)

myFunction(mystring, 2343) = 1

etc.

what is the simplest way of doing this? In php it would take just two lines of code with explode/implode, but I am not sure what is the closest option in VBA.

Please, advise..... it needs to be a very short solution

Thanks
andy7789Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DhaestCommented:
Easiest way, with the help of split-function
Sub test()
    MsgBox GetPosition("(2343, 7765, 9928, 3354)", "7765")
End Sub
 
Public Function GetPosition(inString As String, searchString As String) As Integer
    Dim strSplit() As String
    GetPosition = -1
    
    ' removing ( and )
    inString = Replace(inString, "(", "")
    inString = Replace(inString, ")", "")
    strSplit = split(inString, ",")
    For i = 0 To UBound(strSplit)
        If Trim(strSplit(i)) = searchString Then
            GetPosition = i + 1
        End If
    Next i
    
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wayne Taylor (webtubbs)Commented:
If you are using VBA within Excel, you can use the below function, which also shows usage.

Wayne
Sub test()
 
     Dim mystring As String
     mystring = "(2343, 7765, 9928, 3354)"
 
     MsgBox IndexOf(mystring, "9928") 'will return 3
 
End Sub
 
Public Function IndexOf(strArray As String, item As String) As Integer
 
     Dim arr() As String
     arr = Split(Replace(Replace(Replace(strArray, "(", ""), ")", ""), " ", ""), ",")
 
     Dim v As Variant
     v = Application.Match(item, Application.Transpose(arr), 0)
     If IsError(v) Then
          IndexOf = -1
     Else
          IndexOf = v
     End If
 
End Function

Open in new window

0
andy7789Author Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.