VBA: Convert Text to Number
Use the below function (changing [E:E] to the appropriate range for your needs) to circumvent this issue (or change to any other format such as "mm/dd/yyyy"):
[E:E].SelectWith Selection .NumberFormat = "General" .Value = .ValueEnd WithP.S. In my experience, this VBA solution works SIGNIFICANTLY faster on large data sets and is less likely to crash Excel than using the 'warning box' method.
This can be used to find all the numeric values (even those formatted as text) in a sheet and convert them to single (CSng function).
For Each r In Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeConstants) If IsNumeric(r) Then r.Value = CSng(r.Value) r.NumberFormat = "0.00" End IfNext
I had this problem earlier and this was my solution.
With Worksheets("Sheet1").Columns(5) .NumberFormat = "0" .Value = .ValueEnd With