How to change text columns to numeric type.

[jal]

How to change text columns to numeric type.

I tried selecting the column and then

Format > Cells > Number > Number

and also this:

Format > Cells > Number > Currency

but not working. I'm guessing it won't show numeric totals until I get the column changed from text to numeric. There is a little green flag that affords a menu with "Convert to Number" but it only changes one row at a time. How do I change all the rows at once?

 

[jal]

Never mind, found it on the DAta > Text to Columns and then clicking Next through all the screen. Weird place for it but it works.

 

[lemo]

you may also want to take a look at the VALUE function..
l

 

[jal]

thanks, but I don't know how to apply the Value function to an entire column. I tried Insert > Function > Value and it asked me for a single cell. I had my cursor in the top cell of the desired column (D2). So I typed in D2 and it responded "Circular reference."

I suppose I could insert a temporary column and apply it to that instead? But then I would have an extra column in my grid?

 

[Brianwarnock]

If this is to be a reoccurring problem then look at this thread

Brian

 

[jal]

Very nice Brian, thanks !

 

[Brianwarnock]

You're welcome, pity I haven't a clue about your other copy and paste thread.

Brian

 

[Rx_]

This ws a great post!!!
Using automation code from Access - and a copyFromRecordSet where the SQL has a sum or other function - the SubTotal(101, H32:H41) would not work - a divide by zero
Note that the numbers in H32 to H41 had an error tag.
But, the usual convert Text to number would not work!!

The great post above shows how to find it.
A little more investigation - this is the code to add for automation

the VBA code for automation is:

Sub TextToColumns()
' 
' After a copy objXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsData
' where rsData is SQL with a sum or other function
Range("H32:H41").Select
Selection.TextToColumns Destination:=Range("H32"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
End Sub

 

[Rx_]

Just a side note:
When using the autofilter on the row 4
objXL.Selection.AutoFilter
and then running the code to convert string to numeric on row 5 and beyond
There seemed to be an inconistant error occuring.
That is, the code would work, then stop working. It required a stop and restart Access to remove the error once, but seemed to return the next time the code was run.
While there is no time to really experiment with this, the following appeared to get rid of the problem

Then I put the code below before the autofilter command
Have not had the error come back.

Don't know if it is just my environment or if it is Excel trying to re-evaluate the AutoFilter with new data type (since it is changing text to numbers)
It probably would be good practice to update and format all data types in advance in the range below where the autofilter is going to be applied.

'objXL.Visible = True 
    ' Any column that SQL uses formula to return number & text combination needs to be custom formated
 
1620            objXL.Range("H5:H5615").Select
1630    objXL.Selection.TextToColumns Destination:=Range("H5"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
1640     objXL.Range("M5:M5064").Select
1660            objXL.Selection.TextToColumns Destination:=Range("M5"), DataType:=xlDelimited, _
                TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=True, FieldInfo _
                :=Array(1, 1), TrailingMinusNumbers:=True
 
1700    objXL.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
            'objXL.Rows("4:4").Select
1720    objXL.Rows((intRowPos - 1) & ":" & (intRowPos - 1)).Select              ' based on relative position for where data starts using a variable above (not shown)
    objXL.Selection.AutoFilter                                              ' ---------------    Autofilter --------------- Autofilter -----------
                                                                            
 
                                                                            ' Subtotals added on header over specific columns based on data used
    ' STATE number of Days formula
1900    objXL.Range("G1").Select
1910    objXL.ActiveCell.FormulaR1C1 = "Max Days"
1920    objXL.Range("G2").Select
1930    objXL.ActiveCell.FormulaR1C1 = "Average Days"
1940    objXL.Range("H1").Select
1050    objXL.ActiveCell.FormulaR1C1 = "=SUBTOTAL(104,R[4]C:R[579]C)"
1960    objXL.Range("H2").Select
1970    objXL.ActiveCell.FormulaR1C1 = "=SUBTOTAL(101,R[3]C:R[5798]C)"
 
 
    ' Federal number of days formula
2000    objXL.Range("L1").Select
2010    objXL.ActiveCell.FormulaR1C1 = "Max Days"
2020    objXL.Range("L2").Select
2030    objXL.ActiveCell.FormulaR1C1 = "AveraLe Days"
2040    objXL.Range("M1").Select
2050    objXL.ActiveCell.FormulaR1C1 = "=SUBTOTAL(104,R[4]C:R[579]C)"
2060     objXL.Range("M2").Select
2070   objXL.ActiveCell.FormulaR1C1 = "=SUBTOTAL(101,R[3]C:R[5798]C)"
 
' Note: these SubTotal functions returned divide by 0 or just 0 before changing the cells
' The Excel menu Cell Format Numeric would not actually change the cells from a string to a numeric type
' As explained above, the DATA menu offers the solution
' This is the code to accomplish the same thing using automation

 

[Rx_]

So sorry to post yet one more time!!
Updated
two post below.
Microsoft has a problem with ranges (in charts too) were the global variale persist even when the Excel object has been destroyed.

This is only a proble with code. And, only a problem if the user calls on the code two or more times.
see code below
put currentworksheet. in front of range objects
From another web site where code behaved the same the second or more times (works first time, then does not work again)
"...when your program ends the first time the chart is the selected and the unreferenced: Range(... will throw an error. Just put an: ActiveSheet. in front of Range. The dot after ActiveSheet needs to be there."

 

[Gunilla]

Hi I have a VBA code that I use when I have this problem.
In this code you have to change the C to whatever column you have.

Sub TextToNumbers()

Dim rnOmrade As Range
Dim vaData As Variant
Dim i As Integer


Set rnOmrade = Range(Range("C2"), Range("C65536").End(xlUp))

vaData = rnOmrade.Value

For i = 1 To UBound(vaData)
vaData(i, 1) = vaData(i, 1) * 1
Next i

rnOmrade.Value = vaData
End Sub

I hope it works for you too.
Gunilla

 

[Rx_]

Thanks so much - I am useing this with a modification.
And, I probably found the fix for the other code above.

After a Google search, I found another article that showe this to be a known problem for Range (including Charts) when the report is run over and over again.

According to them (and I feel this is exactly what fixed it) claim that putting ActiveSheet. in front of Range will fix it.

As you can see, like eating chicken soup when your sick ... " it couldn't hurt"
Well, I have run my code over 30 times one after another with out the global fault. Before, your code had the exact same error the second time.
Your code is nice because there is additional logic that can be added.

This solution cost me more time than designing everything from the SQL query, the formats, and the rest. I will never forget it!

Thanks again for the code. Once I got the exact same error, it put me on the track to look at other options.

Set objXL = New Excel.Application

Set rnOmrade = Nothing
Set rnOmrade = objXL.ActiveSheet.Range(objXL.Range("M5"), objXL.Range("M6536").End(xlUp))
vaData = rnOmrade.Value
For i = 1 To UBound(vaData) ' data base record set returns "Pending", blank or a number
If (vaData(i, 1) = "Pending") Then
vaData(i, 1) = "Pending"
ElseIf ((vaData(i, 1) * 1) = 0) Then
vaData(i, 1) = ""
Else
vaData(i, 1) = vaData(i, 1) * 1
 
End If
'vaData(i, 1) = IIf(IsNumeric(vaData(i, 1) * 1), vaData(i, 1) * 1, vaData(i, 1))
Next i
Err.Clear ' one unknown variant type can mess up the whole array
rnOmrade.Value = vaData

 

[Gunilla]

Thank you for showing me why I sometimes get the error about global fault. I only gets it once in a while, and I have never thought about goggle it.
Of course I usually only use it once on the sheet as the information is imported from text files.
Gunilla

 

[Rx_]

I did not find this on this site (my favorite for both Access and Excel).
Our solution turned out to be different more so because of this global error.
I am wondering if we should post part of this on a new thread so the key words can be found.
It was not easy to find.

 

[Gunilla]

I think it is a good idea to post a new thread. Will you do it? I think it is a common problem as so many people are importing text files to Excel and Access. I don't know how many times I have used "mine" solution. I do agree, this is a very good place to find solutions to both hard and easy problems.
Gunilla

 

[Rx_]

Thanks, did it under the VBA code section.

Had some comments back about never using the CurrentWorksheet.
And, in general I totally agree with that.

So, I have asked if there is a better alternative.