If we compare the 3 non Evaluate code lines, we can see that we have got the length down quite noticeably, so there is a chance it may work fairly well
Code: Select all
Application.Index(Split(Mid(Left$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1), 2, Len(Left$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1)) - 2), "|"), 1, 0) '
Application.Index(Split(Mid(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Right$("," & Join(arr1D(), ",") & ",", Len(Join(arr1D(), ",")) - (InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") - 2)), 2, Len(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Right$("," & Join(arr1D(), ",") & ",", Len(Join(arr1D(), ",")) - (InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") - 2))) - 2), ","), 1, 0)
Application.Index(Split(Mid(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Mid$("," & Join(arr1D(), ",") & ",", InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") + 1), 2, Len(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Mid$("," & Join(arr1D(), ",") & ",", InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") + 1)) - 2), ","), 1, 0)
Maybe some time later if I get a few more versions then I will do some speed tests and report back
If anyone else has any versions or practical data to test it on, then please post them and I will add them to my tests.