How do you convert text like `'$71.4145` into a number you can do arithmetic with-CodePudding

The title says it all. One thing I want to avoid is long formulas. If it's more than a single function, something is clearly wrong since this should be a common use case.

I've tried TO_PURE_NUMBER and VALUE

CodePudding user response：

Your question suggests that the value $71.4145 is not a number but a text string. That can happen if your spreadsheet locale is such that it expects comma as decimal mark, or expects a different currency symbol. It will also happen if you have formatted the value as plain text rather than currency.

To convert the text string $71.4145 into the number 71.4145 (seventy-one and change), use regexextract(), like this:

=iferror( value( regexextract( to_text(A2), "[\d.] " ) ) )

CodePudding user response：

Just use -- to suppress text to equivalent number values. Try-

=--A1

CodePudding user response：

try:

=SUBSTITUTE(A1; "$"; )*1