Creating a series of numbers from LEN

[babycody]

If LEN() returns 8 how could I create a series of numbers from that to use in an array like this {1,2,3,4,5,6,7,8}? This one has me completely stumped. I need this to use in a formula. Thank you very much if you can answer this question.

 

[Andrew Poulsom]

Maybe try eg:

ROW(INDIRECT("1:"& LEN(A1)))

 

[babycody]

Thanks for the reply Andrew. I think I must be using your formula in the wrong way, or I asked the question in a misleading way. The highlighted cell below shows the results I am trying to achieve. The cell next to it was my failed attempt to use your formula.

Book1
	A	B	C	D
1	dog	6	12
2
3
Sheet1

 

[Andrew Poulsom]

Why not stick with SUMPRODUCT?

=SUMPRODUCT(ROW(INDIRECT("1:"& LEN($A$1))),{2;2;2})

You need to separate the elements in your array with semicolons instead of commas.