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 '20 at 23:06
  • Yes, this is for Numbers on macOS. – Berry Blue Jul 21 '20 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 '20 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 '20 at 2:40
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

9
  • Does that sort Millions vs Billions as the op shows in the question? – bjbk Jul 19 '20 at 23:50
  • Cool! Thanks for clarifying – bjbk Jul 19 '20 at 23:54
  • 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. – user3439894 Jul 20 '20 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 '20 at 1:23
  • it doesn't matter which digits i use as long as i put them all in. – Natsfan Jul 20 '20 at 1:24

You must log in to answer this question.

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