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

Den tis 15 juli 2025 kl 02:28 skrev Java Joe <ticktockdata@techie.com>:

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

It seems like the second line is unnecessary. At least when I tried,
VALUE(E61) returns 0 if E61 is empty, so I think just =E61 works.

Doing it in one step gives an error for an empty cell though, but it can
easily be solved with IFERROR:
=IFERROR(VALUE(REGEX(C61,"[^0-9.]","","g")),0)

One downside with REGEX in this case is that it won't work in every locale.
I, for instance, use a comma for decimal separation and a semi-colon to
separate arguments, so my formula looks like:
=IFERROR(VALUE(REGEX(C61;"[^0-9,]";"";"g"));0)


Kind regards

Johnny Rosenberg




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

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