Some numbers formatted as text

some numbers formatted as text

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.

Hi John55,

The code I used worked and as I understand by requesting the multiplication Excel converts to a number anything it things could be a number and that's why it should work.

I'll PM sample code to you.

Hope this one loads (attaches) OK
The code is very simple but very useful if you are not sure where your data originates!