Get String between 2 strings, and the end of the parent string

Question

I need to extract a string from between 2 delimiters, to be specific, I have had to attach an email address to an attachment file name so that it can be processed and sent back to the sender. (This is within Outlook 2013 VBA)

The format of the file name is: ADDRjohnDOTdoeATfooDOTcomXADDRA646A10.FOO

My delimiters are ADDR and XADDR

What I need to do is split the string and return both the email string from between the delimiters and the file name after XADDR.

Both the email address and the file name can vary in length, the file extension is not fixed.

What is fixed is ADDR will always be the first 4 characters, and .XXX will be the last 3 characters.

Andrew Morton · Accepted Answer · 2017-06-21T09:53:44.440

You can use the InStr function to find where the second delimiter ("XADDR" in this case) starts, and the Mid function to extract the parts you want:

Sub InstrDemo()
    Dim s, delim1, delim2 As String
    s = "ADDRjohnDOTdoeATfooDOTcomXADDRA646A10.FOO"
    delim1 = "ADDR"
    delim2 = "XADDR"

    Dim pos As Integer
    pos = InStr(s, delim2)

    Dim part1, part2 As String
    part1 = Mid(s, Len(delim1) + 1, pos - Len(delim1) - 1)
    part2 = Mid(s, pos + Len(delim2))

    MsgBox (part1 & vbCrLf & part2)

End Sub

Outputs:

(I used Excel 2013 because it was easier at the time.)

It's better to put the delimiters in as strings like that because it avoids the use of "magic numbers".

Or you could use the Split function:

Sub SplitDemo()
    Dim s, delim1, delim2 As String
    s = "ADDRjohnDOTdoeATfooDOTcomXADDRA646A10.FOO"
    delim1 = "ADDR"
    delim2 = "XADDR"

    s = Mid(s, Len(delim1) + 1)
    Dim parts() As String
    parts = Split(s, delim2)

    ' optional: check that the correct number of parts were found...
    If UBound(parts) <> 1 Then
        MsgBox ("Wrong number of parts.")
    End If

    MsgBox (parts(0) & vbCrLf & parts(1))

End Sub

Which gives the same output as the previous method.

Thank you! one niggle, its left an X on the end of the email address string. — Digital Lightcraft, Jun 21 '17 at 09:47
Excellent, Thank you, got it working nicely, and also thenks for the 2nd option :-D — Digital Lightcraft, Jun 21 '17 at 09:56

score 1 · Answer 2 · answered Jun 21 '17 at 09:19

if you use regular expressions is quite easy to do, you can use this link as a reference How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

You can user a regular expression such as "ADDR(.*)XADDR", this will give you the email address

Get String between 2 strings, and the end of the parent string

2 Answers2