vba function to extract between two delimiters

[johnmerlino]

Hey all,

This function:

Mid( text, start_position, number_of_characters )


won't help if I want to extract between two delimiters like an & and &W:

abc def & abc xyz &W abc

For example:
pos = InStr(Range("A" & i), " &W ")
pos2 = InStr(Range("A" & i), " & ")
main_name = Mid(Range("A" & i).Value, pos2 + 3, pos)

will pull:

abc xyz &W abc

not:

abc xyz

because pos is not treated as end delimiter but rather the length of characters to pull, which in this case will be 18.

So I wonder what function would be suitable to extract:

abc xyz


Thanks for response

 

[RSXchin]

is the 2nd delimiter ALWAYS "&W"?

 

[RSXchin]

try something like this

Function Between_Ands(ByVal Full_String As String, ByVal First_Delimiter As String, ByVal Second_Delimiter As String) As String
Pos = InStr(Full_String, First_Delimiter)
pos2 = InStr(Full_String, Second_Delimiter)
If Pos = 0 Or pos2 = 0 Then
    Between_Ands = "Missing Delimiter"
    Exit Function
End If
Between_Ands = Mid(Full_String, Pos + Len(First_Delimiter), pos2 - (Pos + Len(First_Delimiter)))
End Function

 

[johnmerlino]

thanks for response

This line right here gives me invalid procedure call:

Mid(Range("A" & i).Value, pos + Len("&"), pos2 - (pos + Len("&")))

for this cell:

abc def & abc xyz &W abc

 

[RSXchin]

What was wrong with the code I gave you?

Maybe you didn't understand how to use the formula. Your input (on the worksheet) should look like

 =Between_Ands(A1,"&","&W")

 

[pgc01]

Hi

Another option:

main_name = Split(Split(Range("A" & i).Value, " &W ")(0), " & ")(1)