Changing Cell Format in Numbers

[MikeV99]

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

 

[Tim SPRACKLEN]

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

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

 

[USBill]

Are you selecting the row\column or just the cell?

 

[twerppoet]

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]

 

[MikeV99]

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.

 

[twerppoet]

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.

 

[MikeV99]

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,

 

[twerppoet]

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.

 

[MikeV99]

=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.

 

[twerppoet]

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.

 

[MikeV99]

I just paste over the source cell with the next item to be processed. When done, I clear the source cell until needed again.

 

[twerppoet]

If it works, it works. Glad you got a solution, even if it had to be a work around.

 

[MikeV99]

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

 

[twerppoet]

Thanks for letting me know. Also, good to know about the VALUE function. I'll tuck that away for future use.

 

[bilbo]

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