An interesting challenge! (Find & fix 'Numbers stored as text'

I had a friend come to me recently with a problem he wanted fixed. He has a sheet in a workbook which contains a number of cells throughout the page with the 'Number stored as Text' error. As there is so many of them, he doesn't look forward to fixing all these manually cell by cell.

What I am looking for is code that can:

1. Find all cells in active sheet (or maybe usedrange?) that are 'Numbers stored as text'
2. Convert the values of these cells to number format

I thought this would be easy to write but it's not.

While tempting to fix the entire sheet values, it is important to actually locate the cells with this error as the sheet contains cells with other formats etc. that must not be altered.

Try this macro:

Please Login or Register  to view this content.

I know this works on a single column or row, but have not tried it on an array range, give a try, it wont hurt anything...

find the top-leftest (is that a real word lol?) cell with the text in it. then highlight the entire range, down and across. use the mouse and scroll bars to scroll back to the top-left highlighted cell. you will see a little yellow "warning" diamond. click on that and you will see the option to "convert to number"

Hope it works

edit: just reied it on an array (rows and columns), and it worked great