Notes tests, string, manipulation of text files and string manipulations

[DocAElstein]

Some extended notes for this main forum post
https://eileenslounge.com/viewtopic....a7d27b#p315620
https://eileenslounge.com/viewtopic.php?p=315620&sid=922f8eb190547f727ca48c954aa7d27b#p315620
This is post
https://www.excelfox.com/forum/showt...l=1#post240923
https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24093&viewfull=1#post24093

---

snb's offerings, for Word VBA / VBA alternative for App Match of Excel VBA

Sub M_snb_000()   '   https://eileenslounge.com/viewtopic.php?p=315620#p315620
   sp = Split("aa_mm bb_nn cc_oo dd_pp ee_qq ff_rr gg_ss hh_tt ii_uu jj_vv kk_ww")
   
   c01 = "dd"
   MsgBox Split(Filter(sp, c01)(0), "_")(1)
End Sub

Similar to the last offering , its not working case insensitive for the key word, and for no match it will not work, in this case it will error
This time we have a simple modification to get the case insensitive, changing in the Filter( ) function,
Compare:=vbBinaryCompare
, to
Compare:= vbTextCompare
To stop the error and give me a "" instead if no match is found, I will once again use my If
InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then
, for ease of comparison.
( The coding also has the feature of working if only a portion of the key word(s) are selected )

What’s the basic idea
The use of the Filter( ) function gives a very nice simple way to get at the pair, from which it is child’s play to get the second bit, the URL.
This coding also takes the pair as a pair, which is preferable for me.


Here is a full open ed up code version of the basic snb offering

Sub SplitFilterM_snb_000()  '    https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24094&viewfull=1#post24094

Dim strItAll As String, strEileen As String, strFox As String
 Let strEileen = "Eileen's Lounge_https://eileenslounge.com/app.php/portal, eileenslounge_https://eileenslounge.com/app.php/portal, The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947, "
 Let strFox = "Excel Fox_https://www.excelfox.com/forum/forum.php, excelfox_https://www.excelfox.com/forum/forum.php, "
 
 Let strItAll = strEileen & strFox

Dim SptstrItAll() As String
 Let SptstrItAll() = Split(strItAll, ", ", -1, vbBinaryCompare)  ' 1 D array of pairs like  eileenslounge_https://eileenslounge.com/app.php/portal    The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947,

Dim Excample As String
 Let Excample = "ox"   '  an example  word key

Dim FltIt As String  ' This should return the wanted pair -   The  Filter( )   function returns a subset array, ( of one element in this case ) , the element will be that incuding the search text,  Excample        The  (0) is the first element , the only one
 Let FltIt = Filter(SptstrItAll(), Excample, Include:=True, Compare:=vbBinaryCompare)(0)

Dim wantedURL As String
 Let wantedURL = Split(FltIt, "_", -1, vbBinaryCompare)(1): Debug.Print wantedURL ' gives https://www.excelfox.com/forum/forum.php    the second element of the found pair string is the URL

End Sub

This next version puts it in a form more comparable with my full instr version, Sub InstrIt()

Sub SplitFilter() '    https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24094&viewfull=1#post24094      https://eileenslounge.com/viewtopic.php?p=315620#p315620
Rem 0  The text you selected
Dim SelTxt As String
 Let SelTxt = Selection.Text ' A text I highlighted in Word

Rem 1 Some groups of   name_URL   pairs
Dim strItAll As String, strEileen As String, strFox As String
 Let strEileen = "Eileen's Lounge_https://eileenslounge.com/app.php/portal, eileenslounge_https://eileenslounge.com/app.php/portal, The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947, "
 Let strFox = "Excel Fox_https://www.excelfox.com/forum/forum.php, excelfox_https://www.excelfox.com/forum/forum.php, "
 
 Let strItAll = strEileen & strFox

Dim SptstrItAll() As String
 Let SptstrItAll() = Split(strItAll, ", ", -1, vbBinaryCompare)  ' 1 D array of pairs like  eileenslounge_https://eileenslounge.com/app.php/portal    The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947,

Rem 2 Find the URL but this will error if there is not one
Dim FltIt As String  ' This should return the wanted pair -   The  Filter( )   function returns a subset array, ( of one element in this case ) , the element will be that incuding the search text,  Excample        The  (0) is the first element , the only one
 Let FltIt = Filter(SptstrItAll(), SelTxt Include:=True, Compare:=vbTextCompare)(0)
Dim strURL As String
 Let strURL = Split(FltIt, "_", -1, vbBinaryCompare)(1)

Rem 3 Make the BB Code Tag URL thing
Call MakeABBCodeTagURL(strURL)
End Sub

And here is TLDR version to try and make a better comparison to my shortest version so far, Sub BBCodeTagsURL()

Sub SplitFilter_TLDR() '    https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24094&viewfull=1#post24094      https://eileenslounge.com/viewtopic.php?p=315620#p315620
Rem 0  The text you selected
Dim SelTxt As String
 Let SelTxt = Selection.Text ' A text I highlighted in Word

Rem 1 Some groups of   name_URL   pairs
Dim strItAll As String, strEileen As String, strFox As String
 Let strEileen = "Eileen's Lounge_https://eileenslounge.com/app.php/portal, eileenslounge_https://eileenslounge.com/app.php/portal, The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947, "
 Let strFox = "Excel Fox_https://www.excelfox.com/forum/forum.php, excelfox_https://www.excelfox.com/forum/forum.php, "
 
 Let strItAll = strEileen & strFox

                                                                                        'Dim SptstrItAll() As String
                                                                                        ' Let SptstrItAll() = Split(strItAll, ", ")  ' 1 D array of pairs like  eileenslounge_https://eileenslounge.com/app.php/portal    The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947,
Rem 2 Find the URL if there is one
Dim strURL As String
    If InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then
     Let strURL = Split(Filter(Split(strItAll, ", "), SelTxt, True, vbTextCompare)(0), "_")(1)
    Else
     Let strURL = ""
    End If
Rem 3 Make the BB Code Tag URL thing
Call MakeABBCodeTagURL(strURL)
End Sub

Conclusions
The main coding line
Split(Filter(Split(strItAll, ", "), SelTxt, True, vbTextCompare)(0), "_")(1)
looks nice compared with mine
Mid(strItAll, InStr(InStr(1, strItAll, SelTxt, vbTextCompare), strItAll, "http", vbBinaryCompare), InStr(InStr(InStr(1, strItAll, SelTxt, vbTextCompare), strItAll, "http", vbBinaryCompare), strItAll, ",", vbBinaryCompare) - InStr(InStr(1, strItAll, SelTxt, vbTextCompare), strItAll, "http", vbBinaryCompare))

In addition, I like the way one enters an additional key word_Url pair in one go

---

Edit: A slight modification in a follow up post, … ( https://eileenslounge.com/viewtopic....315705#p315705 )
This,

   sp = Split("aa_mm bb_nn cc_oo dd_pp ee_qq ff_rr gg_ss hh_tt ii_uu jj_vv kk_ww")
   
   c01 = "dd"
   MsgBox Split(Filter(sp, c01)(0), "_")(1)

, Changed to

   sp = Split("aa_mm bb_nn cc_oo dd_pp ee_qq ff_rr DD_ss hh_tt Dd_uu jj_vv kk_ww")
   
   c01 = "DD"
   c02 = ""
   sn = Filter(sp, c01,,1)
   If UBound(sn) > -1 Then c02 = Split(Filter(sp, c01,,1)(0), "_")(1)   
   MsgBox c02

For consistency / less confusingly comparison/ and to make the comparisons generally that I am doing a bit easier, we can say you changed this ,

   sp = Split("aa_mm bb_nn cc_oo dd_pp ee_qq ff_rr gg_ss hh_tt ii_uu jj_vv kk_ww")
   
   c01 = "dd"
   MsgBox Split(Filter(sp, c01)(0), "_")(1)

, to this

  '  c02 = ""
   sp = Split("aa_mm bb_nn cc_oo dd_pp ee_qq ff_rr DD_ss hh_tt Dd_uu jj_vv kk_ww")
   
   c01 = "DD"
   If UBound(Filter(sp, c01, , 1)) > -1 Then
   MsgBox Split(Filter(sp, c01, , Compare:=vbTextCompare)(0), "_")(1)      '  See Settings  https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filter-function
   End If

So you
_(i) use the Compare:=vbTextCompare in the filter function as I did to get the case insensibility,
_(ii) The If UBound(Filter(sp, c01, , 1)) > -1 is effectively an alternative to my If InStr(1, strItAll, SelTxt, vbTextCompare) > 0
Specifically in my TLDR versions , this line would be that alternative, If UBound(Filter(Split(strItAll, ", "), SelTxt, True, vbTextCompare)) > -1
_(iiI) you noticed, as SpeakEasy did, that I had an unnecessary Else bit to make the URL string "" for no match, since the variable would be already at that "" ( I am not sure why you did the c02 = "" though? Perhaps because as you don’t generally declare your variables. Whilst that does not seem to give us problems in the demo coding so far, maybe it could in some other variation of the coding – I mean there might be a situation where the use of an undefined variable might cause Excel not to take it as a zero length string, "" , but rather, it might take it as something else which might then chuck a spanner in the works?)