[excel] VBA: Convert Text to Number

I have columns of numbers that, for whatever reason, are formatted as text. This prevents me from using arithmetic functions such as the subtotal function. What is the best way to convert these "text numbers" to true numbers?

Here is a screenshot of the specific issue: Error

I've tried these snippets to no avail:

Columns(5).NumberFormat = "0"

and

 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

This question is related to excel vba formatting

The answer is


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].Select
With Selection
    .NumberFormat = "General"
    .Value = .Value
End With

P.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.


Similar questions with excel tag:

Similar questions with vba tag:

Similar questions with formatting tag: