Macro to convert text to numbers

[howard]

I have numbers that when imported appear as text in Col F

I have tried to write code to convert these into numbers, but the text for eg '1.00. '3.00 etc will not convert to 1.00, 3.00 etc

Book1
	A
1	Stock Holding
2	1.00
3	1.00
4	1.00
5	1.00
6	1.00
7	1.00
8	2.00
9	3.00
10	1.00
11	1.00
12	2.00
13	1.00
Sheet3

 Sub convertTextToNumbers ()
 with Sheets(3)
. Range("F:F")
.NumberFormat = "General"
.Value = .Value
End With

End Sub

it would be appreciated if someone could assist me

 

[Eric W]

Try:

Sub convertTextToNumbers()
    With Sheets(3).Range("F:F")
        .NumberFormat = "General"
        .Value = .Value
    End With
End Sub

 

[MARK858]

Try...

Sub Howard()

    Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
                                 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, FieldInfo _
                                                                                             :=Array(1, 1), TrailingMinusNumbers:=True
End Sub

Please make sure that you have data/headers in F1. Obviously format your cell accordingly after the conversion.

 

[JoeMo]

This may work - depends on from where the data is imported.

Sub ConvertIt()
Dim R As Range, Vin As Variant, Vout As Variant, i As Long
Set R = Range("F1:F" & Cells(Rows.Count, "F").End(xlUp).Row)
Vin = R.Value
ReDim Vout(1 To UBound(Vin, 1), 1 To 1)
Application.ScreenUpdating = False
For i = 1 To UBound(Vin, 1)
    Vout(i, 1) = Val(Vin(i, 1))
Next i
With Range("A1:A" & UBound(Vin, 1))
    .Value = Vout
    .NumberFormat = "0.00"
End With
Application.ScreenUpdating = True

End Sub

 

[howard]

Thanks for the help guys. Code works perfectly

 

[Eric W]

Glad we could help.