Numbers: Convert text to number

If you're sure those are Text (and not Currency) then you can use REGEX.EXTRACT like this:

=REGEX.EXTRACT(A2,"[\d.,]+")*1

This regular expression will extract number digits and . and , .

The result of REGEX.EXTRACT is Text but you coerce that to a Number by multiplying by 1.

SG

apluslban wrote:

Is there a place to learn this regular expresion you use?

Regular expressions have been around a long time. At first glance they are intimidating. They are designed for flexibility and conciseness rather than readability to humans. There are instructional sites such as regular expressions 101 and many others, as well as apps on the Mac App Store.

I know how to use only a tiny subset of regular expressions but have found even very simple ones can be highly useful in Numbers.

In this particular expression

[\d.,]+

\d means a number "digit"

., means a period or comma

Putting them within the [] is sort of a logical or, meaning match either a digit or a . or a ,

The + at the end means match one or more of these characters.

SG

If it is text and Numbers recognizes it as currency, you can multiply by 1 to convert it into an actual number

=A2*1

then change the format to be Number instead of currency.