Sorting numbers vs. numeric text

[Excel dweeb]

I have values in a variant array that I would like to sort; sometimes
the elements will be text, sometimes numbers. The trouble is that my
selection sort is treating even the numbers like text so that (quoting
from an earlier post by Dianne):

"If your column is populated with numbers, then it will sort
1,2,10,20.

If your column is populated with numeric text, then it will sort
1,10,2,20."

Is there a way to tell Excel (via VBA) to convert this numeric text to
numbers so that I can sort it properly programmatically? How to tell
if a specific column really contains text (like names) vs. numbers
will be another problem.

Any help greatly appreciated.

 

[Jerry W. Lewis]

I don't know of a way to get sort to treat text as numeric, but you can
easily convert the text to numeric. Simply copy a numeric zero, select
the data range, and Edit|Paste Special|Add.

Jerry

 

[Dave Peterson]

If you upgrade to xl2002, you'll get a prompt to sort numbers and text together
(like numbers) or separately (like xl2k and before).

One way around it is to use a helper column and convert text to numbers or
numbers to text and then sort your range by that column.

=a1&""
will convert the number in A1 to text
and so will =text(a1,"0000")
and this'll give you more control how the formatting will go.

And
=--A1
will convert the text number to a number number (if you get my drift).

 

[Excel dweeb]

I have values in a variant array that I would like to sort; sometimes
the elements will be text, sometimes numbers. The trouble is that my
selection sort is treating even the numbers like text so that (quoting
from an earlier post by Dianne):

"If your column is populated with numbers, then it will sort
1,2,10,20.

If your column is populated with numeric text, then it will sort
1,10,2,20."

Is there a way to tell Excel (via VBA) to convert this numeric text to
numbers so that I can sort it properly programmatically? How to tell
if a specific column really contains text (like names) vs. numbers
will be another problem.

Any help greatly appreciated.

Thanks for the replies. Because I want to do this in VBA rather than
using a spreadsheet in between (to save time), I looked at *why* Excel
was sorting by text rather than numbers and identified the variant
array as the culprit.

Testing "IsNumeric(x(i))" gave me "TRUE" for the numeric text and
"FALSE" for the straight text, e.g., names. In the latter case, I want
to sort as text, so no problem. The solution for the former case is to
copy those "quasi-numbers" to another temporary vector typed as
Double. Sorting a Double vector results in the elements being ordered
as numbers.