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+unsubscr...@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