Converting TEXT to NUMBER in VBA

[Joe4]

OK, I should know how to do this, but it is late in the day and I must be having a brain cramp!

On my Excel spreadsheet, I have a TEXT value in A1 that is all numbers, i.e. "000065200". I would like to write some VBA code that changes A1 to the numeric value . How do I do this?

 

[just_jon]

Always going to be all numbers?

 

[Legacy 98055]

'if you text is actually enclosed within quotes
Range("A1") = Mid(Range("A1"), 2, Len(Range("A1")) - 2)
Range("A1").NumberFormat = "#"
'If your text is not enclosed within quotes
Range("A1").NumberFormat = "#"

Tom

 

[Joe4]

The part that I am extracting is. The numbers are actually mixed in with a lot of garbage (spaces, text, symbols). However, it appears that the 9 left most characters will always be numbers. Unfortunately, some lines have blank spaces in front of them, which is why I can't just import it as a fixed width field (they don't always line up).

 

[just_jon]

Didn't think it was a keeper, but you might want to look here -

Public Function streal(sval As String) As Double 

    Dim isign As Integer, iflag As Integer, idiv As Integer 
    Dim rval As Double, rtemp As Double 
    
    rval = 0 
    rtemp = 0 
    isign = 1 
    iflag = 1 
    idiv = 1 
    
    ipos = Len(sval) 
    If (ipos = 0) Then 
        streal = 0# 
        Exit Function 
    End If 
    
    For i = 1 To ipos 
        If (Mid(sval, i, 1)) = " " Then 
            GoTo 9000 
        ElseIf (Mid(sval, i, 1)) = "+" Then 
            isign = 1 
            GoTo 9000 
        ElseIf (Mid(sval, i, 1)) = "-" Then 
            isign = -1 
            GoTo 9000 
        ElseIf (iflag = 1) And (Mid(sval, i, 1) >= "0") And (Mid(sval, i, 1) <= "9") Then 
            rval = (rval * 10) + (CDbl(Mid(sval, i, 1))) 
            GoTo 9000 
        ElseIf (Mid(sval, i, 1) = ".") Then 
            iflag = 0 
            GoTo 9000 
        ElseIf (iflag = 0) And (Mid(sval, i, 1) >= "0") And (Mid(sval, i, 1) <= "9") Then 
            idiv = idiv * 10 
            rtemp = (rtemp * 10) + (CDbl(Mid(sval, i, 1))) 
            GoTo 9000 
        Else 
        End If 
9000: 
        Next i 
        
        If (rtemp <> 0) Then 
            rtemp = rtemp / idiv 
        End If 
        streal = (rval + rtemp) * isign 
        
End Function

 

[Joe4]

I tried out TsTom's suggestion. It changes the format of the underlying cell, but that is not the problem. The problem is the values themselves need to be converted from text to numbers (and there are no quotes around them). Here are some examples of data I am trying to convert:

000065200ABCD
000012300

(Even though the numbers line up now, they don't in the original file. Some of the rows have extra blank spaces at the beginning which I am taking care of in a preliminary step).

In VBA, I need to convert the first 9 characters to a number. I figured out a way to do it, but it involves two steps:

Step 1: In a column to the right, enter an Excel formula using the VALUE and LEFT functions to pull the correct part and convert to a number. Here is the formula:
Cells(i, 5).FormulaR1C1 = "=VALUE(LEFT(RC[-1],9))"

Step 2: Do a copy & special paste to convert the formulas to values and delete the original column

I was hoping to do all this in one step in VBA.

 

[just_jon]

btw, there's a companion called strint, which is string to integer-only conversion. if it would serve a purpose, let me know if you want a sub or function version.

 

[Joe4]

just_jon,

I tried your UDF "streal". and it works fine. I guess what I was hoping to find was something real short and sweet, something like a VBA equivalent to the Excel function "VALUE".

Its funny how often times things which we think are simple (or should be) are often much more complicated than we think!

 

[just_jon]

Ain't it the truth. But, better than it used to be -- reason I wrote strint/streal was there *weren't* any free conversion subroutines around, and the version of f77 we had had the minimum functions.

 

[iHateUserNames]

OK, I should know how to do this, but it is late in the day and I must be having a brain cramp!

On my Excel spreadsheet, I have a TEXT value in A1 that is all numbers, i.e. "000065200". I would like to write some VBA code that changes A1 to the numeric value . How do I do this?

7 years late, but what the heck. It's never too late to learn something you should of known. From the Excel 2000 help file:

<CODE>

<CODE>Dim MyValue</CODE>
<CODE>MyValue = Val("2457")    ' Returns 2457.</CODE>
<CODE>MyValue = Val(" 2 45 7")    ' Returns 2457.</CODE>
<CODE>MyValue = Val("24 and 57")    ' Returns 24.</CODE></CODE> <CODE></CODE>


</PRE>