What would be the best way to combine an unknown number of text variables; variable1, variable2, variable3 etc. into one variable with 'space' between them? The fact that I don't know the exact number of variables beforehand makes it a little challenging, at least to me.
Okay, I have a solution, and it even works beyond 9. This macro is a subroutine. You call it with the prefix of your variable names, and it returns the result.
If your variables are "Variable1", "Variable2", and "Variable3" containing the strings "One", "Two" and "Three", your result will be "One Two Three". Give it a try.
The loop is currently set to 10, but you should probably increase that to whatever your maximum value might be. I probably could have coded that differently, to end when a blank value appears.
While you can do this -- @Airy has shown a method, and there's a section on "indirection" in the Wiki -- it can get complicated. So your first question should be "Do I need to do this?".
The easier way, more suitable nearly all the time, is to have a single variable containing a number of elements -- an array or Dictionary, for example. How you do that will depend on the data you are going to store, so
For just numbers you could have a comma-separated list
For text without any line-breaks you could have a return-delimited list
For things that have a "label" and a "value" -- cakes and prices are a favourite example, for some reason! -- you can use a Dictionary
And there are other ways available too.
How are you getting an "unknown number of variables" in the first place? That in itself may show the simplest way to go.