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.

User profile for user: Razzarino
Razzarino Author
User level: 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
Question marked as Top-ranking reply
User profile for user: Yellowbox
Yellowbox
User level: Level 7
21,197 points

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.



View in context

Similar questions

2 replies
Question marked as Top-ranking reply
User profile for user: Yellowbox
Yellowbox
User level: Level 7
21,197 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

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.
Learn more Sign up