Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change alphanumeric to numeric
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change alphanumeric to numeric
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change alphanumeric to numeric
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alphanumeric Sorting - numeric alpha numeric | Excel Worksheet Functions | |||
Obtain numeric from alphanumeric string | Excel Discussion (Misc queries) | |||
VLOOKUP with numeric and alphanumeric values | Excel Discussion (Misc queries) | |||
Converting Alphanumeric numbers to Numeric | Excel Worksheet Functions | |||
VBA - Looking to strip alphanumeric from numeric | Excel Programming |