How to change alphanumeric to numeric

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!

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

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
--
HTH...

Jim Thomlinson


"borg" wrote:

> 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!

Thank you! It works great!



"Jim Thomlinson" wrote:

> 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
> --
> HTH...
>
> Jim Thomlinson
>
>
> "borg" wrote:
>
> > 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!