Extract (parse?) text string

[caliray]

I have a number (04121997) as text. The solution I’m looking for could be used for a number, whichever is best because I don’t know how to do either one.

I need to extract (parse?), each number and put it into its’ own cell as the ultimate goal is to sum the numbers in the string. I tried =LEFT(A2;1) which gets the left most number, but what do I add to get the 2nd, 3rd, etc. numbers put into cells by themselves? I don’t know how to set up the offset and the related questions were very complex FIND and SEARCH formulas that seem way too complex for such a simple problem.

Also, do I need to be concerned about the length of the string? It will always be 8 digits as these are text or digits representing dates and I will always include zeros in the string. That’s why I’m writing them as text so CALC doesn’t truncate the number when it sees a leading zero.

Thanks for the help.

[cloph]

for computing the sum of the digits, you can use a fancy formula like

=SUMPRODUCT(VALUE(MID(A1;ROW(INDIRECT("A1:A"&LEN(A1)));1))*1)

but explaining this a litte too much for this - but it also includes the answer to the question: Instead of LEFT, use the function MID:

=MID(A2;1;1)

for the first digit, and =MID(A2;2;1) for the second, until =MID(A2;8;1) for the eighth one.

Remember that those functions return text, not a number, so to compute with them, you should wrap them in VALUE function.

If all your input is of equal length, then assembling a manual function using eight VALUE(MID(A2,<offset>,1) statements is a workable solution, and probably easier to grasp than the fancy one above

[caliray]

Perfect answer and result. Thank you.

[karolus]

@cloph: somehow sophisticated but ok

[caliray]

I have one more question about this. In my usage, adding a string of digits in a date i.e. 03281959 always results in a 2 digit result (37) which I’ll call result #1. Summing 3+7 yields 10 (result 2), another 2 digit number, which needs to be reduced to a single digit. I get that. I can simply add them However, what test could I do at the point where result 2 is greater than 9? Would an IF THEN work to test result 2 to see if further parsing and additions are necessary to get a single digit?

[karolus]

Hallo

=SUM(VALUE(MID(A2;ROW(OFFSET($A$1;0;0;LEN(A2);1));1))) 

enter as Array-Formula with ctrl+shift+enter respectivly the [x]Array-option in Formulawizard

if the Lenght of the String is always 8, it can be simplified to

=SUM(VALUE(MID(A2;ROW($A$1:$A$8);1)))

[Lupp]

Completely generalised:

If the number is in A1, apply the following formula in another cell:

=SUMPRODUCT(VALUE(MID(A1;ROW(OFFSET(INDIRECT("A1");0;0;LEN(A1);1));1)))

SUMPRODUCT will force the ROW expression under array ealuation. This meaning that it will produce the values 1, 2, … one after another, as many of them as LEN(A1) created rows by the OFFSET expression. The MID function, thus getting an array instead of a single number, will also evaluate iterated and SUMPRODUCT will get the sequence of digit values for adding.

This will also work if A1 is of type number and has a natural value. MID will apply automatic conversion.

(Sorry, just found the answer by @karolus crossing mine. He followed the same path, but obviuosly a few minuts eartlyer. You may restrict reading my answer to the explanation about array evaluation.)

See attached for demo: ask60809DigitSum001.ods