VBA: position of substring in a comma-delimited string

Posted on 2009-07-06
Last Modified: 2013-11-26
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


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

Question by:andy7789
LVL 53

Accepted Solution

Dhaest earned 250 total points
ID: 24783703
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

LVL 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 250 total points
ID: 24783753
If you are using VBA within Excel, you can use the below function, which also shows usage.

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


          IndexOf = v

     End If

End Function

Open in new window


Author Comment

ID: 24784092
Thank you

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now