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

Format reverts to text

My input of a number reverts to text which prevents use of formulas on the cells. When I try to change it back to number format, it won't. this did not happen till the latest version of numbers. Any advice on what to do?

Posted on Nov 19, 2015 1:59 PM

Reply
Question marked as Best reply

Posted on Nov 23, 2015 8:57 AM

Do you have a decimal separator in the number? If so make sure the , or . (whichever you use in your region) matches what you have in System Preferences > Language & Region > Advanced. Otherwise if you are not using the separator that it expects Numbers will guess that you are entering text.


User uploaded file


You can also try clearing out the cell(s) by selecting it(them) and hitting delete, then reentering your number(s).


And make sure you have upgraded Numbers to version 3.6.1.


SG

5 replies
Question marked as Best reply

Nov 23, 2015 8:57 AM in response to paulfromOPKS

Do you have a decimal separator in the number? If so make sure the , or . (whichever you use in your region) matches what you have in System Preferences > Language & Region > Advanced. Otherwise if you are not using the separator that it expects Numbers will guess that you are entering text.


User uploaded file


You can also try clearing out the cell(s) by selecting it(them) and hitting delete, then reentering your number(s).


And make sure you have upgraded Numbers to version 3.6.1.


SG

Nov 19, 2015 2:31 PM in response to paulfromOPKS

No much to go on. I will assume it has something to do with localization, use of a comma vs point for the decimal. There was a problem in the previous release on Numbers that they fixed in the most recent (as of a few days ago) update. Or what you are using in Numbers does not match your Language settings in System Preferences. Maybe one of those two things?

Nov 23, 2015 9:08 AM in response to SGIII

Thanks all, but that language setting was all correct and set as shown above in the screen shot. I discovered the cause but not the solution. I import financial data into a spreadsheet. When I do it comes in as CSV and the percentages and negatives numbers are interpreted incorrectly. Like they are text. This prevent some of the formulas from working properly. To get rid of the $, %, and "." signs which are perceived as text, I run a find & replace all on the spread sheet to get all that out. I find and replace all the $, the %, and the "." with nothing. Then I format the columns that need percent as percent, numbers as numbers and $ as numbers too. This issue didn't happen prior to this last revision of numbers. It turns out when I run the "." find replace with nothing, it turns the numbers in some of the cells into text. I can't seem to reformat back to actual from text as it is called in the bottom left corner status box. So I have to re-type the cells and all works properly. I think I can run omit the "." find replace all exercise and it might work.


regardless, thanks both of you for your assistance and concern. If you have any further thoughts, always appreciated.

Nov 23, 2015 9:16 AM in response to paulfromOPKS

paulfromOPKS wrote:


Thanks all, but that language setting was all correct and set as shown above in the screen shot. I discovered the cause but not the solution. I import financial data into a spreadsheet. When I do it comes in as CSV and the percentages and negatives numbers are interpreted incorrectly.


Any chance of providing more specifics?


What kind of financial data? What does it look like?

Are you inputing it or importing it? (In your first message your wrote input; your second says import.)


If importing it, how are you importing it?


SG

Nov 23, 2015 2:25 PM in response to paulfromOPKS

I am wondering if there are extraneous non-printing characters in the file. Space and tab characters are generally ignored if Numbers thinks the data is a number but will be included if it thinks the data is text. Maybe there are other characters besides spaces? Look at the CSV to see if there are any extra characters between the commas. Or, after import, try one or both of the following tests on some of the cells getting formatted as text:


1) Select the text then use the arrow keys to move left to right. Are there any extra characters?

2) Use a formula to find the length of the text in that cell. If the cell with the "number" is cell B2, in a different cell write the formula = LEN(B2) . Is it the correct number of characters for what you see or are there more?


If that doesn't show the problem, maybe you can post your CSV file somewhere (or part of it) so we can download it and take a look at it. Or email it to one or more of us directly. Hover over a user name to get the email address.

Format reverts to text

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