Assistance with the InStr function

[aellistechsupport]

Hello,
So I can't figure out how to use the InStr in order to extract text between ":" and "for"

Started with this but I am getting NULL values

MemberType: Trim(Mid([Description],InStr(1,[Description],"Membership: ")+1,IIf(InStr(InStr(1,[Description],"Membership: ")+1,[Description],"Membership: ")=0,0,InStr(InStr(1,[Description],"Membership: ")+1,[Description],"Membership: ")-InStr(1,[Description],"Membership: "))))

Examples of the data set are as follows:
Membership: First Family for #6677890
Membership: LTS - Registration for #1736097
Membership: New Learn To Skate Member for #1733571
Membership: Renewing Learn To Skate Instructor for #1622065
Membership: Subsequent Family for #1739282

and so on

What's consistent:
1. all records start with "Membership: " so the first part of it would be after "Membership: "
2. all records have "for #" so before "for #"

Need to extract out what's in between "Membership: " and "for #" - see bolded and italicized parts from above

[JoeM]

Because "Membership: " is constant, you can simple use:

MemberType: Mid([Description],13,InStr([Description]," for #")-12)

[orange]

Try this for the Renewing example

Sub testInstr()
Dim s As String: s = "Membership: Renewing Learn To Skate Instructor for #1622065"
' below is based on Mid(s,start,end-start)   where end-start =length
Debug.Print Trim(Mid(s, InStr(s, ":") + 1, InStr(s, "for")-1 - InStr(s, ":") ))
End Sub

Result is Renewing Learn To Skate Instructor


Good luck

[aellistechsupport]

Because "Membership: " is constant, you can simple use:

MemberType: Mid([Description],13,InStr([Description]," for #")-12)

Thank you very much!
I just added TRIM