Convert cell to number

Question

I'm working on creating a macro that will manipulate some data in Excel and create a report for me. The problem is that some of the provided data comes stored as text.

I'm pretty new at VB so my trick so far has been to recorded the desired action as a macro and then copy-paste that subroutine into my code. However, this doesn't seem to work in this case. What I normally do is to select the cells and then click the ! pop-up error menu that appears and select "Convert to Number".

Convert to Number dialog

What is the VB code that I need to convert selected cells from text to numbers?

This is a duplicate of [VBA to convert texts to numbers except formula and non-numeric texts](http://stackoverflow.com/questions/20408689/vba-to-convert-texts-to-numbers-except-formula-and-non-numeric-texts). That thread has a complete solution. — PeterT, Apr 03 '15 at 13:01
Thanks @PeterT, I looked for the solution (didn't think I would be the first) but it didn't come up in any of my searches. Maybe cause I didn't use VBA in my search query... Anyway, thanks again! — E_L, Apr 03 '15 at 13:03
See the addendum in [this thread](http://stackoverflow.com/questions/28920305/vba-remove-from-cell-content-in-a-range/28920651?s=3|0.9261#28920651). — , Apr 03 '15 at 13:05

score 4 · Answer 1 · answered Apr 03 '15 at 12:58

I think you are looking for something like this

Sub ConvertTextNumberToNumber()
 For Each WS In Sheets
 On Error Resume Next
 For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
 If IsNumeric(r) Then r.Value = Val(r.Value)
 Next
 Next
 End Sub

score 3 · Answer 2 · edited May 23 '17 at 12:09

3

To convert strings to numbers in VBA:

For individual cells, use CDbl:

myVar = "123"  'a string
Cells("A1") = CDbl(myVar)   'converted to double.

For a range:

Range("A1:A50").NumberFormat = "0"

For an entire column

Columns(1).NumberFormat = "0"

For more NumberFormat options, see this post.

edited May 23 '17 at 12:09

Community

1
1

answered Apr 03 '15 at 13:12

oortCloud

496
5
10

score 1 · Accepted Answer · answered Apr 03 '15 at 14:20

Thanks for your answers guys. Using the link provided by @PeterT and modifying the solution provided by @brettdj in said link, I have managed to answer my own question. The code I am using is:

Sub ConvertTextNumberToNumber(CellSelect)

Dim rng1 As Range

Sheets("Raw Data").Select

Range(CellSelect).Select
Set rng1 = Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeConstants, 2)
If rng1 Is Nothing Then Exit Sub
Cells(Rows.Count, Columns.Count).Copy
rng1.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
End Sub

I went for this code over other code provided because it was quicker, presumably because it didn't use a loop. I don't know how good the code is but it works for me.

Convert cell to number

3 Answers3