VBA code to extract Substring from Main String

Question

I am trying to extract substring from main string. String have not same pattern. Main string is in Column "I". Desired output should be as per column "J". I have to extract substring between "FL" and "WNG".

I have tried to write code put it is not giving proper output. Can you please assist with alternate solution to get desired output using VBA.

Sub Get_Substring()
    
Range("K2") = Mid(Range("I2"), InStrRev(Range("I2"), "FL") + 1, _
                                        InStrRev(Range("I2"), "WNG") - _
                                        InStrRev(Range("I2"), "FL") - 1)
End Sub

Another way? Debug.Print Split(Split(MyString, "REGNO")(1), "FL")(0) and Debug.Print "L" & Split(Split(Split(MyString, "REGNO")(1), "WNG")(0), "FL")(1) will give you 02 and L02 respectively. — Siddharth Rout
– Siddharth Rout, Commented May 30, 2021 at 5:44

score 4 · Accepted Answer · 2021-05-29 17:21:45Z

4

Try the following...

Range("K2") = Mid(Range("I2"), InStrRev(Range("I2"), "FL") + 2, _
                                        InStrRev(Range("I2"), "WNG") - _
                                        InStrRev(Range("I2"), "FL") - 2)

Although, I would make it clear that you want the value for each of the ranges, as follows...

Range("K2").Value = Mid(Range("I2").Value, InStrRev(Range("I2").Value, "FL") + 2, _
                                        InStrRev(Range("I2").Value, "WNG") - _
                                        InStrRev(Range("I2").Value, "FL") - 2)

edited May 29, 2021 at 17:21

answered May 29, 2021 at 17:16

user6585761

Sign up to request clarification or add additional context in comments.

Comments

FaneDuru · Accepted Answer · 2021-05-31 06:37:55Z

3

The next piece of code extracts the necessary string using arrays, too. But it can do it, even if more "WNG" strings exist in the string to be analyzed:

Private Function ExtractString(strTxt As String) As String
  Dim arrFL, arrWNG, i As Long

  arrFL = Split(strTxt, "FL")
  For i = 1 To UBound(arrFL)               'start from the second array element
        arrWNG = Split(arrFL(i), "WNG")   'split each first array element by "WNG"
        'if the array contains at least a "WNG" string:
        If UBound(arrWNG) > 0 Then ExtractString = arrWNG(0): Exit Function 'extract the first array element
  Next
End Function

Note: If more pairs "FL" folowed by "WNG" exists, the function can be adapted to return an array, containing all such potential occurrences...

It can be tested using the next testing Sub:

Sub testExtractString()
  Dim x As String
    x = "John12REGNO02FL02WNGARM01"
    'x = "John12WNGREGNO02FL02WNGARM01"
    'x = "John12WNGREGNO02FL02WNGARWNGM01"
   Debug.Print ExtractString(x)
End Sub

Just uncomment each x definition row...

edited May 31, 2021 at 6:37

answered May 29, 2021 at 19:49

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

1 Comment

T.M. Over a year ago

Straight forward +)

JvdV · Accepted Answer · 2021-05-31 06:26:34Z

I'll chuck in a solution based on regex to assure you got the exact substring:

Sub Test()

Dim stringIn As String: stringIn = "John12REGNO02FL02WNGARM01"
Debug.Print (Extract(stringIn))

End Sub

Function Extract(stringIn As String) As String

With CreateObject("vbscript.regexp")
    .Pattern = "^.*FL(.*?)WNG"
    If .Test(stringIn) = True Then
        Extract = .Execute(stringIn)(0).Submatches(0)
    Else
        Extract = "None Found"
    End If
End With

End Function

^ - Start line anchor.
.*FL - 0+ Chars greedy, and therefor untill, the last occurence of "FL".
(.*?) - A capture group with 0+ but lazy characters and therefor upto the nearest occurence of:
WNG - Literally match "WNG".

NOTE, you could make a more strict pattern only catching digits of that's the only type of characters possible, e.g: ^.*FL(\d*)WNG.

Here is an online demo

T.M. · Accepted Answer · 2021-05-29 18:29:31Z

2

You can try the following udf:

Public Function FLWNG(s As String) As String
'Purpose: get the substring enclosed by the most right pair of FL..WNG
    Dim tmp
    tmp = Split(Replace(s, "WNG", "FL"), "FL")
    FLWNG = tmp(UBound(tmp) - 1)
End Function

Explanation

Replacing all occurencies of WNG in the original string (s) with FL allows to split the resulting string by the FL delimiter only.

Assuming that the original string has at least one enclosing structure, you get the enclosed content as next to last element, i.e. via tmp(Ubound(tmp)-1).

edited May 29, 2021 at 18:29

answered May 29, 2021 at 17:22

T.M.

10k3 gold badges40 silver badges61 bronze badges

1 Comment

FaneDuru Over a year ago

Nice, as usually, voted it up. It splits perfectly the strings we can see. Hypothetically, if there are more "WNG" strings, it should be necessary to choose the next such string coming after "FL". I think, making a second array split by "WNG" and extract the first its element will better make the trick... I will try making such a function, even if it can be more complicated than the classical version using Mid and InStr, just for the sake of playing with arrays...:)

Collectives™ on Stack Overflow

VBA code to extract Substring from Main String

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related