[libreoffice-users] Re: sum text column of common unit of measure numbers

On 2020-05-07 6:04 p.m., Remy Gauthier wrote:
> Hi,
> If your values takes up, for instance the range A2 to A29, you can
> enter this formula as an array formula:
> =SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB","")," MB","")))
> The only trick is to hit CTRL-SHIFT-ENTER instead of just ENTER at the
> end of the formula entry: if you only use ENTER, this will be a
> "normal" formula and it will not work. To make sure you did it right,
> the formula should appear with curly braces on each side in the formula
> display, like this:
> {=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB","")," MB","")))}
> Another thing toi be careful about: if your decimal separator is the
> comma (","), the dot in your list of numbers will also have to be
> changed. You can change the formula like this to make the change of
> separator:
> =SUM(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB",""),"
> MB",""),".",",")))
> Now, if you really want to be fancy, you can use the REGEX function to
> remove the units. This will remove KB, MB, and GB:
> =SUM(VALUE(REGEX(A2:A29," [KMG]B","")))
> And don't forget that CTRL-SHIFT !
> I hope this helps.
> Rémy.
This looks promising. :-)


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy