I'm trying to concatenate multiple strings using a few variables. The idea is, given a string 'x' in length, every 9 digits I want to separate; I do this with commas and single quotes in the concat function.
My issue is passing the previous concatenated string so I can add the next unique 9-digits to the end of it. Idea:
123456789987654321123456789 -> '123456789','987654321','123456789'
I'm able to do this in Excel but it requires too many cells, whereas I can do all the calculations in vba in memory then just spit out the final product. The formula that works is:
=CONCATENATE(**H27**,CONCATENATE(",",MID(E27,**10**,9,"'"))
Where: H27 (Want this to be a variable) Contains the previous concatenated iteration and will change after each step E27 contains the full length of the string - unchanged 10 (Want this to be a variable) Idea being this number changes/increases after each concat iteration
I would like to refer to the first concatenated iteration as Concat1 for example. Then ideally every time I iterate, my Concat1 becomes the previous Concat1 + The next 9 digits (+ some punctuation which isn't an issue). Unsure if this is possible or I'm just missing syntax, or if another method all together is needed. It makes sense to me that it should be something like:
Concat1 = Concat(Concat1 + Concat (,') + Original string, Step, 9, + (') )
Any help appreciated! More context/code can be provided to make things more clear if needed.
TLDR: I'm able to set the results of the 1st Concat to the variable Concat1
Dim Concat1 As Range
Range("H2").FormulaR1C1 = "=CONCATENATE(""'"",LEFT(RC\[-3\],9),""'"")"
Concat1 = Range("H2")
But then cant use this variable for following iterations. This doesn't work:
Range("Q2").FormulaR1C1 = "=CONCATENATE(Concat1,CONCATENATE("",'"",MID(RC\[-12\],10,9),""'""))"
