Re: [libreoffice-users] replace and text to number but no errors

There are a couple of solutions. I have "165.56 MB" in B2

=VALUE(REGEX(B2,"\d+\.\d+")) will return as a number the 165.56.
\d+is one or more digits followed by \. a point followed by \d+ 1 or 
more digits again. This always assumes that there is at least one digit 
before and after the decimal.

You can check if a valid number is returned and if not substitute 0.
=IF(ISNUMBER(VALUE(REGEX(B2,"\d+\.\d+"))),VALUE(REGEX(B2,"\d+\.\d+")),0)

You could check if the right 2 letters are MB and if so perform your 
substitution
=IF(RIGHT(B2,2) = "MB",VALUE(SUBSTITUTE(B2,"MB","")) ,0)

On 15/07/2025 11:26, James wrote:
> I have text cells that contain "165.56 MB" or nothing.
> I use this formula to get the number:
> =VALUE(SUBSTITUTE(C61,"MB",""))
> The problem is some of the cells don't contain MB and then return 
> "Err: 502".
> I want to sum the results but the Err: 502 make that fail.


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