sorting numbers as text
 

I have a list of topic numbers that I sort via a VBA macro which
basically selects the column and hits the sort data button. The data
is numbers formatted as text and sorts by each digit. I'd really
rather it sort by the value following the decimal point.

For example, if I start with the list:
6.3
6.2
6.10
6.21
6.1

it will sort to:
6.1
6.10
6.2
6.21
6.3

but I'd like it to be:
6.1
6.2
6.3
6.10
6.21

Anybody have any ideas? This has been bugging me for some time.

Thanks.

sorting numbers as text
 

If you want a quick solution it would be to split the text strings into two
columns (using the Left() and Right() functions), convert the values to
numbers and make your VBA macro sort using these extra columns as keys.

"jafsonic" wrote:

I have a list of topic numbers that I sort via a VBA macro which
basically selects the column and hits the sort data button. The data
is numbers formatted as text and sorts by each digit. I'd really
rather it sort by the value following the decimal point.

For example, if I start with the list:
6.3
6.2
6.10
6.21
6.1

it will sort to:
6.1
6.10
6.2
6.21
6.3

but I'd like it to be:
6.1
6.2
6.3
6.10
6.21

Anybody have any ideas? This has been bugging me for some time.

Thanks.

sorting numbers as text
 

kimmy wrote:
If you want a quick solution it would be to split the text strings into two
columns (using the Left() and Right() functions), convert the values to
numbers and make your VBA macro sort using these extra columns as keys.

"jafsonic" wrote:

I have a list of topic numbers that I sort via a VBA macro which
basically selects the column and hits the sort data button. The data
is numbers formatted as text and sorts by each digit. I'd really
rather it sort by the value following the decimal point.

For example, if I start with the list:
6.3
6.2
6.10
6.21
6.1

it will sort to:
6.1
6.10
6.2
6.21
6.3

but I'd like it to be:
6.1
6.2
6.3
6.10
6.21

Anybody have any ideas? This has been bugging me for some time.

Thanks.

Thanks Kimmy, but I have one concern. The Left and Right functions require a specified number of digits. As you can see from my example, right("6.1",2) will yield ".1", but a right("6.10",2) will give me "10".

Hmm... maybe that will be ok since it will sort 0.1, 0.2, 0.3, 10, 21.


I'll just have to hope I never go over 6.99.
Is there a way to strip out the decimal so that it will sort 61, 62,
63, 610, 621, 699, 6101 ?

sorting numbers as text
 

=SUBSTITUTE(D3,".","")
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.htm



"jafsonic"

wrote in message
Thanks Kimmy, but I have one concern.
The Left and Right functions require a specified number of digits.
As you can see from my example, right("6.1",2) will yield ".1",
but a right("6.10",2) will give me "10".
Hmm... maybe that will be ok since it will sort 0.1, 0.2, 0.3, 10, 21.
I'll just have to hope I never go over 6.99.
Is there a way to strip out the decimal so that it will sort 61, 62,
63, 610, 621, 699, 6101 ?

sorting numbers as text
 

use mid() instead of right(),
mid(string, start_position, length), here length is optional. if you omit
it, mid() returns a string from start_position to the string end.

wang

"jafsonic" wrote:

Thanks Kimmy, but I have one concern. The Left and Right functions require a specified number of digits. As you can see from my example, right("6.1",2) will yield ".1", but a right("6.10",2) will give me "10".

Hmm... maybe that will be ok since it will sort 0.1, 0.2, 0.3, 10, 21.


I'll just have to hope I never go over 6.99.
Is there a way to strip out the decimal so that it will sort 61, 62,
63, 610, 621, 699, 6101 ?

sorting numbers as text
 

Hello,

I suggest to take my UDF, see file sort_chapter.xls:
http://www.bplumhoff.de/html/software.html

HTH,
Bernd

sorting numbers as text
 

Take this helper column:
=INT(--A1)*10+LEN(A1)-FIND(".",A1)--A1-INT(--A1)
Copy down.
Sort by this.

Regards,
Bernd

sorting numbers as text
 

I hadn't had a chance to thank you all for the good suggestions.

Thanks

wrote:
Take this helper column:
=INT(--A1)*10+LEN(A1)-FIND(".",A1)--A1-INT(--A1)
Copy down.
Sort by this.

Regards,
Bernd