asked on

Excel formulas for extract (VBA)

Hi

I need excel formula or VBA.

I need extract between "MeContext=" and "," result should be in B1=CLWRFLSRBBU04124

and same need to be extract between "EFDD=" and "," result should be in C1=CLWRFLSRBB434411

A1=Frame=r,SubNetwork=Tam,MeContext=CLWRFLSRBBU04124,ME=1,EF=1,EFDD=CLWRFLSRBB434411,ER=8565
B1 = CLWRFLSRBBU04124
C1 = CLWRFLSRBB434411

Shums Faruk

Hi Vijay,

Try below in B1:

=MID($A1,SEARCH("MeContext=",$A1)+10,SEARCH(",ME=",$A1)-SEARCH("MeContext=",$A1)-10)

Open in new window

And below in C1:

=MID($A1,SEARCH("EFDD=",$A1)+5,SEARCH(",ER=",$A1)-SEARCH("EFDD=",$A1)-5)

Open in new window

Extract-Text-Between-Two-Text.xlsx

Vijay P

ASKER

Hi Shums,

Thanks you for your contribution, but in formula comma (,) only is constant in position of ",ME=" and ",ER=" , So ME= & ER= will be variable one.

So is possible to extract between "MeContext=" and "," only?

Shums Faruk

Hi Vijay,

You can extract 16 digit number after "MeContext=" with below formula:

=MID($A1,SEARCH("MeContext=",$A1)+10,16)

Open in new window

And 16 digit number after "EFDD=" with below formula:

=MID($A1,SEARCH("EFDD=",$A1)+5,16)

Open in new window

Extract-Text-Between-Two-Text_v2.xlsx

ASKER CERTIFIED SOLUTION

Member_2_6404472

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_6404472

...and if like to use VBA....

Function extract(ori As String, toExtract As String) As String
    Dim st() As String
    
    st = Split(ori, ",")
    For Each s In st
        If InStr(1, s, toExtract) > 0 Then
            extract = Split(s, "=")(1)
            Exit Function
        End If
    Next
End Function

Open in new window