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]

My question, is there a VBA code to select the entire column, and change all text numbers into real numbers...all at once?

You should be able to do this quite easily with Text to Columns.
Just do the following:
- Select your column
- Change the format to a numeric one
- Select "Text to Columns" from the Data menu
- Click "Finish"

If you want this to be VBA code, just turn on the Macro Recorder and record the steps above.

 

[shaowu459]

there is another way:

1)select the column contains numbers as text;
2)Ribbon---Data---Text to columns---click Finishi directly.

 

[Guzzlr]

there is another way:

1)select the column contains numbers as text;
2)Ribbon---Data---Text to columns---click Finishi directly.

Thank you!

 

[Guzzlr]

Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.NumberFormat = "0"

The code above is working, the text in column E is converted to a number. However, what I really would need is for excel to search in Range (4:4) and find the heading word of "PID", then use the "PID" column to convert the text to numbers, instead of selecting Columns E:E...is this possible?
Thanks for the help

 

[Joe4]

Maybe something like this:

Sub MyTextToCol()

    Dim fCol As Long
    
    On Error GoTo err_chk
'   Find which column "PID" is in in row 4
    fCol = Rows("4:4").Find(What:="PID", After:=Range("A4"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Column
        
'   Perform text to columns on found column
    Columns(fCol).TextToColumns Destination:=Cells(1, fCol), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    
'   Reformat column
    Columns(fCol).NumberFormat = "0"
        
    On Error GoTo 0
    Exit Sub
    
err_chk:
'   Return error message if PID not found
    If Err.Number = 91 Then
        MsgBox "Cannot find PID in row 4!", vbOKOnly, "ERROR!!!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
        
End Sub

 

[Guzzlr]

Maybe something like this:

Sub MyTextToCol()

    Dim fCol As Long
    
    On Error GoTo err_chk
'   Find which column "PID" is in in row 4
    fCol = Rows("4:4").Find(What:="PID", After:=Range("A4"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Column
        
'   Perform text to columns on found column
    Columns(fCol).TextToColumns Destination:=Cells(1, fCol), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    
'   Reformat column
    Columns(fCol).NumberFormat = "0"
        
    On Error GoTo 0
    Exit Sub
    
err_chk:
'   Return error message if PID not found
    If Err.Number = 91 Then
        MsgBox "Cannot find PID in row 4!", vbOKOnly, "ERROR!!!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
        
End Sub

Ooops...I have more than 1: PID & SumOfBudget & type. They are all on Row 4 though.
Sorry

 

[Joe4]

Ooops...I have more than 1: PID & SumOfBudget & tpre...

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

 

[Joe4]

If my previous assumption is correct, then use this variation:

Sub MyTextToCol()

    Dim hdr As Variant
    Dim i As Long
    Dim fCol As Long
    
    Application.ScreenUpdating = False
    
'   Designate headers to look for
    hdr = Array([COLOR=#ff0000]"PID", "SumOfBudget", "tpre"[/COLOR])
                
    On Error GoTo err_chk
    
'   Loop through all values of array
    For i = LBound(hdr) To UBound(hdr)
    
'       Find which column "PID" is in in row 4
        fCol = Rows("4:4").Find(What:=hdr(i), After:=Range("A4"), LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Column
        
'       Perform text to columns on found column
        Columns(fCol).TextToColumns Destination:=Cells(1, fCol), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
    
'       Reformat column
        Columns(fCol).NumberFormat = "0"
        
    Next i
        
    On Error GoTo 0
    Application.ScreenUpdating = True
    Exit Sub
    
err_chk:
'   Return error message if PID not found
    If Err.Number = 91 Then
        MsgBox "Cannot find " & hdr(i) & " in row 4!", vbOKOnly, "ERROR!!!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
        
    Application.ScreenUpdating = True
        
End Sub

You would just update the part in red for all the column names that you want to apply it to.

 

[BenMiller]

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

Just a point, there's an easier way to do this: type the number 1 in any empty cell. Select that cell and CTRL+C. Then select the column you want to convert into numbers, then Right-Click, then CLICK on Paste Special (don't just hover over it), then under Operation, select Multiply and hit OK.