Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 13 Nov 2015 17:10:00 +0100extract (parse?) text stringhttps://ask.libreoffice.org/en/question/60809/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.Fri, 13 Nov 2015 14:59:32 +0100https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/Answer by Lupp for <p>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.</p>
<p>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 <code>=LEFT(A2;1)</code> 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.</p>
<p>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.</p>
<p>Thanks for the help.</p>
https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?answer=60814#post-id-60814Completely 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](/upfiles/1447427146306155.ods)
Fri, 13 Nov 2015 16:10:52 +0100https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?answer=60814#post-id-60814Answer by karolus for <p>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.</p>
<p>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 <code>=LEFT(A2;1)</code> 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.</p>
<p>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.</p>
<p>Thanks for the help.</p>
https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?answer=60812#post-id-60812Hallo
=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)))Fri, 13 Nov 2015 16:00:13 +0100https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?answer=60812#post-id-60812Answer by cloph for <p>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.</p>
<p>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 <code>=LEFT(A2;1)</code> 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.</p>
<p>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.</p>
<p>Thanks for the help.</p>
https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?answer=60811#post-id-60811for 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 :-)Fri, 13 Nov 2015 15:44:13 +0100https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?answer=60811#post-id-60811Comment by caliray for <p>for computing the sum of the digits, you can use a fancy formula like</p>
<pre><code>=SUMPRODUCT(VALUE(MID(A1;ROW(INDIRECT("A1:A"&LEN(A1)));1))*1)
</code></pre>
<p>but explaining this a litte too much for this - but it also includes the answer to the question: Instead of <code>LEFT</code>, use the function <code>MID</code>:</p>
<p>=MID(A2;1;1)</p>
<p>for the first digit, and <code>=MID(A2;2;1)</code> for the second, until <code>=MID(A2;8;1)</code> for the eighth one.</p>
<p>Remember that those functions return text, not a number, so to compute with them, you should wrap them in <code>VALUE</code> function.</p>
<p>If all your input is of equal length, then assembling a manual function using eight <code>VALUE(MID(A2,<offset>,1)</code> statements is a workable solution, and probably easier to grasp than the fancy one above :-)</p>
https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?comment=60818#post-id-60818I 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?Fri, 13 Nov 2015 17:10:00 +0100https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?comment=60818#post-id-60818Comment by karolus for <p>for computing the sum of the digits, you can use a fancy formula like</p>
<pre><code>=SUMPRODUCT(VALUE(MID(A1;ROW(INDIRECT("A1:A"&LEN(A1)));1))*1)
</code></pre>
<p>but explaining this a litte too much for this - but it also includes the answer to the question: Instead of <code>LEFT</code>, use the function <code>MID</code>:</p>
<p>=MID(A2;1;1)</p>
<p>for the first digit, and <code>=MID(A2;2;1)</code> for the second, until <code>=MID(A2;8;1)</code> for the eighth one.</p>
<p>Remember that those functions return text, not a number, so to compute with them, you should wrap them in <code>VALUE</code> function.</p>
<p>If all your input is of equal length, then assembling a manual function using eight <code>VALUE(MID(A2,<offset>,1)</code> statements is a workable solution, and probably easier to grasp than the fancy one above :-)</p>
https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?comment=60815#post-id-60815@cloph: somehow sophisticated but okFri, 13 Nov 2015 16:15:02 +0100https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?comment=60815#post-id-60815Comment by caliray for <p>for computing the sum of the digits, you can use a fancy formula like</p>
<pre><code>=SUMPRODUCT(VALUE(MID(A1;ROW(INDIRECT("A1:A"&LEN(A1)));1))*1)
</code></pre>
<p>but explaining this a litte too much for this - but it also includes the answer to the question: Instead of <code>LEFT</code>, use the function <code>MID</code>:</p>
<p>=MID(A2;1;1)</p>
<p>for the first digit, and <code>=MID(A2;2;1)</code> for the second, until <code>=MID(A2;8;1)</code> for the eighth one.</p>
<p>Remember that those functions return text, not a number, so to compute with them, you should wrap them in <code>VALUE</code> function.</p>
<p>If all your input is of equal length, then assembling a manual function using eight <code>VALUE(MID(A2,<offset>,1)</code> statements is a workable solution, and probably easier to grasp than the fancy one above :-)</p>
https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?comment=60813#post-id-60813Perfect answer and result. Thank you.Fri, 13 Nov 2015 16:10:49 +0100https://ask.libreoffice.org/en/question/60809/extract-parse-text-string/?comment=60813#post-id-60813