Formatting as Number will not work

g48dd · 01-03-2011, 01:00 AM

Hi, I have this sheet this morning that has lots of numbers, alinged in center with error beside them that says "Number Stored as Text". At first I just tried format the whole column as a number, but that changes nothing. Can someone explain why that does not change anything. I thought it would get rid of all the Error codes but it does nothing. I don't want to turn off the Error code as I need to see what numbers were formatted like this.

I am guessing someone cut and pasted these numbers in, and maybe pasted as TEXT or Unicode Text, right now I am clicking on small groups of them and converting them to a number.

I also tried high lighting the whole SS and removing all formats (that removes everything except this TEXT tthing), I need to reformat and rebuild this whole sheet anyway. But when I do that it still does not convert all these numbers that are formated as text back to numbers. I have a 1000 lines or so is there a faster way to convert this, if I paste this whole sheet into a new sheet and use Paste Special-values would that then paste the TEXT back in as numbers?

Thanks
Ken

g48dd · 01-03-2011, 04:12 AM

Solved my own problem- Problem: Hundreds of cells with numbers formatted as text when there is no reason to have them formated that way. How do I fix this, I tried clicking the column head and formatting as number, but that didn't work, I tried removing all the format from the sheet, that didn't work, I tried typing a number in an unused cell, copying that cell and then tried to paste that format into my column, that didn't work, I tried copying the whole sheet to a new sheet and paste special Values, that changed nothing, all this and I still have my little error tag. What did work?

1) Find your range mine was H18:H727
2) go to the first occurrence of a number formatted as text, that was H18, click on that cell so that the little tag appears telling you there is an error here.
3) After clicking on H18 (tag appears) go to the Name Box above column A type your range in H18:H727 Enter and your range is high lighted
4) Ctrl+1 the formatting window, now choose what format you want I just wanted plain number no decimals.

Apparently my problem was that I was selecting the whole column by using the Column Bar & then formatting the whole column but this has no affect on the formatting.

It nice to solve my own problems sometimes

Ken

**arthurbr** · 01-03-2011, 04:22 AM

Glad you solved your problem
You could also have entered 1 in an empty cell - copy this cell - select data - Right click - select Paste special - select Multiply - OK

**rbtrout** · 03-14-2011, 02:15 PM

Originally Posted by arthurbr

Glad you solved your problem
You could also have entered 1 in an empty cell - copy this cell - select data - Right click - select Paste special - select Multiply - OK

This solved my issue. Thanks