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

Microsoft OfficeVBAMicrosoft ExcelMicrosoft ApplicationsSpreadsheets

Last Comment

Member_2_6404472

8/22/2022 - Mon

Shums Faruk

1/6/2018

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

1/6/2018

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

1/6/2018

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

ASKER CERTIFIED SOLUTION

Member_2_6404472

1/7/2018

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Member_2_6404472

1/7/2018

...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