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

'Option Explicit
Sub M_snb() ' https://eileenslounge.com/viewtopic.php?f=26&t=40789&p=315620#p315620
   c00 = "aa bb cc dd ee ff gg hh ii jj kk"           '  Word(s) Keys like       "Excel Fox, Eilen's Lounge
   sp = Split("mm nn oo pp qq rr ss tt uu vv ww")     '  1 D array of URLs like    "https://eileenslounge.com, https://www.excelfox.com
   
   c01 = "dd"                   '  an example word key
   MsgBox sp(UBound(Split(Split(c00, c01)(0))))
End Sub

It don’t work quite as I wanted it to:-
_ It is not case insensitive on the key word, - my fault I did not make that clear. I can get over that by doing some UCase( )
_ It returns a (wrong) answer instead of a "" if it does not find the word key. I will have to add a check for that. Probably the way my coding does that, something of this form will do, and make comparing of the codings easier,
If InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then

What’s the basic idea
There are some useful ideas hidden in it.
The main one is that splitting a text string by the thing you are looking for, ( along with a few other things ), as another way to get the position of it, so like a sort of alternative to InStr way to get at position something in a string

This is an opened up version of Sub M_snb()

Sub SplitytySplit_M_snb_()   '    https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24093&viewfull=1#post24093
Dim WdEileen As String, WdFox As String
 Let WdEileen = "Eileen's Lounge,eileenslounge,The Windows Clipboard,"
 Let WdFox = "Excel Fox,excelfox,"
Dim Wdkey As String
 Let Wdkey = UCase(WdEileen & WdFox)                   '  word(s) Keys like       "Excel Fox, Eilen's Lounge

Dim URLEileen As String, URLFox As String
 Let URLEileen = "https://eileenslounge.com/app.php/portal,https://eileenslounge.com/app.php/portal,https://www.eileenslounge.com/viewtopic.php?p=300947#p300947,"
 Let URLFox = "https://www.excelfox.com/forum/forum.php,https://www.excelfox.com/forum/forum.php,"
Dim URLs As String
 Let URLs = URLEileen & URLFox
Dim SptURLs() As String
 Let SptURLs() = Split(URLs, ",", -1, vbBinaryCompare)  ' 1 D array of URLs like    "https://eileenslounge.com, https://www.excelfox.com
 
Dim Excample As String
 Let Excample = UCase("Excel fox")   '  an example  word key

Dim SptOff As String    '  The next few lines give a sort of  InStr  alternative way  to "get at position something in a string"
 Let SptOff = Split(Wdkey, Excample, -1, vbBinaryCompare)(0)
Dim nth As Long '  This effectively gives us a number which is the Index of both the word key and the URL
 Let nth = UBound(Split(SptOff, ",", -1, vbBinaryCompare))
 Let nth = Len(SptOff) - Len(Replace(SptOff, ",", "", 1, -1, vbBinaryCompare))
 
Dim wantedURL As String
 Let wantedURL = SptURLs(nth): Debug.Print wantedURL ' gives https://www.excelfox.com/forum/forum.php
End Sub

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

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

Rem 1 Some groups of   name, URL,   pairs
Dim WdEileen As String, WdFox As String
 Let WdEileen = "Eileen's Lounge,eileenslounge,The Windows Clipboard,"
 Let WdFox = "Excel Fox,excelfox,"
Dim Wdkey As String
 Let Wdkey = UCase(WdEileen & WdFox)                   '  word(s) Keys like       "Excel Fox, Eilen's Lounge

Dim URLEileen As String, URLFox As String
 Let URLEileen = "https://eileenslounge.com/app.php/portal,https://eileenslounge.com/app.php/portal,https://www.eileenslounge.com/viewtopic.php?p=300947#p300947,"
 Let URLFox = "https://www.excelfox.com/forum/forum.php,https://www.excelfox.com/forum/forum.php,"
Dim URLs As String
 Let URLs = URLEileen & URLFox
Dim SptURLs() As String
 Let SptURLs() = Split(URLs, ",", -1, vbBinaryCompare)  ' 1 D array of URLs like    "https://eileenslounge.com, https://www.excelfox.com
 
Rem 2 Find the URL , but only  works  if there is one
Dim SptOff As String    '  The next few lines give a sort of  InStr  alternative way  to "get at position something in a string"
 Let SptOff = Split(Wdkey, SelTxt, -1, vbBinaryCompare)(0)
Dim nth As Long '  This effectively gives us a number which is the Index of both the word key and the URL
 Let nth = UBound(Split(SptOff, ",", -1, vbBinaryCompare))
 Let nth = Len(SptOff) - Len(Replace(SptOff, ",", "", 1, -1, vbBinaryCompare))
 Let strURL = SptURLs(nth)
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 SplitytySplit_TLDR()   '  https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24093&viewfull=1#post24093
Rem 0  The text you selected
Dim SelTxt As String
 Let SelTxt = UCase(Selection.Text) ' A text I highlighted in Word in  UCase  as part of way to get the key text search case insensitive

Rem 1 Some groups of   name, URL,   pairs
Dim WdEileen As String, WdFox As String
 Let WdEileen = "Eileen's Lounge,eileenslounge,The Windows Clipboard,"
 Let WdFox = "Excel Fox,excelfox,"
Dim Wdkey As String
 Let Wdkey = UCase(WdEileen & WdFox)                   '  word(s) Keys like       "Excel Fox, Eilen's Lounge

Dim URLEileen As String, URLFox As String
 Let URLEileen = "https://eileenslounge.com/app.php/portal,https://eileenslounge.com/app.php/portal,https://www.eileenslounge.com/viewtopic.php?p=300947#p300947,"
 Let URLFox = "https://www.excelfox.com/forum/forum.php,https://www.excelfox.com/forum/forum.php,"
Dim URLs As String
 Let URLs = URLEileen & URLFox
                            'Dim SptURLs() As String
                            ' Let SptURLs() = Split(URLs, ",")  ' 1 D array of URLs like    "https://eileenslounge.com, https://www.excelfox.com
 
Rem 2 Find the URL if there is one, or make   ""   if no match
Dim strURL As String
    If InStr(1, Wdkey, SelTxt, vbTextCompare) > 0 Then
     Let strURL = Split(URLs, ",")(UBound(Split(Split(Wdkey, SelTxt)(0), ",")))
    Else
     Let strURL = ""
    End If
Rem 3 Make the BB Code Tag URL thing
Call MakeABBCodeTagURL(strURL)
End Sub

Conclusion
Rem 2 is probably the best part to compare. The main coding line
Split(URLs, ",")(UBound(Split(Split(Wdkey, SelTxt)(0), ",")))
looks a bit better in snb’s than 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))


A lesser point possibly is that adding more pairs is slightly more difficult, and prone to mistakes in snb’s as the key word and the URL go in different places.

(I noticed something new, or better said something I overlooked: Both mine and snb’s coding here will get you a URL string likely to be the one you want if you select only part of the text and then run the coding.
In these examples, for example, if selecting
Excel FoX
, and running the coding got you this in the Microsoft Word document,
[URL=https://www.excelfox.com/forum/forum.php] Excel FoX [/url]
(, so in the final forum post you would get this Excel FoX )

, then selecting
oX
, and running the coding would get you this in the Microsoft Word document
Excel F[URL=https://www.excelfox.com/forum/forum.php]oX [/url]
(, and in the final forum post you would get this
Excel FoX
I suspect that would be more useful than not, but I might change my opinion as time goes on and the number of key word , URL link pairs increases, as that might increase the occurrence of the coding getting the wrong URL, as there will be more likelyhood of a short text being found in more key words
)