Always going to be all numbers?
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 ...
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?
'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 = "#"
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).
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
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:
(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.
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!