Short answer Multiply them By 1 (in another formatted sheet)

Too little too late probably, but in another sheet within the same workbook (ie Sheet2) add a link to the sheet with the data:

=If(Len(Sheet1!B2) =0,"",Sheet1!B2*1)

This way if it's text it will show up as text, if it's blank, it will show as blank, if it's numeric, it will be numeric... winner winner chicken dinner....

Good luck!!