Converting Numbers Stored as Text, into Numbers

[Guzzlr]

Hello All,
I have an access query which exports numbers as text. In the past, I would simply insert a column, then multiply the text number by 1, and drag the formula all the way down. Then copy and and paste as values back over the column with text numbers.
My question, is there a VBA code to select the entire column, and change all text numbers into real numbers...all at once?
If I use the recorder, all it tells me is when I select the cell, it doesn't show the step where I convert the error drop-down to number.
Thanks for the help

 

[Joe4]

Just a point, there's an easier way to do this:

I wouldn't say that it is any easier than Text to Columns - it is practically the same number of steps.
Anyway, at this point, I think they are looking to automate it, not do it manually.

 

[Guzzlr]

Are you saying that you have more than one column that you need to apply "Text to Columns" to?

This is correct: "OAAC_Delivery_Order" & "PID" & "SumOfBudget"

 

[Joe4]

This is correct: "OAAC_Delivery_Order" & "PID" & "SumOfBudget"

Then my code back in post 9 should do what you want.
Just changed the part in red to match the three column names you are looking for.

 

[Rick Rothstein]

Assuming your headers are on Row 4 starting at Column A, here is another way to do this without using Text To Columns...

[table="width: 500"]
[tr]
	[td]Sub ConvertColumnOfTextNumbersToRealNumbers()
  Dim C As Long, Addr As String, Cell As Range
  Application.ScreenUpdating = False
  For C = 1 To Cells(4, Columns.Count).End(xlToLeft).Column
    If UBound(Filter(Array("OAAC_Delivery_Order", "PID", "SumOfBudget"), Cells(4, C).Value)) = 0 Then
      With Intersect(Columns(C), ActiveSheet.UsedRange)
        .NumberFormat = "General"
        .Value = .Value
      End With
    End If
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[Peter_SSs]

Some comments in the interest of ensuring the correct columns, and only the correct columns, are processed I think both post 9 and post 14 codes need adjustment.

Post 9
In the event that a column headed, say, "Spiders" occurred to the left of column "PID", the Spiders column would get processed and the PID column would not. Suggest that the Find should have LookAt:= xlWhole

Post 14
Addition columns with headings like (eg "ID", "Order" or "Budget") would also be processed when that may not be desired.

 

[Rick Rothstein]

Some comments in the interest of ensuring the correct columns, and only the correct columns, are processed I think both post 9 and post 14 codes need adjustment.

Post 14
Addition columns with headings like (eg "ID", "Order" or "Budget") would also be processed when that may not be desired.

Here is my adjustment...

[table="width: 500"]
[tr]
	[td]Sub ConvertColumnOfTextNumbersToRealNumbers()
  Dim C As Long, Addr As String, Cell As Range
  Application.ScreenUpdating = False
  For C = 1 To Cells(4, Columns.Count).End(xlToLeft).Column
    [B][COLOR="#008000"]'  Note the column header text must be surrounded by spaces in the Array function call[/COLOR][/B]
    If UBound(Filter(Array(" OAAC_Delivery_Order ", " PID ", " SumOfBudget "), " " & Cells(4, C).Value & " ")) = 0 Then
      With Intersect(Columns(C), ActiveSheet.UsedRange)
        .NumberFormat = "General"
        .Value = .Value
      End With
    End If
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[Peter_SSs]

Here is my adjustment...

To be more generally robust I would suggest that using a space is still pretty risky, though possibly not for this particular OP whose headings, from looking at the few examples, may well not include any spaces anyway. With, for example with

If UBound(Filter(Array(" OAAC_Delivery_Order ", " Account PID ", " SumOfBudget "), " " & Cells(4, C).Value & " ")) = 0 Then

a column with heading "PID" would get processed incorrectly. Better in my opinion to pick a character that will not occur in the headings as that delimiter at the edges of your values. The particular user would have to choose, but perhaps

If UBound(Filter(Array("|OAAC_Delivery_Order|", "|Account PID|", "|SumOfBudget|"), "|" & Cells(4, C).Value & "|")) = 0 Then

Another faster way

If InStr(1, "|OAAC_Delivery_Order|PID|SumOfBudget|", "|" & Cells(4, C).Value & "|", 1) > 0 Then

Or perhaps actually better to just find and process the particular columns more like Joe was doing rather than checking, who knows, possibly thousands of columns when only 3 need work.