Changing Cell Format in Numbers

Discussion in 'iPad Help' started by MikeV99, Mar 9, 2011.

  1. MikeV99
    Offline

    MikeV99 iPad Fan

    Joined:
    Feb 5, 2011
    Messages:
    200
    Thanks Received:
    3
    Trophy Points:
    0
    Location:
    Arkansas
    Ratings:
    +3 / 0
    Is it possible to change the format of data in a cell? For example, I have 1234.78 in a cell in text format. I want to change it to currency $1,234.78. I have tried selecting Currency in the Format dropdown, but it does not do the change.

    Thanks
  2. Tim SPRACKLEN
    Offline

    Tim SPRACKLEN iPad Legend

    Joined:
    Jan 3, 2011
    Messages:
    15,246
    Thanks Received:
    121
    Trophy Points:
    0
    Location:
    Scotland
    Ratings:
    +121 / 0
    OK - I'm not a 'Numbers' guru, but until you get a reply from someone who knows more than I do (which, by inference, is anyone else!) have you seen the Apple Help information on Numbers?

    http://help.apple.com/iwork/1.3/mobile/interface/index.html#tan724868a9

    Tim
    Scotland
  3. USBill
    Offline

    USBill iPad Enthusiast

    Joined:
    Apr 29, 2010
    Messages:
    399
    Thanks Received:
    3
    Trophy Points:
    0
    Ratings:
    +3 / 0
    Are you selecting the row\column or just the cell?
  4. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,526
    Thanks Received:
    2,076
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,414 / 1
    Well, since you can see the Format and select Curency, I've got to figure you're almost there. The only reason I can thing why the format isn't changing is that the cell is already populated, and with an incompatible data type.

    For instance, if it has date & time, duration, or text in the cell, you can't change it to currency.

    To check, double tap on the cell. The cell data keyboard will come up. There are four icons on the left. From left to right they are Numbers, Time/Duration, Text, and Formula. To change to a currency format the cell data must be Numbers. That makes sense, yes.

    Just change the data and you'll have no problem changing the format.

    You can change empty cells to anything you want. So selecting the cell and deleting the content will also let you change the format.

    Let me know if it doesn't work for you.

    Edit: Hee hee, in this case the answer to everything was '42'. Which is the icon for number entry, in case you didn't notice. [Not so obscure reference to Hitchhikers Guide to the Galexy by Douglas Adams]
    Last edited: Mar 9, 2011
  5. MikeV99
    Offline

    MikeV99 iPad Fan

    Joined:
    Feb 5, 2011
    Messages:
    200
    Thanks Received:
    3
    Trophy Points:
    0
    Location:
    Arkansas
    Ratings:
    +3 / 0
    Thanks for the responses. I am trying to accomplish the same task in Numbers on the same spreadsheet as I do in Excel. The spreadsheet has columns that are defined as currency/bold. I receive an email, copy the number from the message, and special paste/text it into a cell. Excel converts the paste into currency/bold. Given that Numbers works differently, I was trying to find a quick procedure that would duplicate the approach within the capability of Numbers.

    Thoughts?

    Thanks.
  6. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,526
    Thanks Received:
    2,076
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,414 / 1
    From the few tests I performed, it looks like Numbers isn't smart enough to convert text to a number.

    Any number you copy from an email is text, of course. That's the only way it could be displayed properly. Most desktop spreadsheets are smart enough to convert text that hold only number characters to numbers when you paste into a cell. It doesn't look liken Numbers is that smart. When you paste a number from email, it just enters the text. That is why you can't format it, and why it overrides any number formatting you have in the cell before pasting.

    I can only find one way around this. Instead of pasting directly into the cell, double tap on the cell to get the cell editing keyboard. Make sure the number icon is selected and then paste into the entry field. That seems to work. If you are only pasting single numbers at a time, this shouldn't be much more trouble than pasting directly into the cell.


    You can convert an entire column or row to Currency Bold. When the table is selected you'll see a bar on the left and top. If you tap on the bar opposite of the column you want, the entire column will be selected. To turn the column to bold, go to the Cell tab and change it to bold. Then go to Formate and change it to currency. If your header wasn't bold before, you may have to change it back individually.

    As before, it will not change cells that already contain incompatible data. But it will make manual entry quicker, since new data entered into empty cells will be formatted correctly.
  7. MikeV99
    Offline

    MikeV99 iPad Fan

    Joined:
    Feb 5, 2011
    Messages:
    200
    Thanks Received:
    3
    Trophy Points:
    0
    Location:
    Arkansas
    Ratings:
    +3 / 0
    Thank you for your comments.

    I had tried the keyboard editor previously. It would not accept the Paste - nothing pasted. Since your paste worked I took another look at the data that was copied. It contained a separator comma. I pasted the number into Notes and edited out the comma, copied it from Notes, and pasted it to the entry line of the Keyboard editor and it worked. It starting to feel like a big PITA to get it to work. Maybe I need to see if Numbers has a Text function to convert to a number.

    Regards,
  8. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,526
    Thanks Received:
    2,076
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,414 / 1
    You've probably discovered this yourself by now, but if not. . .,

    Heck of a round about way to do it though.

    I created a small table, just two columns. In the second column I created a formula to convert text to dollars. Function = DOLLAR (A1,2) where A1 is the text cell and 2 is the number of decimal places. You'll find it under the Text functions.

    I could then paste a text version of dollars into the text field (A1), including the dollar sign if I like. Samples I used: 23,450.56 : $34,001.4 : $23 Be careful not to include a line end or paragraph feed in the copied text. You'll get an error.

    You can then copy from the converted cell and paste to the cell in the table you want. You'll be presented with a choice of pasting the formula or value. Paste the value.

    I suppose if you wanted to keep things simple, and don't mind having an extra column in you original table, you could create a column just for pasting the values.
  9. MikeV99
    Offline

    MikeV99 iPad Fan

    Joined:
    Feb 5, 2011
    Messages:
    200
    Thanks Received:
    3
    Trophy Points:
    0
    Location:
    Arkansas
    Ratings:
    +3 / 0
    =value

    Here is what I did:
    1. Copy/Paste the email value to a general cell outside the data range.
    2. Select the cell to receive the paste and double click to open editing keyboard.
    3. Select =/Text/VALUE - source should be bright blue.
    4. Click the general cell to designate it as the source.
    5. Click the green arrow to accept.
    6. VALUE converts the text to a number; it displays as the cell was previously formatted (currency, bold, etc.).
    Thank you for your assistance.
  10. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,526
    Thanks Received:
    2,076
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,414 / 1
    Sounds pretty close same thing I did, except I used the Dollar function instead of the Value function. Only problem I can see is that you're going to have to create a new source cell each time you do that. I you try to use the same one, you are going to change the value in the destination cell.

    You are better off, unless I misunderstand what you did, either using the two column copy paste method I described, or creating another column or table with a one-to-one relations ship to the destination cells.
  11. MikeV99
    Offline

    MikeV99 iPad Fan

    Joined:
    Feb 5, 2011
    Messages:
    200
    Thanks Received:
    3
    Trophy Points:
    0
    Location:
    Arkansas
    Ratings:
    +3 / 0
    I just paste over the source cell with the next item to be processed. When done, I clear the source cell until needed again.
  12. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,526
    Thanks Received:
    2,076
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,414 / 1
    If it works, it works. Glad you got a solution, even if it had to be a work around.
  13. MikeV99
    Offline

    MikeV99 iPad Fan

    Joined:
    Feb 5, 2011
    Messages:
    200
    Thanks Received:
    3
    Trophy Points:
    0
    Location:
    Arkansas
    Ratings:
    +3 / 0
    I thought you might be interested in knowing that you are right and I am wrong. After experimenting with this some more I find that your suggestion that 2 cells are needed for the conversion is correct. I finally just added a second spreadsheet for the conversion cells. However, I did find that the VALUE function provided me more consistent results with regard to cell formating.

    Regards
  14. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,526
    Thanks Received:
    2,076
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,414 / 1
    Thanks for letting me know. Also, good to know about the VALUE function. I'll tuck that away for future use.
  15. bilbo
    Offline

    bilbo iPF Noob

    Joined:
    May 26, 2011
    Messages:
    3
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    canada , bc
    Ratings:
    +0 / 0
    bilbo

    Hi Mike

    I use numbers on my Mac so hope the same options are available on the ipad which I will be getting soon .

    To change the data in a cell to currency you need to go to the "inspector" and click on the 4th box from the left with the number 42. Go the the box that says "automatic" and open the menu. It will let you choose the format you want for your number. Figured this one out by trial and error. I find the numbers "help" not all that useful.


    bilbo
  16. MikeV99
    Offline

    MikeV99 iPad Fan

    Joined:
    Feb 5, 2011
    Messages:
    200
    Thanks Received:
    3
    Trophy Points:
    0
    Location:
    Arkansas
    Ratings:
    +3 / 0
    Thanks for the response. The iPad evidently is a bit different than the one on the Mac. In any event it works great the way I have it set up now.

    Regards.

    Mike

    Sent from my iPad using iPF

    PS: I made the change using the inspector as you suggested and it does do as you described. Thanks.
    Last edited: May 28, 2011

Share This Page

Search tags for this page
apple numbers formatting currency
,

clear formatting nubers mac

,
format a cell in pages
,

format cells in numbers mac

,

how do i change cell format in numbers

,

how do i extend a border on a cell in numbers ipad

,
how do you format columns in mac numbers
,

how to change cell size in numbers mac

,

how to change cell size in the app number

,

how to change cell size numbers app

,

how to change cells in numbers ipad

,

how to change the format of a cell in numbers mac

,
how to edit an email address cell in mac numbers
,

how to format cells in numbers ipad

,
how to outline a cell in numbers
,
how to set american date format in apple numbers
,
mac numbers change email format
,

mac numbers convert text to number

,
mac pages change format to numeric
,

numbers for ios changing cell formatting values