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
This question is related to
excel
vba
formatting
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
Source: Stackoverflow.com