Convert Text to Number VBA

One of the little known ways to convert a text string which is a number but has been entered as text is to multiply the text by 1.  This is especially useful when applying a formula to convert a text to a number. You could also use the VALUE formula. However, I have always preferred multiplying by 1 as there is less typing.

Excel does of course have the text to number conversion tool however when you record a macro no code is created after you run the procedure which is odd. So what to do if you want to convert a string input into a number input inside a VBA procedure. I made a YouTube tutorial on the conversion of numbers displayed as strings back into numbers. The following is the start Excel file.

TexttoNum.xlsm

 
 

Here is an example of a number being displayed as text. Let’s say the data is in A1 and looks as follows:

‘100

If you were to use a helper column and used the following to be equal to 100 with this simple formula:

=A1 * 1

This would result in the text being equal to a number 100, which is what it should have equalled all along.  

The VBA procedure to Convert Text

The same can be done with VBA on the fly.  Try the following.  It will replicate the formula with an Excel VBA procedure.

Sub texttoNum() 'Basic Excel VBA procedure of a text to a number
Dim i

i="100"
i = i * 1
MsgBox Application.IsNumber(i)

End Sub

I have declared i as a variant above to keep the variant type open.  If I had declared the variable (i) as a String or an Integer then the procedure would have fallen over.  

Excel now has an inbuilt procedure which will change a whole batch of numbers from text to a number.  This has made things a lot easier when changing data in one simple action.  There is usually a green symbol in the top left of cell.  Highlight the whole range and click on this green text symbol

Text to Number vba

Notice the green symbol in the corner?  After highlighting the column click on the exclamation mark at the top.

Convert Text Excel

Click on Convert Text to Number.  

The VBA equivalent is as follows.  This used formula in the used cells.

Sub TexttoNum1()
Dim lr As Long
lr = Range("a" & Rows.Count).End(xlUp).Row

Range("B11:B" & lr) = "=A11*1"
Range("B11:B" & lr) = Range("B11:B" & lr).Value
End Sub

A More Involved VBA Procedure

The following will do the same thing as the above however it will run very quickly over large datasets as it runs in memory.  We will push the data into one array and output the data to a new array. The first procedure uses hard coded ranges while the second is nice and flexible with regard to the bottom of the range.

Sub texttoNum2() 'Excel VBA static example of the text to numbers procedure.
Dim ar As Variant
Dim var As Variant
Dim i As Integer

ar = [A11:A22)
ReDim var(1 To UBound(ar), 1 To 1)

For i=1 To UBound(ar) 'Start of VBA loop
var(i, 1)=ar(i, 1) * 1
Next i
[B11:B22] = var
End Sub

To add a bit of flexibility to the code. The ranges are dynamic in the following example.

Sub texttoNum3() 'Excel VBA dynamic example of the text to numbers procedure.
Dim ar As Variant
Dim var As Variant
Dim i As Long
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
ar = Range("A11:A" & lr)

ReDim var(1 To UBound(ar), 1 To 1)

For i=1 To UBound(ar) 'Start of VBA loop
var(i, 1)=ar(i, 1) * 1
Next i
Range("C11:C" & lr) = var
End Sub

The following Excel file outlines the VBA procedure in a practical example.