Extract Value from Variable in String

[nguyenak]

Hi,

I am trying to create function that extracts the value from a variable on a given string:

For example


ColumnA: PropertyLocation:Type=email,Medium=5,Name=campaign 1

Result Goal:
ColumnB(Type): email
ColumnC(Medium): medium
ColumnD(Name): campaign1

I tried the following:

Function sMedium(dummystring As String) As String
Dim element As Variant
For Each element In Split(url, ",")
If Split(element, "=")(1) = "Medium" Then
sMedium = Split(element, "=")(1)
Exit Function

Else
sMedium = ""

Next element

End Function


There seems to be a comple error. If there is another approach, I would be interest in knowing.

[June7]

Try:

Function sMedium(URL As String) As String
Dim element As Variant, i As Integer
element = Split(URL, "=")
For i = 0 To UBound(element)
    If element(i) = "Medium" Then
        sMedium = element(i)
        Exit Function
    End If
Next
End Function

[nguyenak]

Sorry, for clarification,
Input: PropertyLocation:Type=email,Medium=5,Name=campaign 1


Result Goal:
ColumnB(Type): email
ColumnC(Medium): 5
ColumnD(Name): campaign1

the above code didnt work.

[June7]

You are trying to parse the value into 3 pieces in a query? Use an argument for the function to know which piece to return. Try:

Function ParseString(sString As String, ColID As String) As String
Dim element As Variant
element = Split(sString, ",")
Select Case ColID
    Case "B"
        ParseString = Mid(element(1),InStr(element(1),"=")+1)
    Case "C"
        ParseString = Mid(element(2),InStr(element(2),"=")+1)
    Case "D"
        ParseString = Mid(element(3),InStr(element(3),"=")+1)
End Select
End Function

Then call the function:
ParseString(input, "B")
ParseString(input, "C")
ParseString(input, "D")

OR

Function ParseString(sString As String, ColID As Integer) As String
Dim element As Variant
element = Split(sString, ",")
ParseString = Mid(element(ColID),InStr(element(ColID),"=")+1)
End Function

Then the function calls would be:
ParseString(input, 0)
ParseString(input, 1)
ParseString(input, 2)