some numbers formatted as text

[peterhw]

I have had a problem with some data not appearing to be what I thought it was. A column that appeared to be all numbers contained some text fields.

I'd been looking for a solution and with the help of members (of another forum) I think I now have a solution.

If I have a character 1 in a cell - then that cell is 1 character long and has the ASCII code 49 but when I apply isnumeric then I may get TRUE for some cells and FALSE for other cells.

Not having control of the data source I wanted to be able to validate the data - prior to use.
So simple but just select the range then multiply the source by 1 and replace

Please Login or Register  to view this content.

Hope this is useful

[Pepe Le Mokko]

Thanks for the macro
Adding 0 also does the trick and it can be done with Paste Special Values - Multiply ( after copying 1) or add ( after copying 0)
If the values are involved in calculations they are coerced automatically within the calcualtion

[Paul]

Duplicate thread. Same post and additional information here: http://www.excelforum.com/excel-prog...d-as-text.html

Thread is now closed.