Numbers in Excel think they are text and wont change

[ajt8888]

I have a series on numbers that think they are text and despite converting them to text or general and even using text to columns they still wont appear as anything you can calculate at all. All I want to do is find the lowest and highest numbers on the list but looks like I need a formual that can count numbers that are text. I am sorry I cant attach file as there is no way to attach.

 

[RoryA]

Changing cell format will not affect the cell content. You need to change to a number format and then convert - there should be a convert to number option on the error smart tag when the cells are selected.

 

[ajt8888]

Thanks but there is no error smart tag to look at.

 

[ajt8888]

Its not seeing them as number so no Smart tag, I cant even sort them because they are not numbers even though formatted as numbers. There are a 1000 numbers I may just have to type them out but will then have to do that 17 times. Sometimes Excel is just rubbish.

 

[Fluff]

Cross posted Unable to convert numbers that think they are text into something that can be counted

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

There is nothing wrong with Excel.
You are using the RIGHT function which returns text value.
As mentioned in the other thread simply change your formula to

=RIGHT(A2,7)+0

and then fill down & across

 

[yky]

There seems to be two types of number in TEXT format.

Column A was formatted as TEXT before entering the numbers. They are left-aligned and there is a small green triangle on the top-left corner of each cell, indicating number in TEXT format. Column B was originally in GENERAL but after entering the formula "=A2+0", it automatically turned into TEXT format (as seen from Format/Format Cells or Ctrl-1). There is, however, no green triangle in the cell. Column C is in GENERAL format.

You can do simple math (addition, division, multiplication, division, Log, maybe others) on A as "=A2+A3" gives 3. Functions such as SUM fail though.

Column B, though left aligned implying TEXT, is just like regular number. SUM works on Column B. All functions and math operations should work on C as it is in GENERAL format.

TYPE(A2) gives 2, indicating TEXT format, while TYPE(B2) gives 1, indication number format. The latter is not in agreement with what one gets by clicking Format/Format Cells or Ctrl-1, both indicating column B is TEXT.

This is done on Excel 2010. Maybe the 2019 version gives the correction information.

TEXT	=A2+0	General
1	1	1​
2	2	2​
3	3	3​
4	4	4​
5	5	5​
6	6	6​
7	7	7​
8	8	8​
0	36	36
=SUM(A2:A9)	=SUM(B2:B9)	=SUM(C2:C9)
3
=A2+A3