Solved: String into Array

[makako]

Hi, I know this can be done in VBA but I want to know if it is possible doing it in only one cell with no code. I need to take as an input a numeric ID in a client and generate a validation number. The validation number is generated like this:

Ex: ID = (In cell A1 for the formula below) 2023453
Validation generation:
SUM( MMULT( {2,0,2,3,4,5,3} "This array I would need to generate and dont know how, any ideas?" ; TRANSPOSE( "{" & LEFT(REPEAT("1,2,";INT(LEN(A1)/2)+1);(LEN(A1)*2-1) & "}" )) )

that creates the array

{=SUM(MMULT( {2,0,2,3,4,5,3} ; TRANSPOSE( {1,2,1,2,1,2,1} )))}
{=SUM(MMULT( {2,0,2,3,4,5,3} ; {1/2/1/2/1/2/1} ))}
{=SUM({ 2*1 , 0*2 , 2*1 , 3*2 , 4*1 , 5*2 , 3*1 })}
{=SUM({ 2 , 0 , 2 , 6 , 4 , 10 , 3 })}
=27

So, any ideas in using only excel turning 2023453 into {2,0,2,3,4,5,3}??

[Bob Phillips]

Try this

=SUM((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*(IF(INT(ROW(INDIRECT("1:7"))/2)*2=ROW(INDIRECT("1:7")),2,1)))

[Bob Phillips]

Without the hard-coding

=SUM((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*(IF(INT(ROW(INDIRECT("1:"&LEN(A1)))/2)*2=ROW(INDIRECT("1:"&LEN(A1))),2,1)))

[Bob Phillips]

Here is a better one, non-array and no IF

=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),(MOD(TRUNC(ROW(INDIRECT("1:"&LEN(A1)) )),2)=0)+1)

[makako]

WOW, that was awesome, you are goooooooood!!!!
Thanks.