Numbers is stuck on Automatic formatting -- nothing is working

Question

Level 1

9 points

Numbers is stuck on Automatic formatting -- nothing is working

Hi, I downloaded a CSV doc into Numbers of my transactions and it includes Australian dollar currency figures eg. A$5.50. I need to add these long lists of A$ figures but Numbers is only recognising them as "Text". When I try to change the format to currency or number by the usual ways, it just reverts back to "Automatic". Nothing I've tried seems to work. Have tried "Clear all" and pasting with matching style etc. Have tried pasting in a new sheet, nothing, nada.

It's vexing. Very grateful if anyone who knows how to fix this can speak up.

Cheers,

Ian

MacBook Air 13″, 10.14

Posted on Jul 6, 2020 7:01 AM

Reply

Answer 1

Best reply

Yellowbox

Level 7

20,483 points

Jul 6, 2020 7:53 AM in response to Razzarino

Hi Razzarino,

Yes, that is a pain. The CSV is formatted as text (for example A$5.50)

Try this. First extract the value from the CSV text.

Formula in B2 =VALUE(RIGHT(A2,LEN(A2)−2))

The LEN (length) function returns the number of characters in A2.

The RIGHT function returns the rightmost characters in A2, minus those two naughty A$ characters.

The VALUE function returns a number.

Fill the formula down.

Now you can format column B as Currency:

Select Column B and Copy. Then Menu > Edit > Paste Formula Results. Then you can delete the naughty Column A.

Regards,

Ian.

Reply

Answer 2

Razzarino Author

Level 1

9 points

Jul 6, 2020 6:11 PM in response to Yellowbox

Hi Ian,

Thanks kindly for your help. So complex but it actually works!

Cheers,

Ian

Reply

Numbers is stuck on Automatic formatting -- nothing is working

Similar questions