If the reason for the error is the same every time, and you have a formula for that particular situation, you can use IFERROR(), something like this: =IFERROR(VALUE(SUBSTITUTE(C61,"MB",""));<Your in-case-of-error formula here>)
For instance if the error causing cell just lacks the ”MB” string and already is a pure number: =IFERROR(VALUE(SUBSTITUTE(C61,"MB",""));C61) If the first formula causes an error, the second formula will be used, otherwise the first one. Kind regards Johnny Rosenberg Den tis 15 juli 2025 kl 02:28 skrev Java Joe <[email protected]>: > Better Solution? Use REGEX to strip all characters except numbers and > period. > > To make relatively bullet-proof and not show errors it's probably best > implemented in 2 cells. > > My Example: > Cell E61: =REGEX(C61, "[^0-9.]", "", "g") > Cell F61: =IF(E61<>"", VALUE(E61), 0) > > Could be wrapped in a single expression, with repeated REGEXP function: > =IF(REGEX(C61, "[^0-9.]", "", "g") <>"", VALUE(REGEX(C61, > "[^0-9.]", "", "g")), 0) > > > Older versions of LO don't have REGEX function, here's a somewhat > workable solution if that is needed: > =VALUE(IF(ISERR(FIND(" ", TRIM(C61), 1)), C61, MID(TRIM(C61), 1, > FIND(" ", TRIM(C61), 1)))) > > > On 7/14/25 20:12, Steve Edmonds wrote: > > 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: [email protected] > 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 > -- To unsubscribe e-mail to: [email protected] 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
