Apple Intelligence is now available on iPhone, iPad, and Mac!

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

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
Question marked as Top-ranking reply

Posted on Jul 6, 2020 7:53 AM

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.



2 replies
Question marked as Top-ranking reply

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.



Numbers is stuck on Automatic formatting -- nothing is working

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