Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Converting TEXT to NUMBER in VBA

This is a discussion on Converting TEXT to NUMBER in VBA within the Excel Questions forums, part of the Question Forums category; OK, I should know how to do this, but it is late in the day and I must be having ...

  1. #1
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,522

    Default Converting TEXT to NUMBER in VBA

    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?

  2. #2
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default

    Always going to be all numbers?
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

    '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

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,522

    Default

    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).

  5. #5
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default

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

    Code:
    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
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,522

    Default

    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.

  7. #7
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default

    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.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,522

    Default

    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!

  9. #9
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default

    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.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  10. #10
    New Member
    Join Date
    May 2008
    Posts
    9

    Default Re: Converting TEXT to NUMBER in VBA

    Quote Originally Posted by Joe4 View Post
    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:
    
    
    Dim MyValue
    MyValue = Val("2457")    ' Returns 2457.
    MyValue = Val(" 2 45 7")    ' Returns 2457.
    MyValue = Val("24 and 57")    ' Returns 24. 
    
    
    

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com