# extract (parse?) text string [closed]

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.

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-03-14 14:02:15.364798

Sort by » oldest newest most voted

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

more

Perfect answer and result. Thank you.

( 2015-11-13 16:10:49 +0200 )edit

@cloph: somehow sophisticated but ok

( 2015-11-13 16:15:02 +0200 )edit

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?

( 2015-11-13 17:10:00 +0200 )edit

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

more

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

more