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.

Numbers formatting error

In existing spreadsheet, certain currency numbers only show the first number e.g. $1,700 show $ 1. Same with certain formulas. The real number is in the cell and will show only if format is changed to "number" rather than "currency" It used to be correct for all cells on multiple sheets of the spreadsheet. I have tried to copy individual cells & cell formats from one column to the next but it is only showing 1 number to the right of the cell on selected cells. Is there. something in an update that could have changed something? Any suggestion on how to get fix this format issue using currency (instead of changing 6-8 cells in each sheet. to numbers?

MacBook Pro 15″, macOS 12.0

Posted on Dec 21, 2021 11:15 PM

Reply
Question marked as Top-ranking reply

Posted on Jan 2, 2022 11:44 PM

Figured it out. It appears to be related to the font or size of column. Changing the font on the spreadsheet to Times News Roman (from Arial or Helvetica) resolves the problem. So does adjusting column widths.


I don't recall making any changes to the sheets in question but it would make more sense if they truncated the number, flushing it to the left if space is the issue IMO

Similar questions

7 replies
Question marked as Top-ranking reply

Jan 2, 2022 11:44 PM in response to evi1anne

Figured it out. It appears to be related to the font or size of column. Changing the font on the spreadsheet to Times News Roman (from Arial or Helvetica) resolves the problem. So does adjusting column widths.


I don't recall making any changes to the sheets in question but it would make more sense if they truncated the number, flushing it to the left if space is the issue IMO

Jan 2, 2022 11:04 PM in response to evi1anne

Here are screen shots, don't know how this helps

This is Formatting Error...all numbers in 1st 2 rows of numbers are the same.

Changed format from currency to number for Feb/Mar:


All numbers were previously formatted correctly with currency. Only recently changed to showing just the first number.

If I copy and paste the number or format from Jan to Feb it gives the strange format of only showing the 1st number.

There is no rhyme or reason as to which cells within the spreadsheet changed for no reason that I can explain.

Jan 2, 2022 11:39 PM in response to evi1anne

Hi Anne,


I think you missed a step, as there are no screen shots shown in your post above.


Please give it another go.


Here's a more detailed description of the process:


Set up the shot to show a sample of the affected cells AND the Format Inspector.


Click on one of the affected cells, then set the Inspector to Cell > Data format.


In your screen shot, include the selected cell and others in the two affected rows, the column reference tabs above the cells included in the shot, and the details regarding the data format shown in the Format Inspector.


Here's an example approximating the items that should be visible in the screen shot. The format shown in the Inspector applies only to the selected cell, and is there only for display purposes of the example.


Taking the screen shot:


After arranging the items to be in the shot, and checking that the inspector is showing the requested information:

Place the pointer at the top left corner of the area to be captured.

Press shift-command-4.

The pointer will change to a crosshair.

Drag the crosshair down and right to place a selection rectangle around the area to be captured, then release the mouse button to take the shot and save it to your desktop with the name "screen shot" followed by the date and time that it was taken.


Inserting the image in your post:


Click to place the insertion point where you want the image placed in your post.

Click the Mountains and Moon icon in the row of icons below the composition window.

In the desktop window, locate and double click the Screen shot file you just took.

You'll be transferred back to your post, where, after a second or two, your screen shot image should appear.


Regards,

Barry


Jan 3, 2022 12:34 AM in response to evi1anne

Happy to see you've found a solution. The cause is still a mystery, though.


"I don't recall making any changes to the sheets in question but it would make more sense if they truncated the number, flushing it to the left if space is the issue IMO"


Number values, whether formatted as numbers or as currency, are automatically aligned right.


Even when set to "accounting style," where the currency symbol is placed at the left edge of the cell, the number is aligned right, which keeps the decimals stacked in a straight line (except where the user chooses to show specific vsalues in bold and chooses to use parentheses to mark negative values.


I'm wondering if the pasted values in the February and March columns of your first screen shot were pasted into a cell formatted to hold Text, and lost their Accounting Style format.


That loss might change the space between $ and the first digit from 'auto generated' to 'a fixed number of spaces' or 'a fixed number of non-breaking spaces'. The latter might cause the number to be pushed off the right edge of the cell, even if 'wrap text in cell' was enabled.


Regards,

Barry

Jan 3, 2022 6:43 AM in response to evi1anne

It is the cell width not the font. There are too many digits in your number to fit in the cell and this is apparently how Numbers handles that problem when it is set to "accounting style". Changing the font worked only because you changed to a font that used less width. If you decrease the cell width after changing the font, the problem will return. The proper solution is to increase the cell width to accommodate your numbers.

Numbers formatting error

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