VBA Convert Text to Number

Question

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    I ve tried these snippets to no avail   Columns 5  NumberFormat    0    and   Selection PasteSpecial Paste  xlPasteValues  Operation  xlNone  SkipBlanks             False  Transpose  False

User · Answer

For large datasets a faster solution is required.

Making use of 'Text to Columns' functionality provides a fast solution.

Example based on column F, starting range at 25 to LastRow

Sub ConvTxt2Nr()

Dim SelectR As Range
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Sheets("DumpDB")

LastRow = sht.Cells(sht.Rows.Count, "F").End(xlUp).Row

Set SelectR = ThisWorkbook.Sheets("DumpDB").Range("F25:F" & LastRow)

SelectR.TextToColumns Destination:=Range("F25"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

End Sub

User · Answer

Convert text to Number with ZERO Digits and Number convert ZERO Digits    Sub ZERO DIGIT    On Error Resume Next  Dim rSelection As Range  Set rSelection   rSelection  rSelection Select  With Selection   Selection NumberFormat    General     Value    Value  End With  rSelection Select   Selection NumberFormat    0   Set rSelection   Nothing End Sub    Convert text to Number with TWO Digits and Number convert TWO Digits    Sub TWO DIGIT    On Error Resume Next  Dim rSelection As Range  Set rSelection   rSelection  rSelection Select  With Selection   Selection NumberFormat    General     Value    Value  End With  rSelection Select   Selection NumberFormat    0 00   Set rSelection   Nothing End Sub    Convert text to Number with SIX Digits and Number convert SIX Digits     Sub SIX DIGIT    On Error Resume Next  Dim rSelection As Range  Set rSelection   rSelection  rSelection Select  With Selection   Selection NumberFormat    General     Value    Value  End With  rSelection Select   Selection NumberFormat    0 000000   Set rSelection   Nothing End Sub

User · Answer

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

User · Answer

This converts all text in columns of an Excel Workbook to numbers   Sub ConvertTextToNumbers   Dim wBook As Workbook Dim LastRow As Long  LastCol As Long Dim Rangetemp As Range   Enter here the path of your workbook Set wBook   Workbooks Open  yourWorkbook   LastRow   Cells Find What       After  Range  A1    SearchOrder  xlByRows  SearchDirection  xlPrevious  Row LastCol   Cells Find What       After  Range  A1    SearchOrder  xlByColumns  SearchDirection  xlPrevious  Column  For c   1 To LastCol Set Rangetemp   Cells c  EntireColumn Rangetemp TextToColumns DataType  xlDelimited        TextQualifier  xlDoubleQuote  ConsecutiveDelimiter  False  Tab  True        Semicolon  False  Comma  False  Space  False  Other  False  FieldInfo         Array 1  1   TrailingMinusNumbers  True Next c End Sub

User · Answer

I had this problem earlier and this was my solution   With Worksheets  Sheet1   Columns 5       NumberFormat    0       Value    Value End With

User · Answer

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 If Next

User · Answer

The solution that for me works is   For Each xCell In Selection    xCell Value   CDec xCell Value   Next xCell

User · Answer

Using aLearningLady s answer above  you can make your selection range dynamic by looking for the last row with data in it instead of just selecting the entire column   The below code worked for me   Dim lastrow as Integer  lastrow   Cells Rows Count  2  End xlUp  Row  Range  C2 C   amp  lastrow  Select With Selection      NumberFormat    General       Value    Value End With

[excel] VBA: Convert Text to Number

Examples related to excel

Examples related to vba

Examples related to formatting