• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 97
  • Last Modified:

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
0
Vijay P
Asked:
Vijay P
  • 2
  • 2
1 Solution
 
ShumsDistinguished Expert - 2017Commented:
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
0
 
Vijay PAuthor 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?
0
 
ShumsDistinguished Expert - 2017Commented:
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
0
 
Rossano PraderiIT ConsultantCommented:
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
0
 
Rossano PraderiIT ConsultantCommented:
...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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now