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

Printable View

Show 40 post(s) from this thread on one page

06-01-2022, 01:25 AM
DocAElstein

Some extra stuff for this post
https://eileenslounge.com/viewtopic.php?f=27&t=38243

later

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
06-13-2022, 02:09 PM
DocAElstein

In support of this main forum Thread
https://excelfox.com/forum/showthrea...nto-excel-cell
_1) create new number and place in cell B1 according to last serial number in csv file,

Before
ExcelFile:
_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )

_Row\^Col
A

B

C

1
Serial# : TTR0000001

2
TTR0000002

3
TTR0000003

4

Worksheet: Sheet1

Text File:

Code:

TTR0000001 TTR0000002 TTR0000003

Run macro:

Code:

Sub NewSN() ' Rem 1 Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "serial_number.csv" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Rem 2 determine a new number '2a Current number If Right(TotalFile, 2) = vbCr & vbLf Then Let TotalFile = Left(TotalFile, Len(TotalFile) - 2) ' Take off last line feed Dim PosLstLineFeed As Long: Let PosLstLineFeed = InStrRev(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) Dim CrntNmbr As String: Let CrntNmbr = Mid(TotalFile, PosLstLineFeed + 2) Let CrntNmbr = Replace(CrntNmbr, "TTR", "", 1, -1, vbBinaryCompare) '2b creat new number Let CrntNmbr = CrntNmbr + 1 Let CrntNmbr = Format(CrntNmbr, "0000000") Let CrntNmbr = "TTR" & CrntNmbr Rem 3 Put new number in Excel file Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1) Dim LrB As Long: Let LrB = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row Let Ws1.Range("B" & LrB + 1 & "").Value = CrntNmbr End Sub

After:

_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )

_Row\^Col
A

B

C

1
Serial# : TTR0000001

2
TTR0000002

3
TTR0000003

4
TTR0000004

5

Worksheet: Sheet1
06-13-2022, 02:43 PM
DocAElstein

In support of this main forum Thread
https://excelfox.com/forum/showthrea...nto-excel-cell
_2) save new serial number to csv file below last numbers.

Before :
text File

Code:

TTR0000001 TTR0000002 TTR0000003

Excel File
_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )

_Row\^Col
A

B

C

1
Serial# : TTR0000001

2
TTR0000002

3
TTR0000003

4
TTR0000004

5

Worksheet: Sheet1

Run this macro

Code:

' https://excelfox.com/forum/showthread.php/2797-find-last-alphanumeric-row-of-txt-file-and-fill-into-excel-cell Sub SaveLatestSNinTextFile() Rem 1 Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "serial_number.csv" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Rem 2 get latest serial nimber from Excel file Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1) Dim LrB As Long: Let LrB = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row Dim CrntNmbr As String: Let CrntNmbr = Ws1.Range("B" & LrB & "").Value Rem 3 add latest serial number to text file ' 3a add a new line and the latest serial number to the string of the entire file If Right(TotalFile, 2) = vbCr & vbLf Then Let TotalFile = Left(TotalFile, Len(TotalFile) - 2) Let TotalFile = TotalFile & vbCr & vbLf & CrntNmbr ' ' 3b replace the text file with the new string Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Open PathAndFileName For Output As #FileNum2 Print #FileNum2, TotalFile ' write out entire text file Close #FileNum2 End Sub

After:

Code:

TTR0000001 TTR0000002 TTR0000003 TTR0000004

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg. 9f6hAjkC0ct9f8jleOui-u
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
09-14-2022, 12:07 PM
DocAElstein

In support of this main forum Thread post:
https://excelfox.com/forum/showthrea...ll=1#post16695

content in one line input reduced sample.txt https://app.box.com/s/grrxh1rl372pzp2exn6em00ovhj4qhih
content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos

https://i.postimg.cc/yYdStNqH/Before...ced-sample.jpg

What’s those two text files got in them?:
Using the Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv

, and using this Calling macro

Code:

' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16696&viewfull=1#post16696 Sub SantaComing() ' Rem 1 Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "content in one line input reduced sample.txt" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) End Sub

Here is the results, for example from the immediate window :

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems " & vbCr & vbLf & "in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short " & vbCr & vbLf & "stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & vbCr & vbLf & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story " & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that " & vbCr & vbLf & "the story has somehow wormed"

Here are the corresponding results if I do the same to look at the after file ( content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos )

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed"

So, what do we conclude?:
_ 1) there are no tab characters
_ 2) It seems that the line separator is the commonly used 2 character pair of a carriage return and a line feed, ( In VBA coding, vbCr & vbLf )
_ 3) It seems like the character string we wish to remove, ( the extra new line forming characters ) are three character:
A space and a carriage return and a line feed. ( In VBA coding _ " " & vbCr & vbLf _ )
( _ 4) I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I will do that!)

This initial macro seems to do the required:

Code:

Sub ReplaceInTextFileThreeCharacters__Space_vbCr_vbLf__WithA__Space__() ' https://excelfox.com/forum/showthread.php/2817-Make-all-text-file-content-in-one-line-if-a-space-found ' Rem 1 Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "content in one line input reduced sample.txt" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Rem 2 remove " " & vbCr & vbLf ' Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, "", 1, -1, vbBinaryCompare) Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, " ", 1, -1, vbBinaryCompare) ' I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I do that! Rem 3 Make new file Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "new text file.txt" Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Open PathAndFileName For Output As #FileNum2 Print #FileNum2, TotalFile ' write out entire text file Close #FileNum2 End Sub

Here is the corresponding output from my function for the new text file made

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed" & vbCr & vbLf

That looks very similar to the output requested. Here is the new text file made:
new text file.txt https://app.box.com/s/w2zydwa20lr3jyxuk8z7ddjgpl0xrcvn

SantaClawsIsComing.xls https://app.box.com/s/7ken6nf050xd4xqwhxu3av1yebdb74dr
09-14-2022, 12:07 PM
DocAElstein

In support of this main forum Thread post:
https://excelfox.com/forum/showthrea...ll=1#post16695

content in one line input reduced sample.txt https://app.box.com/s/grrxh1rl372pzp2exn6em00ovhj4qhih
content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos

https://i.postimg.cc/yYdStNqH/Before...ced-sample.jpg

What’s those two text files got in them?:
Using the Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv

, and using this Calling macro

Code:

' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16696&viewfull=1#post16696 Sub SantaComing() ' Rem 1 Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "content in one line input reduced sample.txt" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) End Sub

Here is the results, for example from the immediate window :

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems " & vbCr & vbLf & "in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short " & vbCr & vbLf & "stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & vbCr & vbLf & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story " & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that " & vbCr & vbLf & "the story has somehow wormed"

Here are the corresponding results if I do the same to look at the after file ( content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos )

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed"

So, what do we conclude?:
_ 1) there are no tab characters
_ 2) It seems that the line separator is the commonly used 2 character pair of a carriage return and a line feed, ( In VBA coding, vbCr & vbLf )
_ 3) It seems like the character string we wish to remove, ( the extra new line forming characters ) are three character:
A space and a carriage return and a line feed. ( In VBA coding _ " " & vbCr & vbLf _ )
( _ 4) I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I will do that!)

This initial macro seems to do the required:

Code:

Sub ReplaceInTextFileThreeCharacters__Space_vbCr_vbLf__WithA__Space__() ' https://excelfox.com/forum/showthread.php/2817-Make-all-text-file-content-in-one-line-if-a-space-found ' Rem 1 Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "content in one line input reduced sample.txt" ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... ' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length 'Get #FileNum, , TotalFile ' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782 Let TotalFile = Input(LOF(FileNum), FileNum) Close #FileNum Rem 2 remove " " & vbCr & vbLf ' Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, "", 1, -1, vbBinaryCompare) Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, " ", 1, -1, vbBinaryCompare) ' I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I do that! Rem 3 Make new file Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "new text file.txt" Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Open PathAndFileName For Output As #FileNum2 Print #FileNum2, TotalFile ' write out entire text file Close #FileNum2 End Sub

Here is the corresponding output from my function for the new text file made

Code:

Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed" & vbCr & vbLf

That looks very similar to the output requested. Here is the new text file made:
new text file.txt https://app.box.com/s/w2zydwa20lr3jyxuk8z7ddjgpl0xrcvn

SantaClawsIsComing.xls https://app.box.com/s/7ken6nf050xd4xqwhxu3av1yebdb74dr
09-29-2022, 01:33 PM
DocAElstein

Some notes in support of this forum main post
https://excelfox.com/forum/showthrea...ll=1#post16712

An erroring formula: https://i.postimg.cc/nzbWtGTQ/An-erroring-formula.jpg
https://i.postimg.cc/mcjjYMvb/An-erroring-formula.jpg

Highlight a section, such as the Match section https://i.postimg.cc/5NxZ0pJB/Highlight-match.jpg
https://i.postimg.cc/K4H9pPRj/Highlight-match.jpg

Hit key F9 https://i.postimg.cc/7P1FwdFP/Hit-ke...esult-is-5.jpg
https://i.postimg.cc/Thw7c72B/Hit-ke...esult-is-5.jpg

This reveals that we have 5 , or in total , 5 + 1 = 6

So in the VLookUp we are looking at column 6 in the range given by bk
The names manager tells us which range we want:
Second formula section is the range bk https://i.postimg.cc/JhBFg23L/range-bk.jpg
https://i.postimg.cc/zL8pHpPt/range-bk.jpg

Similarly we can investigate the first section in the erroring formula
Highlight first formula section https://i.postimg.cc/902syY7j/Highli...la-section.jpg

Hit key F9 https://i.postimg.cc/wvLStG3V/Hiut-F...st-section.jpg

This reveals that the Look up value, the value that you are looking for is "DMG1"

Further investigations by trial and error , reveals that some character combinations in cell A2 cause the error. But I do not know why yet ?

Examples:
Not work:
https://i.postimg.cc/YqbTmg2J/d-not-work.jpg
https://i.postimg.cc/d0zbVYQ3/d-MG1-not-work.jpg
Works
https://i.postimg.cc/3xq6tTv1/MG1-works.jpg

In fact, it seems that some character combinations are not allowed as the Look Up value generally , for example try another cell, and I can find a character combination that does not work
https://i.postimg.cc/SK204shH/Not-wo...ters-in-A6.jpg
https://i.postimg.cc/Ff4Cgh1x/Not-wo...ters-in-A6.jpg

I am puzzled.

In fact if you look in detail at the results you are getting when it does appear to work, then they are not alwaysthe correct values.
https://postimg.cc/kBnt3Zzg
https://i.postimg.cc/kBnt3Zzg/Wrong-...m-VLook-Up.jpg https://i.postimg.cc/MKB0RJYV/Wrong-...m-VLook-Up.jpg

Very strange. I am even more puzzled
?????

Update Answer from Sandy https://excelfox.com/forum/showthrea...ll=1#post16717

Quote:

Originally Posted by sandy666

with formula =VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,0) value $1.55 is returned and so on
..just forgot comma on the end or define last argument 0, VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,) so he need to learn how to use VLOOKUP function
VLOOKUP require all arguments, even if last argument is omitted there should be defined place for it after last comma

https://i.postimg.cc/ncHVbyD8/ThatsIt.gif
https://i.postimg.cc/15VpN7Hj/ThatsIt.jpg https://i.postimg.cc/N5Yy34Z6/ThatsIt.jpg
( I thought I had checked that, but missed something somewhere, I don’t know why I missed that, maybe I think also I need to learn how to use VLoopUp properly! )
09-29-2022, 01:33 PM
DocAElstein

Some notes in support of this forum main post
https://excelfox.com/forum/showthrea...ll=1#post16712

An erroring formula: https://i.postimg.cc/nzbWtGTQ/An-erroring-formula.jpg
https://i.postimg.cc/mcjjYMvb/An-erroring-formula.jpg

Highlight a section, such as the Match section https://i.postimg.cc/5NxZ0pJB/Highlight-match.jpg
https://i.postimg.cc/K4H9pPRj/Highlight-match.jpg

Hit key F9 https://i.postimg.cc/7P1FwdFP/Hit-ke...esult-is-5.jpg
https://i.postimg.cc/Thw7c72B/Hit-ke...esult-is-5.jpg

This reveals that we have 5 , or in total , 5 + 1 = 6

So in the VLookUp we are looking at column 6 in the range given by bk
The names manager tells us which range we want:
Second formula section is the range bk https://i.postimg.cc/JhBFg23L/range-bk.jpg
https://i.postimg.cc/zL8pHpPt/range-bk.jpg

Similarly we can investigate the first section in the erroring formula
Highlight first formula section https://i.postimg.cc/902syY7j/Highli...la-section.jpg

Hit key F9 https://i.postimg.cc/wvLStG3V/Hiut-F...st-section.jpg

This reveals that the Look up value, the value that you are looking for is "DMG1"

Further investigations by trial and error , reveals that some character combinations in cell A2 cause the error. But I do not know why yet ?

Examples:
Not work:
https://i.postimg.cc/YqbTmg2J/d-not-work.jpg
https://i.postimg.cc/d0zbVYQ3/d-MG1-not-work.jpg
Works
https://i.postimg.cc/3xq6tTv1/MG1-works.jpg

In fact, it seems that some character combinations are not allowed as the Look Up value generally , for example try another cell, and I can find a character combination that does not work
https://i.postimg.cc/SK204shH/Not-wo...ters-in-A6.jpg
https://i.postimg.cc/Ff4Cgh1x/Not-wo...ters-in-A6.jpg

I am puzzled.

In fact if you look in detail at the results you are getting when it does appear to work, then they are not alwaysthe correct values.
https://postimg.cc/kBnt3Zzg
https://i.postimg.cc/kBnt3Zzg/Wrong-...m-VLook-Up.jpg https://i.postimg.cc/MKB0RJYV/Wrong-...m-VLook-Up.jpg

Very strange. I am even more puzzled
?????

Update Answer from Sandy https://excelfox.com/forum/showthrea...ll=1#post16717

Quote:

Originally Posted by sandy666

with formula =VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,0) value $1.55 is returned and so on
..just forgot comma on the end or define last argument 0, VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,) so he need to learn how to use VLOOKUP function
VLOOKUP require all arguments, even if last argument is omitted there should be defined place for it after last comma

https://i.postimg.cc/ncHVbyD8/ThatsIt.gif
https://i.postimg.cc/15VpN7Hj/ThatsIt.jpg https://i.postimg.cc/N5Yy34Z6/ThatsIt.jpg
( I thought I had checked that, but missed something somewhere, I don’t know why I missed that, maybe I think also I need to learn how to use VLoopUp properly! )
10-02-2022, 06:21 PM
DocAElstein

This is post https://excelfox.com/forum/showthrea...6727#post16727
#post16727

In support of this forum Thread: https://excelfox.com/forum/showthrea...6726#post16726
https://excelfox.com/forum/showthrea...6718#post16718

I want to analyse the text in cells A2, A5, B7, and B10.
I can use my Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv
In conjunction with this short macro

Code:

Option Explicit ' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16727&viewfull=1#post16727 ' https://excelfox.com/forum/showthread.php/2818-in-VBA-if-the-given-string-is-found-then-delete-everything-between-two-newlines-where-the-string-appears Sub WotsEeGot() Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A2").Value2) ' https://pastebin.com/raw/eutzzxHv Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A5").Value2) Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B7").Value2) Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B10").Value2) End Sub

Here are some of the results

Code:

"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string" "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string"

So it looks like most important info to glean is that the line separator is as conventional in an Excel cell, vbLf
10-02-2022, 06:21 PM
DocAElstein

This is post https://excelfox.com/forum/showthrea...ll=1#post19610
#post19610
It was copied initially before I edited it from the post above, #post16727 , and that #post16727 stayes yellow highlighted after the copy

Some note in support of this main forum post
https://eileenslounge.com/viewtopic....303644#p303644

„WieGehtsYouTubeServerChrome.txt“ https://app.box.com/s/a7k2izgyzqhd7f98hlaq9csw0l4tyyl6
10-02-2022, 06:21 PM
DocAElstein

This is post https://excelfox.com/forum/showthrea...6727#post16727
#post16727

In support of this forum Thread: https://excelfox.com/forum/showthrea...6726#post16726
https://excelfox.com/forum/showthrea...6718#post16718

I want to analyse the text in cells A2, A5, B7, and B10.
I can use my Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv
In conjunction with this short macro

Code:

Option Explicit ' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16727&viewfull=1#post16727 ' https://excelfox.com/forum/showthread.php/2818-in-VBA-if-the-given-string-is-found-then-delete-everything-between-two-newlines-where-the-string-appears Sub WotsEeGot() Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A2").Value2) ' https://pastebin.com/raw/eutzzxHv Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A5").Value2) Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B7").Value2) Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B10").Value2) End Sub

Here are some of the results

Code:

"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string" "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." "in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string"

So it looks like most important info to glean is that the line separator is as conventional in an Excel cell, vbLf
10-02-2022, 06:21 PM
DocAElstein

hhhhkh

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
10-02-2022, 06:21 PM
DocAElstein

hhhhkh

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
05-13-2023, 01:49 PM
DocAElstein

for later use

https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwplzlpYpmRqjGZem14AaABAg. 9hrvbYRwXvg9ht4b7z00X0
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgyOGlCElBSbfPIzerF4AaABAg. 9hrehNPPnBu9ht4us7TtPr
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwHjKXf3ELkU4u4j254AaABAg. 9hr503K8PDg9ht5mfLcgpR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw1-OyZiDDxCHM2Rmp4AaABAg.9hqzs_MlQu-9ht5xNvQueN
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg. 9htWqRrSIfP9i-fyT84gqd
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg. 9htWqRrSIfP9i-kIDl-3C9
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i57J9GEOUB
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i58MGeM8Lg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i59prk5atY
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwaWs6XDXdQybNb8tZ4AaABAg. 9i5yTldIQBn9i7NB1gjyBk
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxV9eNHvztLfFBGsvZ4AaABAg. 9i5jEuidRs99i7NUtNNy1v
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugx2zSXUtmLBSDoNWph4AaABAg. 9i3IA0y4fqp9i7NySrZamd
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9i7Qs8kxEqH
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9i7TqGQYqTz
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAJSNws8Zz
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAJvZ6kmlx
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAK0g1dU7i
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKCDqNmnF
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKHVSTGHy
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKSBKPcJ6
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKgL6lrcT
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKlts8hKZ
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKrX7UPP0
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAL5MSjWpA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
05-13-2023, 01:49 PM
DocAElstein

for later use

https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwplzlpYpmRqjGZem14AaABAg. 9hrvbYRwXvg9ht4b7z00X0
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgyOGlCElBSbfPIzerF4AaABAg. 9hrehNPPnBu9ht4us7TtPr
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwHjKXf3ELkU4u4j254AaABAg. 9hr503K8PDg9ht5mfLcgpR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw1-OyZiDDxCHM2Rmp4AaABAg.9hqzs_MlQu-9ht5xNvQueN
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg. 9htWqRrSIfP9i-fyT84gqd
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg. 9htWqRrSIfP9i-kIDl-3C9
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i57J9GEOUB
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i58MGeM8Lg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i59prk5atY
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwaWs6XDXdQybNb8tZ4AaABAg. 9i5yTldIQBn9i7NB1gjyBk
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxV9eNHvztLfFBGsvZ4AaABAg. 9i5jEuidRs99i7NUtNNy1v
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugx2zSXUtmLBSDoNWph4AaABAg. 9i3IA0y4fqp9i7NySrZamd
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9i7Qs8kxEqH
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9i7TqGQYqTz
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAJSNws8Zz
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAJvZ6kmlx
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAK0g1dU7i
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKCDqNmnF
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKHVSTGHy
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKSBKPcJ6
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKgL6lrcT
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKlts8hKZ
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKrX7UPP0
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAL5MSjWpA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
05-13-2023, 01:49 PM
DocAElstein

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
05-13-2023, 01:49 PM
DocAElstein

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
05-13-2023, 02:06 PM
DocAElstein

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

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
ttps://www.youtube.com/watch?v=LP9fz2DCMBE
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
ttps://www.youtube.com/watch?v=bFxnXH4-L1A
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
ttps://www.youtube.com/watch?v=GqzeFYWjTxI
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
05-13-2023, 02:06 PM
DocAElstein

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

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
ttps://www.youtube.com/watch?v=LP9fz2DCMBE
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
ttps://www.youtube.com/watch?v=bFxnXH4-L1A
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
ttps://www.youtube.com/watch?v=GqzeFYWjTxI
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

_Row\^Col	A	B
1	Serial# :	TTR0000001
2		TTR0000002
3		TTR0000003
4

_Row\^Col	A	B
1	Serial# :	TTR0000001
2		TTR0000002
3		TTR0000003
4		TTR0000004
5

_Row\^Col	A	B
1	Serial# :	TTR0000001
2		TTR0000002
3		TTR0000003
4		TTR0000004
5

5 Attachment(s)

non VBA way to have in a column the result of a multicolumn summation, and the numbers summed

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 https://i.postimg.cc/D4QZRdjs/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 Attachment 5757
https://i.postimg.cc/15tQP4rD/formul...-drag-down.jpg https://i.postimg.cc/zbKXRF1M/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 https://i.postimg.cc/GTntMSQd/Copy-column-D.jpg Attachment 5758

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

_5) At this point, you could delete columns A- C if you wish
https://i.postimg.cc/QM0ytJbm/Delete-Columns-A-C.jpg Attachment 5760 https://i.postimg.cc/2bZ37fn0/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
Attachment 5761 https://i.postimg.cc/dkffXwV4/Founde...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 https://i.postimg.cc/zyw9fsxG/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 )

_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

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

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

5 Attachment(s)

non VBA way to have in a column the result of a multicolumn summation, and the numbers summed

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 https://i.postimg.cc/D4QZRdjs/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 Attachment 5757
https://i.postimg.cc/15tQP4rD/formul...-drag-down.jpg https://i.postimg.cc/zbKXRF1M/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 https://i.postimg.cc/GTntMSQd/Copy-column-D.jpg Attachment 5758

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

_5) At this point, you could delete columns A- C if you wish
https://i.postimg.cc/QM0ytJbm/Delete-Columns-A-C.jpg Attachment 5760 https://i.postimg.cc/2bZ37fn0/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
Attachment 5761 https://i.postimg.cc/dkffXwV4/Founde...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 https://i.postimg.cc/zyw9fsxG/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 )

02-15-2024, 01:49 AM
DocAElstein

3 Attachment(s)

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
Attachment 5763 https://i.postimg.cc/21nHkShn/Test-d...n-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
Attachment 5764 https://i.postimg.cc/nCyRfDjH/First-...1-A1-B1-C1.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
Attachment 5766 https://i.postimg.cc/FfN4bR4x/Extend...-full-info.jpg
02-15-2024, 01:49 AM
DocAElstein

3 Attachment(s)

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
Attachment 5763 https://i.postimg.cc/21nHkShn/Test-d...n-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
Attachment 5764 https://i.postimg.cc/nCyRfDjH/First-...1-A1-B1-C1.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
Attachment 5766 https://i.postimg.cc/FfN4bR4x/Extend...-full-info.jpg
03-27-2024, 02:38 PM
DocAElstein

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

This particular post is intended just to reproduce , for later clarity , some things in the original main forum post

At this point in the proceedings I had got to approximately these posts, or thereabouts . https://eileenslounge.com/viewtopic....315596#p315596
https://eileenslounge.com/viewtopic....315512#p315512 and the coding I was using was in **essence this following, ( ** changed just slightly to make comparisons with the next two post , ( https://www.excelfox.com/forum/showt...ll=1#post24094 https://www.excelfox.com/forum/showt...ll=1#post24093 ) , easier

Those last two Eileen’s Lounge links, https://eileenslounge.com/viewtopic....315596#p315596
https://eileenslounge.com/viewtopic....315512#p315512 , sum up well what I was doing and this coding version of mine is a good summary of a shortened version of my main coding ideas, ( I have farmed out the BB Code tag making part to another routine, as that is not the main point of the current discussions. The main point of the current discussions is the getting of the appropriate URL to match a highlight key word in Microsoft Word)

Code:

Sub BBCodeTagsURL_() ' 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 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 = "[URL=" & strURL & "] " & .Text & " [/url]" .Collapse Direction:=wdCollapseEnd .Font.Color = wdColorAutomatic End With End Sub
03-27-2024, 02:38 PM
DocAElstein

Some extended notes for this main forum post
https://eileenslounge.com/viewtopic....a7d27b#p315620
https://eileenslounge.com/viewtopic.php?p=315620&sid=922f8eb190547f727ca48c9 54aa7d27b#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:

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

Code:

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

Code:

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

Code:

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
)
03-27-2024, 02:38 PM
DocAElstein

1 Attachment(s)

Some extended notes for this main forum post
https://eileenslounge.com/viewtopic....a7d27b#p315620
https://eileenslounge.com/viewtopic.php?p=315620&sid=922f8eb190547f727ca48c9 54aa7d27b#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?)
03-27-2024, 02:38 PM
DocAElstein

1 Attachment(s)

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
03-27-2024, 09:32 PM
DocAElstein

1 Attachment(s)

Some extended notes for replies to these main forum posts
https://eileenslounge.com/viewtopic....315849#p315849
https://eileenslounge.com/viewtopic....315754#p315754
This is post
https://www.excelfox.com/forum/showt...ll=1#post24094
https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24094&viewfull=1#post24094

Another snb offering , ( a couple ),- so lets take a look at snb’s dic first, not necessarily a pretty sight, but never mind.

Code:

Sub snb_dic() ' To use a dictionary I would write it this way: https://eileenslounge.com/viewtopic.php?p=315849#p315849 c00 = Selection With CreateObject("scripting.dictionary") .Item(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal" .Item(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal" .Item(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947" .Item(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php" .Item(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php" MsgBox .Item(UCase(c00)) End With End Sub Sub snb_docv() ' In Word you can also use the docvariables: https://eileenslounge.com/viewtopic.php?p=315849#p315849 c00 = Selection With ThisDocument .Variables(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal" .Variables(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal" .Variables(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947" .Variables(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php" .Variables(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php" MsgBox .Variables(UCase(c00)) End With End Sub

First, snb either noticed, or didn’t, that a If dic.Exists(SelTxt) Then is never needed. The same goes for SpeakEasy’s dic, so let’s first get those two up in a better comparison for, snb’s and SpeakEasy’s dic

Code:

' https://eileenslounge.com/viewtopic.php?p=315849#p315849 Sub snb_dicc_TLDR() ' https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24094&viewfull=1#post24094 Rem 0 The text you selected Let SelTxt = UCase(Trim$(Selection.Text)) ' A text I highlighted in Word Rem 1 Some groups of name, URL, pairs With CreateObject("scripting.dictionary") ' Eileen's Lounge .Item(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal" .Item(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal" .Item(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947" ' Excel Fox stuff .Item(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php" .Item(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php" Rem 2 Find the URL if there is one Dim strURL As String Let strURL = .Item(SelTxt) End With Rem 3 Make the BB Code Tag URL thing Call MakeABBCodeTagURL(strURL) End Sub

Code:

Sub BBCodeTagsURLDictionaryd() 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 Key:=UCase("Eileen's Lounge"), Item:="https://eileenslounge.com/app.php/portal" mydic.Add Key:=UCase("eileenslounge"), Item:="https://eileenslounge.com/app.php/portal" mydic.Add Key:=UCase("The Windows Clipboard"), Item:="https://www.eileenslounge.com/viewtopic.php?p=300947#p300947" ' Excel Fox stuff mydic.Add Key:=UCase("Excel Fox"), Item:="https://www.excelfox.com/forum/forum.php" mydic.Add Key:=UCase("excelfox"), Item:="https://www.excelfox.com/forum/forum.php" Rem 2 Find the URL if there is one Dim strURL As String Let strURL = mydic(SelTxt) Rem 3 Make the BB Code Tag URL thing Call MakeABBCodeTagURL(strURL) End Sub

Maybe the main diffference now is that the filling looks just slightly tidier

_.________________________________________________ _________________________________________________

Code:

Sub snb_docv() ' In Word you can also use the docvariables: https://eileenslounge.com/viewtopic.php?p=315849#p315849 c00 = Selection With ThisDocument .Variables(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal" .Variables(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal" .Variables(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947" .Variables(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php" .Variables(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php" MsgBox .Variables(UCase(c00)) End With End Sub

The document variable looked initially interesting. It seems to be like a simplified dictionary. The disadvantage here seems to be that if the document variable does not exist, then attempting to get that non-existent variable, will error. There is no simple way, for example there is no equivalent of the dictionary If dic.Exists(SelTxt) Then
Probably error handling would be the only simple way to do a check. We usually all frown a bit on error handling, if we can do without it. So unless this document variable way has significant other advantages that I don’t know about, then, whilst it certainly is interesting to know about it, I think, for now, it does not get on my short list, or at least low down the list. Shame as my initial thoughts were that it might be some sort of word dedicated and optimised type of dictionary. So then I would have had it all neatly in in word.
I wonder if possibly Error handling in VBA has a different reputation in Word as Excel? My opinion of it as a bad thing comes from partly from the average smarter Excel person than me in who more often than not says it’s a bad thing if you can find a way to do without it.

Anyway, this would be the equivalent short coding equivalent for the document variable way

Code:

' https://eileenslounge.com/viewtopic.php?p=315849#p315849 Sub snb_docvc_TLDR() ' https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24094&viewfull=1#post24094 Dim strURL As String, SelTxt As String Rem 0 The text you selected Let SelTxt = UCase(Trim$(Selection.Text)) ' A text I highlighted in Word Rem 1 Some groups of name, URL, pairs With ThisDocument ' Eileen's Lounge .Variables(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal" .Variables(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal" .Variables(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947" ' Excel Fox stuff .Variables(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php" .Variables(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php" Rem 2 Find the URL if there is one On Error Resume Next Let strURL = .Variables(SelTxt) On Error GoTo 0 End With Rem 3 Make the BB Code Tag URL thing Call MakeABBCodeTagURL(strURL) End Sub

Something like this would add the two latest snb offerings to the main bit to make comparisons from, at least as regards the main working bit of the coding ( correcting SpeakEasy’s dic to remove the If mydic.Exists( bit

Code:

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)) If InStr(1, Wdkey, SelTxt, vbTextCompare) > 0 Then Let strURL = Split(URLs, ",")(UBound(Split(Split(Wdkey, SelTxt)(0), ","))) 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) Let strURL = mydic(SelTxt) ' SpeakEasy dic Let strURL = .Item(SelTxt) ' snb dic On Error Resume Next Let strURL = .Variables(SelTxt) ' document variable way On Error GoTo 0

Not so much difference with Speakeasy’s dic or snb’s dic. People’s opinions differ a bit about the With CreateObject( being good because you do away with an object variable. Personally I am less keen most of the time with the With and End With pair, but in some uses I quite like it, the With CreateObject( being one of them

The document variable way is new to me, so I will reserve judgment on that one just now. Perhaps smarter Word experts passing might at some time have a comment on it or advice about it?
03-27-2024, 09:32 PM
DocAElstein

HCKJ<C<CHCH

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg. ADd4m2zp_xDADd6Nnotj1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984
https://eileenslounge.com/viewtopic.php?f=30&t=41610
https://eileenslounge.com/viewtopic.php?p=322176#p322176
https://eileenslounge.com/viewtopic.php?p=322238#p322238
https://eileenslounge.com/viewtopic.php?p=322270#p322270
https://eileenslounge.com/viewtopic.php?p=322300#p322300
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086
https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851
http://www.eileenslounge.com/viewtopic.php?p=322084#p322084
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
03-29-2024, 07:01 PM
DocAElstein

LATER

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=316441#p316441
https://eileenslounge.com/viewtopic.php?p=324736#p324736
https://eileenslounge.com/viewtopic.php?p=324990#p324990
https://eileenslounge.com/viewtopic.php?f=27&t=41937&p=325485#p325485
https://eileenslounge.com/viewtopic.php?p=325609#p325609
https://eileenslounge.com/viewtopic.php?p=325610#p325610
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdPM65i9PG
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdPQHFk_zm
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg. ADd4m2zp_xDADd6Nnotj1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
https://www.youtube.com/watch?v=fEHKPhJxgBA&lc=Ugxx8_MjhC9FDaQgcHN4AaABAg
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg. ACGbG9c76OWACGbjKa7H8k
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg
https://www.youtube.com/watch?v=GyPHaydeng0&lc=UgzE4a4f_e_y9Rk5OR94AaABAg
https://www.youtube.com/watch?v=I5FkNG94BcQ&lc=UgxXnkEHqulXSR5tXwh4AaABAg
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa6BSa173Z
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa6-64Xpgl
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa5ms39yjd
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa5ZXJwRCM
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa4Pr15NUt
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa4I83JelY
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdMo2n-hyF
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
03-29-2024, 07:01 PM
DocAElstein

Later

_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

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

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

Show 40 post(s) from this thread on one page

All times are GMT +5. The time now is 03:09 AM.

ExcelFox is Not Associated With Microsoft®