convert text to numerical values in Numbers

Select the cells and change the Data Format (dropdown in the Cell tab of the format inspector pane at the right) to Number.

If there are decimal separators make sure they match the separator in your region (usually a . or a ,). Otherwise Numbers won't recognize the text as a number.

SG

I think it is something you are doing (or not doing) that is causing this problem. It is usually bad practice to have a column that has mixed data in it (some text, some numbers or dates, etc), especially if the format of the column is anything other than "automatic" and most especially if the text justification has been set manually (which masks the default of text aligning left and numbers aligning right). It is too easy to get a mix of textual "numbers" and actual numbers (which is bad enough) and no easy way to tell one from the other (which is worse).

The cell data format (number, currency, text, etc.) is set in the Cell tab of the Format sidebar and nowhere else. There is no overlap with the Text tab in this respect.

Some info on formatting that became longer of a read than I meant it to be:

Automatic format is the default. If left as automatic, Numbers will try to determine what type of data it is and treat it as such. Text will be text unless it could be something else, such as a number or date. Numbers will be number. Some data might get misinterpreted if you don't pre-format the cell to what you want. Text aligns by default to the left. Everything else aligns by default to the right. If you change the text justification yourself, you will lose that visual clue.

If a cell has been formatted by the user (is no longer automatic), it will switch to automatic if the data typed into the cell does not match that format. For example, if I format a cell to number but then type "abc" into it, the format will switch to automatic. When I type a number into the cell, the format will go back to being "number". However, if a cell is formatted as text and I type "123" into it, "123" can be text and the format will remain as text.

If the data in a cell does not match the format the user is trying to set for it, the format will not stick (if you click off then click back, it will go back to automatic). For example, if an automatic cell has "abc" in it and I try to format it as number, the format will not stick. But the number format is still lurking in the background. If I later type a number into the cell, it will then take on the "number" format I had chosen.

If a cell has been formatted and has data in it that matches that format, you cannot change the format to something else unless the data could also be of that format. For example, if I have "abc" in a cell formatted as text, I cannot change the format to number while that text is there (if I click off then back it will go back to text). But if I have "123" in a cell formatted as text, I can change the format to "number" and it will take on the new format.

This question may run a bit deeper.

I've been working with Numbers daily for several months on a financial spreadsheet mixing text and numbers in the same column. The text entries are static, but number cells are frequently updated manually.

It is common (several times per week) to find that the format for a number cell has inexplicably converted from "number" to "text" yielding erroneous results in summations and other calculations. Frankly, this has become so frequent that my first task on opening a Numbers spreadsheet is to validate the format of all entries.

jhworkman wrote:

It is common (several times per week) to find that the format for a number cell has inexplicably converted from "number" to "text" yielding erroneous results in summations and other calculations.

Are you saying that a cell that was correctly formatted as "number" when you closed the document will be formatted as "text" when you reopen it? Are you the only one who uses this document?

environment

machine is residential, single user, pw-protected, firewalled

context

late 2012 mini, 2.5Ghz i5@16Gb, Catalina 10.15.7, Numbers v6.1(6369)

Summary spreadsheet is 58Rx15C summary, 3 supporting sheets@13Rx8c. Supporting sheets post a single numerical value to Summary.

"Are you the only one who uses this document?" Yes.

"Are you saying that a cell that was correctly formatted as "number" when you closed the document will be formatted as "text" when you reopen it?"

A qualified yes.

•I cannot assert that the behavior is limited to or invoked by "close the spreadsheet, open the spreadsheet".

•The behavior is intermittent and I have not noticed an action that appears to drive the change. Obviously, had I intentionally changed the format I would not be raising the issue.

My suspicion is that it is related to how one formats and/or reformats a row or column that is mixed number/text cells. Recall that text and number presentation selection occurs across two format selection areas, "Cell" and "Text" and changes can be applied to a single cell, a limited selection of cells, whole row, or whole columns. For example, one sets the decimal and thousands separator in "Cells" but presentation justification in "Text". It seems to me that there is malleability to assignment of number or text ... the appearance of "Automatic" in the Data Format box has always made me nervous ... "Automatic" when or why?

My solution has been to always treat computation results with suspicion and, if in doubt, check cell formats.

Badunit:

Thank you for the time and effort expended on that explanation. I'm sure that the cause or causes of my intermittent problems lie somewhere in the complexities you cite, i.e. "<snip> For example, if an automatic cell has "abc" in it and I try to format it as number, the format will not stick. But the number format is still lurking in the background. If I later type a number into the cell, it will then take on the "number" format I had chosen. <snip>"

However, after a good night's sleep and your second cup of coffee, if you reread your reply dispassionately, I think that you'll see, as I do, that the complexity and fragility of data entry is working against me. I use the software to monitor something. I want to arrange text and data, input and output, in a text/numbers layout that

that simplifies data entry and decision making.

Although now enlightened as to how clever and thoughtful the Numbers engineers are, I'll continue to regard the product with suspicion because it is needlessly complex. And, when in doubt, continue to check cell formats.

thank you, again, for your time and effort

I disagree about the complexity and fragility but it does require understanding and proper use. Hopefully something in what I wrote will help you understand what you are doing incorrectly so you won't keep having the same problem.