Convert text to numbers

  • Thread starter Thread starter Conan Kelly
  • Start date Start date
C

Conan Kelly

Hello all,

When working in XL and it finds numbers stored as text, a smart tag pops up
letting you convert the text to numbers.

I'm assuming there is a way to do this programatically.......maybe something
like a "ConverTextToNumbers" method.......yes/no/maybe???

Thanks for any help anyone can provide,

Conan Kelly
 
try this macro:

Sub numerify()
Dim r As Range
Count = 0
For Each r In ActiveSheet.UsedRange
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub
 
Conan,

Here's an example:

Sub test()

Set ws = ActiveSheet
Set r = ws.Range("A:B").SpecialCells(xlCellTypeConstants)

For Each rr In r
If IsNumeric(rr.Value) Then
rr.NumberFormat = "General"
rr.Value = rr.Value
End If
Next

End Sub
 
Gary's Student,

Thanks for the help.

I would have though that there would have been some method part of the Error
object that would have done that automatically.

Guess not!!! That would be too easy!!!

Thanks again for all of your help,

Conan
 
Back
Top