Ask Your Question
0

extract (parse?) text string [closed]

asked 2015-11-13 14:59:32 +0200

caliray gravatar image

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 flag offensive 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

3 Answers

Sort by » oldest newest most voted
2

answered 2015-11-13 15:44:13 +0200

cloph gravatar image

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

edit flag offensive delete link more

Comments

Perfect answer and result. Thank you.

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

@cloph: somehow sophisticated but ok

karolus gravatar imagekarolus ( 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?

caliray gravatar imagecaliray ( 2015-11-13 17:10:00 +0200 )edit
0

answered 2015-11-13 16:00:13 +0200

karolus gravatar image

updated 2015-11-13 16:09:01 +0200

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)))
edit flag offensive delete link more
0

answered 2015-11-13 16:10:52 +0200

Lupp gravatar image

updated 2015-11-13 16:15:55 +0200

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

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2015-11-13 14:59:32 +0200

Seen: 1,115 times

Last updated: Nov 13 '15