Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

Coding for this Thread
http://www.eileenslounge.com/viewtopic.php?f=30&t=35980
and this post
https://eileenslounge.com/viewtopic....279798#p279798

Full version:

Code:

---

Sub Test()  '    http://www.eileenslounge.com/viewtopic.php?f=30&t=35980
Dim Indx As Long ' the index of the element to be removed -  for this example it can be chosen to be  0 or  1 or  2 or  3  or  4
 Let Indx = 4

Dim arr1D() As Variant
 Let arr1D() = Array(1, 2, 3, 4, 5)
Dim Joint As String
 Let Joint = Join(arr1D(), "|"): Debug.Print Joint '                                      1|2|3|4|5 ' - make sure you use a seperator that does not appear in any array element
 Let Joint = "|" & Joint & "|": Debug.Print Joint '                                      |1|2|3|4|5| ' - needed so that I can get at the last and first element also
Dim CrackedJoint As String ' For cracked Joint
 ' I can use  Application.WorksheetFunction.Substitute  to pick out specific seperators , so I will replace the one before and after with some word like "Crack"
 Let CrackedJoint = Application.WorksheetFunction.Substitute(Joint, "|", "Crack2", Indx + 2): Debug.Print CrackedJoint ' |1|2|3|4|5Crack2 '  '  https://excelfox.com/forum/showthread.php/2230-Built-in-VBA-methods-and-functions-to-alter-the-contents-of-existing-character-strings
 Let CrackedJoint = Application.WorksheetFunction.Substitute(CrackedJoint, "|", "Crack1", Indx + 1): Debug.Print CrackedJoint '  |1|2|3|4Crack15Crack2
Dim Crack1 As Long, Crack2 As Long ' The positions of the cracks
 Let Crack1 = InStr(1, CrackedJoint, "Crack1", vbBinaryCompare): Debug.Print Crack1  '            9
 Let Crack2 = InStr(1, CrackedJoint, "Crack2", vbBinaryCompare): Debug.Print Crack2  '              16
Dim LeftBit As String, RightBit As String
 Let LeftBit = Left$(CrackedJoint, Crack1 - 1): Debug.Print LeftBit                  '    |1|2|3|4
 Let RightBit = "|" & Mid$(CrackedJoint, Crack2 + 6): Debug.Print RightBit          '            |
Dim JointedJoint As String
 Let JointedJoint = LeftBit & RightBit: Debug.Print JointedJoint                    '    |1|2|3|4|
 Let JointedJoint = Mid(JointedJoint, 2, Len(JointedJoint) - 2): Debug.Print JointedJoint ' 1|2|3|4
Dim arr1DOut() As String
 Let arr1DOut() = Split(JointedJoint, "|", -1, vbBinaryCompare)
' The above array is of element types of  String  , so we can't assign that to out original  Variant  type array.  We can convert with
 Let arr1D() = Application.Index(arr1DOut(), Evaluate("={1,1,1,1}"), Evaluate("={1,2,3,4}")) '  https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html#post5408376
 Let arr1D() = Application.Index(arr1DOut(), Evaluate("=Column(A:D)/Column(A:D)"), Evaluate("=Column(A:D)")) '  https://www.excelforum.com/excel-programming-vba-macros/1328703-copy-1-dim-array-to-and-2-dim-array.html#post5410028
 Let arr1D() = Application.Index(arr1DOut(), Evaluate("=Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")/Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")"), Evaluate("=Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")")) '

' or
 Let arr1D() = Application.Index(arr1DOut(), 1, 0) '

End Sub


---

“One liner ( almost ) “ versions

Code:

---


Sub Test2()
Dim Indx As Long
 Let Indx = 4

Dim arr1D() As Variant:  Let arr1D() = Array(1, 2, 3, 4, 5)
 Let arr1D() = Application.Index(Split(Mid(Left$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1), 2, Len(Left$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), _
 InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1)) - 2), "|"), Evaluate("=Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")/Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")"), Evaluate("=Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")")) '

End Sub






Sub Test3()
Dim Indx As Long
 Let Indx = 1

Dim arr1D() As Variant:  Let arr1D() = Array(1, 2, 3, 4, 5)
 Let arr1D() = Application.Index(Split(Mid(Left$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1), 2, Len(Left$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), _
 InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1)) - 2), "|"), Evaluate("=Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")/Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")"), Evaluate("=Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")")) '

End Sub

---

Function version

Code:

---

Sub testFunction()
Dim arr1D() As Variant:  Let arr1D() = Array(1, "2", 3, 4, 5)
 Let arr1D() = DeleteItem(arr1D(), 1)
End Sub

Function DeleteItem(ByVal Var As Variant, Indx As Long) As Variant
 Let DeleteItem = Application.Index(Split(Mid(Left$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1), 2, Len(Left$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), _
 InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1)) - 2), "|"), Evaluate("=Column(A:" & Split(Cells(1, UBound(Var)).Address, "$", -1, vbBinaryCompare)(1) & ")/Column(A:" & Split(Cells(1, UBound(Var)).Address, "$", -1, vbBinaryCompare)(1) & ")"), Evaluate("=Column(A:" & Split(Cells(1, UBound(Var)).Address, "$", -1, vbBinaryCompare)(1) & ")")) '
End Function


---

Coding for this Thread
http://www.eileenslounge.com/viewtopic.php?f=30&t=35980
and this post
https://eileenslounge.com/viewtopic....de06b5#p279861

Code:

---

Sub DeleteItemByIndexIn1DArraySHG1() '  http://www.eileenslounge.com/viewtopic.php?f=30&t=35980&p=279809#p279809    https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15218&viewfull=1#post15218
Dim Indx As Long '
 Let Indx = 1 ' 1 is for deleting the first element

Dim arr1D() As Variant
 Let arr1D() = Array(1, 2, 3, 4, 5)
Dim Joint As String
 Let Joint = Join(arr1D(), ","): Debug.Print Joint '                                          1,2,3,4,5 ' - make sure you use a seperator that does not appear in any array element

Dim Pos1 As Long, Pos2 As Long
 Let Pos1 = Evaluate("=Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))")
Debug.Print Pos1 '  1
 Let Pos2 = Evaluate("=Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))")
Debug.Print Pos2 '  3
Dim LeftBit As String, RightBit As String
 Let LeftBit = Left$("," & Joint, Pos1 - 1): Debug.Print LeftBit                              '  nothing there                  '
 Let LeftBit = Evaluate("=Left(""," & Joint & """, " & Pos1 - 1 & ")"): Debug.Print LeftBit    '  nothing there
 Let RightBit = "," & Mid$("," & Joint & ",", Pos2 + 1): Debug.Print RightBit                  ' ,2,3,4,5,
'  The  MID  spreadsheet function is less helpful since it must have the  third argument  ( in VBA MID the third length argument is optional
 Let RightBit = "," & Right$("," & Joint & ",", Len(Joint) - (Pos2 - 2)): Debug.Print RightBit ' ,2,3,4,5,    '  we don't want to take off the  ,  and  Joint is one less than  Joint & ","  so we take off in total  2  less
 Let RightBit = Evaluate("="",""&Right(""," & Joint & ","", Len(""" & Joint & """) - (" & Pos2 - 2 & "))")
Debug.Print RightBit '                                                                          ,2,3,4,5,    '

Rem Joining the two and trimming odff the leading and trailing seperators
Dim JointedJoint As String
'Let JointedJoint = LeftBit & RightBit: Debug.Print JointedJoint                          '      ,2,3,4,5,
'Let JointedJoint = Evaluate("=""" & LeftBit & RightBit & """"): Debug.Print JointedJoint '      ,2,3,4,5,
 Let JointedJoint = Evaluate("=" & """" & LeftBit & RightBit & """"): Debug.Print JointedJoint ' ,2,3,4,5,
 Let JointedJoint = Evaluate("=" & """" & LeftBit & ",""&Right(""," & Joint & ","", Len(""" & Joint & """) - (" & Pos2 - 2 & "))")
Debug.Print JointedJoint                                                                      ' ,2,3,4,5,
 Let JointedJoint = Evaluate("=Left(""," & Joint & """, " & Pos1 - 1 & ")&"",""&Right(""," & Joint & ","", Len(""" & Joint & """) - (" & Pos2 - 2 & "))")
Debug.Print JointedJoint                                                                      ' ,2,3,4,5,
 Let JointedJoint = Evaluate("=Left(""," & Joint & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Joint & ","", Len(""" & Joint & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))")
Debug.Print JointedJoint                                                                      ' ,2,3,4,5,
 
 'Let JointedJoint = Mid(JointedJoint, 2, Len(JointedJoint) - 2): Debug.Print JointedJoint '    2,3,4,5
'Debug.Print JointedJoint '                                                                      2,3,4,5
 'Let JointedJoint = Evaluate("=Mid(Left(""," & Joint & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Joint & ","", Len(""" & Joint & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2)),2,Len(Left(""," & Joint & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Joint & ","", Len(""" & Joint & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))))") '  Evaluate string  has 355 characters so it wont work
'Debug.Print JointedJoint
 Let JointedJoint = Mid(Evaluate("=Left(""," & Joint & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Joint & ","", Len(""" & Joint & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))"), 2, Len(Evaluate("=Left(""," & Joint & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Joint & ","", Len(""" & Joint & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))")) - 2)
Debug.Print JointedJoint                                                                      '  2,3,4,5

' replace  Joint  with  Join(arr1D(), ",")
 Let JointedJoint = Mid(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))"), 2, Len(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))")) - 2)
Debug.Print JointedJoint                                                                      '  2,3,4,5

' Get the string array back
Dim arr1DOut() As String
 Let arr1DOut() = Split(JointedJoint, ",", -1, vbBinaryCompare): Let arr1DOut() = Split(JointedJoint, ",")
 Let arr1DOut() = Split(Mid(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))"), 2, Len(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))")) - 2), ",")
' The spilt has returned  string  Elements, so we can't directly assign to the original array
' Let arr1D() = Application.Index(arr1DOut(), Evaluate("={1,1,1,1}"), Evaluate("={1,2,3,4}")) '  https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html#post5408376
' Let arr1D() = Application.Index(arr1DOut(), Evaluate("=Column(A:D)/Column(A:D)"), Evaluate("=Column(A:D)")) '  https://www.excelforum.com/excel-programming-vba-macros/1328703-copy-1-dim-array-to-and-2-dim-array.html#post5410028
' Let arr1D() = Application.Index(arr1DOut(), Evaluate("=Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")/Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")"), Evaluate("=Column(A:" & Split(Cells(1, UBound(arr1D())).Address, "$", -1, vbBinaryCompare)(1) & ")")) '

' or
' Let arr1D() = Application.Index(arr1DOut(), 1, 0) '  https://excelfox.com/forum/showthread.php/1111-VBA-Trick-of-the-Week-Slicing-an-Array-Without-Loop-%c3%a2%e2%82%ac%e2%80%9c-Application-Index
 Let arr1D() = Application.Index(Split(Mid(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))"), 2, Len(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))")) - 2), ","), 1, 0) '  Full workings: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15218&viewfull=1#post15218
 
 
End Sub


---

Or ....

Code:

---

Sub DeleteItemByIndexIn1DArraySHG2() '  http://www.eileenslounge.com/viewtopic.php?f=30&t=35980&p=279809#p279809    https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15218&viewfull=1#post15218
Dim Indx As Long '
 Let Indx = 1 '  1 is for deleting the first element
Dim arr1D() As Variant: Let arr1D() = Array(1, 2, 3, 4, 5)

 Let arr1D() = Application.Index(Split(Mid(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))"), 2, Len(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))")) - 2), ","), 1, 0) '  Full workings: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15218&viewfull=1#post15218
End Sub


---

Some explanations in next post

( In this example, Indx, is the Index number of the element that we want to remove starting from 1 – For the first element Indx must be given as 1

In Words , this is how the main code line works…( taking the example of wanting to remove the first element
My 1 D array , for example , {1,2,3,4,5} , is turned into a single text string, “1,2,3,4,5”. ( The separating thing, a comma in this case, is arbitrary. You should choose some character that is not likely to appear in any of your data.)
The next thing to do is add additional leading and trailing separating things ( commas in this example ) , so in the example it would then look like “,1,2,3,4,5,”

Now we use this sort of bit a lot.. Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx +1 & "))
Substitute is used to change the comma before the element you want to some other arbitrary separating thing.
So lets say we used a | and are wanting the first element ( Indx=1 ) removed. We then would have like
“|1,2,3,4,5,”
We then do a Find to get the position of that |
In other words,
The Substitute gives us this "|1,2,3,4,5,"
The Find looks for the | and gives us 1
Substitute is used again to change the comma after the element you want to some other arbitrary separating thing.
So lets say we used a | again. ( we are still wanting the first element) We then would have like
“,1|2,3,4,5,”
We then do a Find to get the position of that |
In other words,
The Substitute gives us this ",1|2,3,4,5,"
The Find looks for that | and gives us 3
Here is the last bit in close to the final code line:

Code:

---

Mid(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))"), 2, Len(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))")) - 2)

---

So we now know where the start and the end is of the element that we want to remove are
We can use this information to determine the string before, and to determine the string after, the element that we want to remove.
So we put those two strings together and that gives us the original string without the element that we want to remove.
Finally we Split that text back into an array

( Once again we will have all string elements out, regardless of what element types we have in our original array )

post for later use..
http://i.imgur.com/dol9tfQ.jpg
http://i.imgur.com/Ucpj9pZ.jpg
http://i.imgur.com/mtLzChH.jpg
http://i.imgur.com/N2PbR0C.jpg
http://i.imgur.com/Nzfnk90.jpg
http://i.imgur.com/rnYpqNh.jpg
http://i.imgur.com/RODuXQl.jpg
http://i.imgur.com/zpWglC9.jpg


Hello
I tried it , second one, http://i.imgur.com/wL6hN1c.jpg
It did not work

http://i.imgur.com/Z7bl5cc.jpg
http://i.imgur.com/NG2ICxa.jpg
http://i.imgur.com/EpDQOXB.jpg

Alan

post for later use-

post for later use

post for later use

Test...

MID( SUBSTITUTE(Text,Delim,REPT(" ",LEN(Text))) , (element*LEN(Text)) - (LEN(Text)-1) , LEN(Text) )

MID( SUBSTITUTE(Text,Delim,REPT(" ",LEN(Text))) , (element*LEN(Text)) - ( LEN(Text) ) , LEN(Text) )

Row\Col	A	B	C
1			What is pseudo is in the Cell to the left ( column B )
2	Example get the first thing, 1 from the Text string "1,3,5"	1,3,5	my original test text
3	Length	5	the length in characters of my original test text
4	( Rept " " ) x Length		5 spaces like "12345"
5	Substitute in the original string ( B2 ) 5 spaces for each comma seperator	1_____3_____5	like "1123453123455" is 13 characters
6	I apply to B5 the MID function starting at (1x5)-(5-1)=1 and for a length of 5	1____	like "11234"
7	I apply to B5 the MID function starting at (1x5)-(5)=0 and for a length of 5	#VALUE!	Excel doesn't forgive me for trying to start at 0 !!!!
8
9	Example get the second thing, 3 from the Text string "1,3,5"	1,3,5	my original test text
10	Length	5	the length in characters of my original test text
11	( Rept " " ) x Length		5 spaces like "12345"
12	Substitute in the original string ( B9 ) 5 spaces for each comma seperator	1_____3_____5	like "1123453123455" is 13 characters
13	I apply to B12 the MID function starting at (2x5)-(5-1)=6 and for a length of 5	_3___	like "13123"
14	I apply to B12 the MID function starting at (2x5)-(5)=5 and for a length of 5	__3__	like "12312"
15
16	Example get the third thing, 5 from the Text string "1,3,5"	1,3,5	my original test text
17	Length	5	the length in characters of my original test text
18	( Rept " " ) x Length		5 spaces like "12345"
19	Substitute in the original string ( B16 ) 5 spaces for each comma seperator	1_____3_____5	like "1123453123455" is 13 characters
20	I apply to B19 the MID function starting at (3x5)-(5-1)=11 and for a length of 5	__5	like "125" Note: I try to do length 5, but Excel forgives me and gives the 3 it has available
21	I apply to B19 the MID function starting at (1x5)-(5)=0 and for a length of 5	___5	like "1235" Note: I try to do length 5, but Excel forgives me and gives the 4 it has available

Row\Col	B
1
2	1,3,5
3	=LEN(B2)
4	=REPT(" ",B3)
5	=SUBSTITUTE(B2,",",B4)
6	=MID(B5,(1*B3)-(B3-1),B3)
7	=MID(B5,(1*B3)-(B3),B3)
8
9	1,3,5
10	=LEN(B9)
11	=REPT(" ",B10)
12	=SUBSTITUTE(B9,",",B11)
13	=MID(B12,(2*B10)-(B10-1),B10)
14	=MID(B12,(2*B10)-(B10),B10)
15
16	1,3,5
17	=LEN(B16)
18	=REPT(" ",B17)
19	=SUBSTITUTE(B16,",",B18)
20	=MID(B19,(3*B17)-(B17-1),B17)
21	=MID(B19,(3*B17)-(B17),B17)

Row\Col	A	B	C
7	I apply to B5 the MID function starting at (1x5)-(5)=0 and for a length of 5	#VALUE!	Excel doesn't forgive me for trying to start at 0 when using MID

Test...

MID( SUBSTITUTE(Text,Delim,REPT(" ",LEN(Text))) , (element*LEN(Text)) - (LEN(Text)-1) , LEN(Text) )

MID( SUBSTITUTE(Text,Delim,REPT(" ",LEN(Text))) , (element*LEN(Text)) - ( LEN(Text) ) , LEN(Text) )

Row\Col	A	B	C
1			What is pseudo is in the Cell to the left ( column B )
2	Example get the first thing, 1 from the Text string "1,3,5"	1,3,5	my original test text
3	Length	5	the length in characters of my original test text
4	( Rept " " ) x Length		5 spaces like "12345"
5	Substitute in the original string ( B2 ) 5 spaces for each comma seperator	1_____3_____5	like "1123453123455" is 13 characters
6	I apply to B5 the MID function starting at (1x5)-(5-1)=1 and for a length of 5	1____	like "11234"
7	I apply to B5 the MID function starting at (1x5)-(5)=0 and for a length of 5	#VALUE!	Excel doesn't forgive me for trying to start at 0 !!!!
8
9	Example get the second thing, 3 from the Text string "1,3,5"	1,3,5	my original test text
10	Length	5	the length in characters of my original test text
11	( Rept " " ) x Length		5 spaces like "12345"
12	Substitute in the original string ( B9 ) 5 spaces for each comma seperator	1_____3_____5	like "1123453123455" is 13 characters
13	I apply to B12 the MID function starting at (2x5)-(5-1)=6 and for a length of 5	_3___	like "13123"
14	I apply to B12 the MID function starting at (2x5)-(5)=5 and for a length of 5	__3__	like "12312"
15
16	Example get the third thing, 5 from the Text string "1,3,5"	1,3,5	my original test text
17	Length	5	the length in characters of my original test text
18	( Rept " " ) x Length		5 spaces like "12345"
19	Substitute in the original string ( B16 ) 5 spaces for each comma seperator	1_____3_____5	like "1123453123455" is 13 characters
20	I apply to B19 the MID function starting at (3x5)-(5-1)=11 and for a length of 5	__5	like "125" Note: I try to do length 5, but Excel forgives me and gives the 3 it has available
21	I apply to B19 the MID function starting at (1x5)-(5)=0 and for a length of 5	___5	like "1235" Note: I try to do length 5, but Excel forgives me and gives the 4 it has available

Row\Col	B
1
2	1,3,5
3	=LEN(B2)
4	=REPT(" ",B3)
5	=SUBSTITUTE(B2,",",B4)
6	=MID(B5,(1*B3)-(B3-1),B3)
7	=MID(B5,(1*B3)-(B3),B3)
8
9	1,3,5
10	=LEN(B9)
11	=REPT(" ",B10)
12	=SUBSTITUTE(B9,",",B11)
13	=MID(B12,(2*B10)-(B10-1),B10)
14	=MID(B12,(2*B10)-(B10),B10)
15
16	1,3,5
17	=LEN(B16)
18	=REPT(" ",B17)
19	=SUBSTITUTE(B16,",",B18)
20	=MID(B19,(3*B17)-(B17-1),B17)
21	=MID(B19,(3*B17)-(B17),B17)

Row\Col	A	B	C
7	I apply to B5 the MID function starting at (1x5)-(5)=0 and for a length of 5	#VALUE!	Excel doesn't forgive me for trying to start at 0 when using MID

post to use later