2

I have currency values in the following format in a CSV file.

$171.5M
$2.74B

How do I have Numbers recognize these as proper currency values for sorting?

4
  • 1
    Do we presume you’re on macOS version of pages? (Or perhaps iOS or iPad or web?)
    – bmike
    Jul 19, 2020 at 23:06
  • Yes, this is for Numbers on macOS.
    – Berry Blue
    Jul 21, 2020 at 0:52
  • What you need to do is multiply the M number by 1.0x10^6 and the B number by 1.0x10^9. This will give you the values with all the digits. i have tried to get Numbers to recognize that format with no luck...
    – Natsfan
    Jul 21, 2020 at 2:25
  • I’ll play with the new REGEX formulas in the new version of Numbers and see what can be done but that may do the trick.
    – bjbk
    Jul 21, 2020 at 2:40

1 Answer 1

1

You can't use the letters B or M in the cell. you have to use all the digits. Open numbers and on the right side of the window, on the top right corner be sure to click on Format. You should have 4 tabs underneath Format that read "Table, Cell, Text, Arrange. Click on the Cell tab and a new panel appears with a pulldown menu Data Format. One of the options under this menu is Currency, select that and you should be good to go. My setting is Automatic and it usually recognizes the $ sign as currency. See image below.

enter image description here

enter image description here

8
  • Does that sort Millions vs Billions as the op shows in the question?
    – bjbk
    Jul 19, 2020 at 23:50
  • 1
    This answer does not appear to work with the same data provided in the OP. Please use the sample data in the OP, not just something arbitrary you've typed in. Jul 20, 2020 at 0:22
  • you can't put the M and B after the numbers. That doesn't work, you have to put all the digits in.
    – Natsfan
    Jul 20, 2020 at 1:23
  • it doesn't matter which digits i use as long as i put them all in.
    – Natsfan
    Jul 20, 2020 at 1:24
  • @bjbk - Sorry but you can't use the B and M digits after the numbers. you need to put in all the digits.
    – Natsfan
    Jul 20, 2020 at 1:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .