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

Hi Scott - I agree that this is a good solution. However, I can hear the end-users now complaining that "everything's totaling to zero!!" already. Since VBA is already being used to run the script (I should have mentioned this in the question), in my opinion it would be best to just include code to avoid this conversation in the first place. — aLearningLady, Apr 21 '16 at 13:49
use CInt(Cell().Value) to get the value of the cell as number — gizlmo, Apr 21 '16 at 13:50
I've had a similar problem with currency values witch are formed by R$ 0,00 in BRL and the CCUR() realy made my day at that day. I think Cdec() would help. Check other functions on the link. [ How to use the CDEC Function (VBA)](https://www.techonthenet.com/excel/formulas/ccur.php) — Anderson Wanselowski, Apr 20 '18 at 22:56
I know it's been a while but it's the way you've entered the value that is probably incorrect. If you do: " cell.Value = "865" " the cell will be completed with a text instead of a number. If you don " cell.value = 865 " the cell will be completed with a number. — smirnoff103, Aug 23 '21 at 14:58

aLearningLady · Accepted Answer · 2019-01-04T20:02:29.200

52

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.

edited Jan 04 '19 at 20:02

answered Apr 21 '16 at 13:41

aLearningLady

1,988
4
24
42

2

On some sheets this populated a WHOLE column, while it originally contained only 200 rows. The `TextToCOlumn` method seems more reliable – iDevlop Aug 13 '18 at 10:00
1

Thanks, this worked after I modified to not use selection, instead something like, with mywb.sheets(1).range("E:E") – mx5_craig Sep 20 '21 at 08:37
to avoid the WHOLE column issue, add: `var = ActiveSheet.UsedRange.Rows.Count Range("E1:E" & var).Select` before: `Selection.Value = .Value` – Jason R. Escamilla Feb 14 '23 at 00:01

score 12 · Answer 2 · answered Apr 21 '16 at 15:20

12

I had this problem earlier and this was my solution.

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

answered Apr 21 '16 at 15:20

BerticusMaximus

705
5
16

score 12 · Answer 3 · answered Sep 02 '17 at 21:29

12

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

answered Sep 02 '17 at 21:29

Jones

181
2
4

1

Before, I had to remove "B" (billions) that was attached to the number. Just this solution worked, but I used `CDec` cause `CSng` was transforming 1.45 into 1.45000023, for instance. – xm1 Mar 26 '18 at 15:45

score 4 · Answer 4 · answered Jan 11 '17 at 15:39

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

TextTo Column is the best way. On some sheets I had issues with the `.Value = .Value` method (whole column being filled instead of the populated 200 rows) — iDevlop, Aug 13 '18 at 09:58

Mahesh S · Answer 5 · 2020-03-14T14:10:15.443

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

score 2 · Answer 6 · answered Sep 23 '21 at 19:03

2

The solution that worked for me many times is:

Sub ConvertTextToNumber()

With Range("A1:CX500") 'you can change the range 
.NumberFormat = "General"
.Value = .Value 
End With

End Sub

answered Sep 23 '21 at 19:03

Hakob TARPOSHYAN

21
1

score 1 · Answer 7 · answered Feb 22 '18 at 17:56

1

The solution that for me works is:

For Each xCell In Selection

  xCell.Value = CDec(xCell.Value)

Next xCell

answered Feb 22 '18 at 17:56

Camilo Vargas

11
1

score 1 · Answer 8 · answered Feb 10 '19 at 18:18

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

score 0 · Answer 9 · answered Jan 15 '19 at 07:07

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

While Text to Column is a good way to do this, You can only use it on one column. If you try to do the Text to Column solution then you should only use it One Column at a time. — sander126, Jul 10 '19 at 02:53

score 0 · Answer 10 · edited Dec 20 '22 at 12:22

From the recorded macro one gets the code below; for a new application you just need to update selection and range:

Sub num()

Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
End Sub

score -1 · Answer 11 · answered Dec 23 '21 at 12:13

-1

I had problems making above codes work. To me multiplying with 1 did the trick:-)

    Cells(1, 1).Select
    Cells(1, 1) = ActiveCell * 1

answered Dec 23 '21 at 12:13

Jacob Hald

29
2

VBA: Convert Text to Number

11 Answers11

Linked

Related