format cells problem

I have a problem on cell formatting.

The initial column format is Text but I tried to reformat it Number with separator. But there was no effect.

The worksheet and cells are not locked/protected. After clicking the formula bar then hightlight any cell again. That particular cell change to the expected format.

Is there a way to refresh all the cells in one go? so that I do not have to click every single cell to make it works.

Thanks.

Likely, your data is either imported or copied over, in which case it cannot be changed from text simply by formatting.

On workaround is this:
1. In a blank cell somewhere on the spreadsheet, type the number 1.
2. Click on the cell containing the 1 and press CTRL+C to copy it.
3. Next highlight the entire range containing the text to be converted to number.
4. Right click on the highlighted range-Paste special-Multiply.
5. Now, the text is numbers. You can format it how you normally would.

Let me know if that works.

Thanks heaps.

it works fine. Is it a bug in Excel?

I was having the same issue with a date field. By following your procedure you can convert it to a number, then you can format it however you wish. Your solution was a big help!!