You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Numbers: Convert text to number

Is there a way to convert texts to numbers. e.g


$12.3 convert to 12.3


Posted on May 28, 2023 7:11 AM

Reply
Question marked as Top-ranking reply

Posted on May 28, 2023 9:05 AM

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

4 replies
Question marked as Top-ranking reply

May 28, 2023 9:05 AM in response to apluslban

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

May 30, 2023 5:08 AM in response to apluslban

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



Numbers: Convert text to number

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.