# Extract (parse?) text string

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.

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

Perfect answer and result. Thank you.

@cloph: somehow sophisticated but ok

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?

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)))``

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.)