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

**DocAElstein** · 05-13-2023, 01:49 PM

In support of this main forum post
https://eileenslounge.com/viewtopic.php?f=30&t=39654

Convert VBA Mathematical Expression to Excel spreadsheet form

Proposed solution type.
I would describe this solution type as very unclever. Its simple but tedious and complicated. Its just using simple string manipulation to change a VBA code mathematical string into the equivalent in Excel spreadsheet form.
The specific example given here would be a very limited solution only applicable to some specific forms, such as in the OPs given example:
s = "((7 > 5) And (10 < 15)) Or (Not (20 = 30))"
Here is the Excel spreadsheet form given by Hans
s = "OR(AND(7>5,10<15),NOT(20=30))"
, ( and also BigBen over at scrapoverflow
Evaluate("OR(AND(7>5,10<15),NOT(20=30))")
)

Here a full solution rambling development, then later a simplified function

This example type solution will be limited to a general form that will have one or more Ors applying to some mathematical logic that my include some Ands. This sort of form
( …… And …… ) Or ( ……. ) Or ( …… )
So similar to the OPs
((7 > 5) And (10 < 15)) Or (Not (20 = 30))

Coding description ( Coding here: https://www.excelfox.com/forum/showt...ll=1#post20038 )
In Rem 0 is some initial investigating into what we can and we cannot get away with in terms of having the final Excel spreadsheet form slightly different to the actual required one. Specifically we are looking at the effects of extra spaces in the Excel spreadsheet form. This is because the VBA string code line differs in some places to the Excel spreadsheet form in that it has some extra spaces.
We find that in some situations the extra spaces have no effect on the final calculation, so that is helpful in simplifying the conversion changes necessary.
The conclusion is then, we need to get rid of spacers after expressions as Excel does not seem to like those, so that is done in Rem 1

Rem 2
The crux of this limited solution is to first split by the Ors in the VBA string code line.
'2a) we then examine each of the Or bits, by that I mean the Or elements , or in other words Each of the OrbIts are the bracket bits from the general form
( …… And …… ) Or ( ……. ) Or ( …… )
In this limited solution example, we check for convert any Ands to the Excel spreadsheet form. The converted form is then used to overwrite the original VBA code line form.

Finally, in '2b) , the modified Or bits are reJoined

In the example we end up with
OR(AND((7 > 5) ,(10 < 15)) ,AND(NOT(20 = 30)))
, which is close enough to the actual required
OR(AND (7>5,10<15),NOT(20=30))
, such that in Rem 3 , it gives the correct result in Evaluate(" ")

That’s it!

Full coding and simplified function form in next post

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**DocAElstein** · 05-13-2023, 02:06 PM

Code:

'  https://eileenslounge.com/viewtopic.php?f=30&t=39654
Sub EvaluateVBAStringConversionConfusions()
Rem 0 some initial  investigating into what we can and we cannot get away with in terms of having the final Excel spreadsheet form slightly different to the actual required one. Specifically we are looking at the effects of extra spaces in the Excel spreadsheet form. This is because the VBA string code line differs in some places to the Excel spreadsheet form in that it has some extra spaces.
Dim EsVBA As String, EsExcel As String
 Let EsVBA = "((7 > 5) And (10 < 15)) Or (Not (20 = 30))"
 Let EsExcel = "OR(AND(7>5,10<15),NOT(20=30))"
Dim Res As Variant: Let Res = Evaluate(EsExcel)   '   Wahr  - OK
 Let EsExcel = "OR(AND(  7 >  5,10<15)  ,NOT(  20 =  30))"
 Let Res = Evaluate(EsExcel)                      '   Wahr  - OK
 Let EsExcel = "OR(AND(  7 >  5,10<15)  ,AND(NOT(  20 =  30)))"
 Let Res = Evaluate(EsExcel)                      '   Wahr  - OK
 
 Let EsExcel = "OR(AND (7>5,10<15),NOT(20=30))"
 Let Res = Evaluate(EsExcel)                      '  Error    .....   we need to get rid of spacers after expressions as Excel does not like those

Rem 1 we need to get rid of spacers after expressions as Excel does not seem to like those
Dim EsVBAinExcel As String
 Let EsVBAinExcel = Replace(EsVBA, "Or ", "OR", 1, -1, vbTextCompare) ' Note - TextCompare is important to make it not case sensitive
 Let EsVBAinExcel = Replace(EsVBAinExcel, "NOT ", "NOT", 1, -1, vbTextCompare)
 Let EsVBAinExcel = Replace(EsVBAinExcel, "and ", "AND", , , vbTextCompare)
' Or
 Let EsVBAinExcel = Replace(Replace(Replace(EsVBA, "Or ", "OR", , , vbTextCompare), "NOT ", "NOT", , , vbTextCompare), "and ", "AND", , , vbTextCompare)

Rem 2 Do the  Or   conversion
Dim SptOr() As String
 Let SptOr() = Split(EsVBAinExcel, "OR", -1, vbBinaryCompare)
' Or
 Let SptOr() = Split(EsVBAinExcel, "OR")  '    Binary compare is excact and it is default

'2a) Convert  And s  in each  Or
Dim OrbIt As Variant, CntOr As Long: Let CntOr = -1                              '  -1  allows us to tart at the indicie of zero when we increase by 1 at the start of each loop
    For Each OrbIt In SptOr()
     Let CntOr = CntOr + 1
    Dim SptAnd() As String
     Let SptAnd() = Split(OrbIt, "AND")
    Dim AndIt As Variant, strAnds As String, CntAnd As Long: Let CntAnd = -1     '  -1  allows us to tart at the indicie of zero when we increase by 1 at the start of each loop
         For Each AndIt In SptAnd()
          Let strAnds = strAnds & AndIt & ","
         Next AndIt
     Let strAnds = Left(strAnds, (Len(strAnds) - 1)) '  take off last comma
     Let strAnds = "AND" & strAnds
     Let SptOr(CntOr) = strAnds ' Change the OrbIt to the form that will work in Excel spreadsheet
     Let strAnds = "" '   Empty  strAnds  so that I can use the variable again in the next loop
    Next OrbIt
'    '2b) ' build up the final or
 Let EsVBAinExcel = Join(SptOr(), ",")
 Let EsVBAinExcel = "OR(" & EsVBAinExcel & ")"
' At this point we have                       OR(AND((7 > 5) ,(10 < 15)) ,AND(NOT(20 = 30)))
' , which is close enought to what we want      OR(AND (7>5,10<15),NOT(20=30))
Rem 3
 Debug.Print EsVBAinExcel
 Let Res = Evaluate(EsVBAinExcel)
End Sub


Sub FuncIt()
Dim Es As String: Let Es = "((7 > 5) And (10 < 15)) Or (Not (20 = 30))"   '    Yasser's example
Debug.Print VBAMathStringToSpreadsheetString(Es)                          '    OR(AND((7 > 5) ,(10 < 15)) ,AND(NOT(20 = 30)))
Debug.Print Evaluate(VBAMathStringToSpreadsheetString(Es))                '    True
End Sub

'  https://eileenslounge.com/viewtopic.php?f=30&t=39654
Function VBAMathStringToSpreadsheetString(ByVal EsVBA As String) As String
 Let EsVBA = Replace(Replace(Replace(EsVBA, "Or ", "OR", , , vbTextCompare), "NOT ", "NOT", , , vbTextCompare), "and ", "AND", , , vbTextCompare)
Dim SptOr() As String
 Let SptOr() = Split(EsVBA, "OR")
Dim OrbIt As Variant, CntOr As Long: Let CntOr = -1
    For Each OrbIt In SptOr()
     Let CntOr = CntOr + 1
    Dim AndIt As Variant, strAnds As String, CntAnd As Long: Let CntAnd = -1
         For Each AndIt In Split(OrbIt, "AND")
          Let strAnds = strAnds & AndIt & ","
         Next AndIt
     Let SptOr(CntOr) = "AND" & Left(strAnds, (Len(strAnds) - 1))
     Let strAnds = "" '
    Next OrbIt
 Let VBAMathStringToSpreadsheetString = "OR(" & Join(SptOr(), ",") & ")"
End Function

**DocAElstein** · 05-13-2023, 01:49 PM

**DocAElstein** · 02-13-2024, 03:54 AM

Some notes to go with this forum post
https://eileenslounge.com/viewtopic....314200#p314200
Thanks to ErikJan for this solution

Simple non VBA way to have in a single column the result of a multicolumn summation, and the numbers used in the summation formula, without having to keep the columns with the numbers used in the summation formula,

What we want

Let's say as example, I have this, some numbers in columns A – C, and I want to sum them.
https://i.postimg.cc/zDpr9TKJ/Number...n-column-D.jpg

_Row\^Col	A	B	C
1	10	11	20
2	11	12	21
3	12	13	22
4	13	14	23
5	14	15	24

I want to see the final summed result, but I also want to see the numbers I used to get the final sum. Preferably I want to see
_ the final summed result
and
_ the numbers used to get the result,
all in the same column, even if I delete columns A - C

So for example in the first row, I could do something like this in cell D1
= 10 + 11 + 20, which is a bit tedious,
or
= A1 + A2 + A3
Etc, That is OK, but if I delete columns A – C, then I’m fucked. I could copy column D and then Paste values back, so that when I delete columns A – C, I still have my result. But I have lost the information of what numbers were used in the summation, (assuming I had deleted columns A – C ) .

A solution, ( as done by ErikJan )
Here is a simple non VBA way to have in a single column the result of a multicolumn summation, and the numbers used in the summation formula, without having to keep the columns with the numbers used in the summation formula.

_ 1) Put this formula in column D
= "'=" & A1 & " + " & B1 & " + " & C1
( note the extra '), and
_2) then drag it down formula A1 & B1 & C1 in column D and drag down.JPG
https://i.postimg.cc/15tQP4rD/formul...-drag-down.jpg

_Row\^Col	D
1	= "'=" & A1 & " + " & B1 & " + " & C1
2	= "'=" & A2 & " + " & B2 & " + " & C2
3	= "'=" & A3 & " + " & B3 & " + " & C3
4	= "'=" & A4 & " + " & B4 & " + " & C4
5	= "'=" & A5 & " + " & B5 & " + " & C5

_3) Now Copy the column D
https://i.postimg.cc/C5vpkVSZ/Copy-column-D.jpg

Copy column D.JPG

_4) , and paste it back as values
https://i.postimg.cc/50SV9CrS/Paste-back-as-values.jpg

Paste back as values.JPG

_5) At this point, you could delete columns A- C if you wish
https://i.postimg.cc/QM0ytJbm/Delete-Columns-A-C.jpg Delete Columns A-C.JPG

_Row\^Col	A
1	'=10 + 11 + 20
2	'=11 + 12 + 21
3	'=12 + 13 + 22
4	'=13 + 14 + 23
5	'=14 + 15 + 24

So now you see the values you want summed.

_6) To get the values, do an Excel Find and Replace to remove the apostrophe'
https://i.postimg.cc/nzJxtk3L/Find-a...Apostrophe.jpg https://i.postimg.cc/cC1NvJyy/Founde...Apostrophe.jpg
Find and Replace to remove the Apostrophe.JPG

_7) If you wish to see the numbers used again, then you can hit the back button
https://i.postimg.cc/t48yByPW/Hit-ba...-summation.jpg

( Note: you could use any character, or characters rather than the apostrophe ' , but the apostrophe may have the advantage, depending on your settings or Excel versions, of not showing in the cells. I must investigate that further )

**DocAElstein** · 02-15-2024, 01:49 AM

Some notes to go with this forum post
https://eileenslounge.com/viewtopic....314200#p314200
https://eileenslounge.com/viewtopic....314281#p314281

Just for fun, the way I might do it.
Same test data, just to help compare offered solutions:
https://i.postimg.cc/NjzZxy0C/Test-d...n-column-D.jpg
Test data 5 rows of columns A - C to be summed in column D.jpg

I want in column D the summed values and also the numbers used in the summation , - I want the numbers used in the summation, for example, for future reference even if I delete columns A - Z

I would do this …..

Code:

Sub My2Euros() '  https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=23991&viewfull=1#post23991
 Let Selection = "=A1+B1+C1 & ""                     "" & A1 & "" "" & B1 & "" "" & C1"   '     This line is like putting this formula in cell D1    =A1+B1+C1 & "          " & A1 & " " & B1 & " " & C1         and dragging it down
 Let Selection = Selection.Value                                                          '     line changes the cell values to the value the formula gives, rather than the formula, ( this is necessary so that I can still everything as I initially do in the sum result column, even if I delete the columns A - C
End Sub

The first code line is like putting this formula in cell D1

Code:

=A1+B1+C1 & "                     " & A1 & " " & B1 & " " & C1

https://i.postimg.cc/wjvSJ59s/First-...1-A1-B1-C1.jpg
First code line is like this in D1 A1+B1+C1 & & A1 & & B1 & &.jpg

, and then dragging it down.

The second code line changes the cell values to the value the formula gives, rather than the formula, ( this is necessary so that I can still everything as I initially do in the sum result column, even if I delete the columns A - C

That way is just my preference for a few reasons:
_ Text and text storage in computers is, as I understand it as a computer Layman, cheap and easy these days, so having a lot of text anywhere idoes no noticeable harm or performance degradation
_ I would probably have things in the next column and my columns are not usually very wide., so I usually only see the results clearly, but if need be, to get full information ,I can extend the column , look in the formula bar or copy cells or a cell, etc.
https://i.postimg.cc/TPW36LNB/Extend...-full-info.jpg
Extended column or look in formula bar for full info.jpg

**DocAElstein** · 03-27-2024, 02:38 PM

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

Code:

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

Code:

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()

Code:

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()

Code:

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,

Code:

   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

Code:

   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 ,

Code:

   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

Code:

  '  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?)

**DocAElstein** · 03-27-2024, 02:38 PM

Some extended notes for replies to these main forum posts
https://eileenslounge.com/viewtopic....315705#p315705
https://eileenslounge.com/viewtopic....315689#p315689
This is post
https://www.excelfox.com/forum/showt...ll=1#post24103
https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24103&viewfull=1#post24103

Here is another set of TLDR versions. The SplitFilter one from snb has not changed too much. There is a new dic one from SpeakEasy

Code:



' ===================================

'    https://eileenslounge.com/viewtopic.php?p=315705#p315705
'    https://eileenslounge.com/viewtopic.php?p=315689#p315689


Sub BBCodeTagsURLb()  '     https://eileenslounge.com/viewtopic.php?p=315503#p315503  https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24092&viewfull=1#post24092
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

Rem 2 Find the URL if there is one
Dim strURL As String
    If InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then
     Let strURL = 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))
    Else
'     Let strURL = ""
    End If

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

End Sub
Sub SplitytySplit_TLDRb()    '  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
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
Sub SplitFilter_TLDRb() '    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
  '  If UBound(Filter(Split(strItAll, ", "), SelTxt, True, vbTextCompare)) > -1 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

Sub BBCodeTagsURLDictionaryb()
    Rem 0  The text you selected
Dim mydic As New Scripting.Dictionary  ' Early Binding referrence   https://i.postimg.cc/mgKt2QgN/Microsoft-Scripting-Runtime.jpg
Dim SelTxt As String, strURL As String
 Let SelTxt = UCase(Trim$(Selection.Text)) ' A text I highlighted in Word
    
Rem 1 Some groups of   name, URL,   pairs
' Eileen's Lounge
mydic.Add UCase("Eileen's Lounge"), "https://eileenslounge.com/app.php/portal"
mydic.Add UCase("eileenslounge"), "https://eileenslounge.com/app.php/portal"
mydic.Add UCase("The Windows Clipboard"), "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947"
' Excel Fox stuff
mydic.Add UCase("Excel Fox"), "https://www.excelfox.com/forum/forum.php"
mydic.Add UCase("excelfox"), "https://www.excelfox.com/forum/forum.php"
     
    
Rem 2 Find the URL if there is one
    If mydic.Exists(SelTxt) Then
     Let strURL = mydic(SelTxt)
  '  Else
    End If
    
Rem 3 Make the BB Code Tag URL thing
Call MakeABBCodeTagURL(strURL)
End Sub


























Sub MakeABBCodeTagURL(ByVal strURL As String) ' https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24092&viewfull=1#post24092
    With Selection
     .Text = " " & .Text & " "
     .Collapse Direction:=wdCollapseEnd
     .Font.Color = wdColorAutomatic
    End With
End Sub