How to change alphanumeric to numeric

[Guest]

Hi,

I have bunch of "numbers" in column 1 like

2
9
15
71
8

but when I sort, the 9 is at the bottom of the list because it is not truly
numeric.

From what I've read in this forun, I was able to identify the cells that are
not numeric by using:

Sub test()
Dim rng As Range

Set rng = ActiveSheet.Columns(1).SpecialCells(xlConstants, xlTextValues)
rng.Select
End Sub

Now that I am able to identify the locations of the cells, is there a quick
way to change them to numeric values?

Thanks!

 

[JakeyC]

My favourite 'trick' for performing this task is to multiply everything
(including text) by 1. This forces numbers to be treated as such.

The quickest way to do it is to copy a cell containing the number 1 and
PasteSpecial values with operation 'multiply'.

HTH

 

[Guest]

Since you have narrowed down the data set this is not too bad to do...

Sub test()
Dim rng As Range
Dim rngCurrent as Range

Set rng = ActiveSheet.Columns(1).SpecialCells(xlConstants, xlTextValues)
for each rngCurrent in rng
If IsNumeric(rngCurrent.Value) Then
rngCurrent.NumberFormat = "0"
rngCurrent.Value = CDbl(rngCurrent.Value)
rngCurrent.Formula = rngCurrent.Value
End If
next rngCurrent

 

[Guest]

Thank you! It works great!

Since you have narrowed down the data set this is not too bad to do...

Sub test()
Dim rng As Range
Dim rngCurrent as Range

Set rng = ActiveSheet.Columns(1).SpecialCells(xlConstants, xlTextValues)
for each rngCurrent in rng
If IsNumeric(rngCurrent.Value) Then
rngCurrent.NumberFormat = "0"
rngCurrent.Value = CDbl(rngCurrent.Value)
rngCurrent.Formula = rngCurrent.Value
End If
next rngCurrent