I tested out the below vba code today and it worked perfectly converting all text formatted cells on all worksheets in my spreadsheet to number formatted cells. Formulas will not always search text formatted cells correctly. As you can see this example, the VLOOKUP in column C does not successfully run through the text formatted numbers in column E.
After we insert the VBA code and run it, the cells are converted to numbers, and the formula works correctly.
For Each WS In Sheets
On Error Resume Next
For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(r) Then r.Value = Val(r.Value)
This is available at: