[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


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

The solution that for me works is:

For Each xCell In Selection

  xCell.Value = CDec(xCell.Value)

Next xCell 

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

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

''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




I had this problem earlier and this was my solution.

With Worksheets("Sheet1").Columns(5)
    .NumberFormat = "0"
    .Value = .Value
End With

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

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to formatting

How to add empty spaces into MD markdown readme on GitHub? VBA: Convert Text to Number How to change indentation in Visual Studio Code? How do you change the formatting options in Visual Studio Code? (Excel) Conditional Formatting based on Adjacent Cell Value 80-characters / right margin line in Sublime Text 3 Format certain floating dataframe columns into percentage in pandas Format JavaScript date as yyyy-mm-dd AngularJS format JSON string output converting multiple columns from character to numeric format in r