We help IT Professionals succeed at work.

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
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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

Author

Commented:
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?
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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
Hi,

you can extract the text with this formula:
=SUBSTITUTE(LEFT(MID($A$1,FIND(A3,$A$1)+LEN(A3),LEN($A$1)-FIND(A3,$A$1)),FIND(",",MID($A$1,FIND(A3,$A$1)+LEN(A3),LEN($A$1)-FIND(A3,$A$1)))),",","")

Open in new window


The string to be find have to end with a comma otherwise this formula doesn't work.

Extract a specific text from a string
...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


Function Usage example