How to format cells to recognize numbers like 1.1.1?

[GStrawley]

Hello,

This seems similar to Jaffo's problem of today, but a little
different.

I am entering some numbers as part of a decimal classification scheme
similar to the version numbers used in computer programs or the
chapters and subchapters in some books and reports. It is in the form
of "1.1.1, 1.1.2 ...."

I am trying to get Excel to recognize this format as numbers rather
than text so that numbers like 1.1.10 are sorted in the proper sequence
and go after 1.1.9. I tried entering "#.#.#" and even "##.##.##" in the
Format Cells - Special box, but nothing changed when I pasted the
numbers into the cells.

Is there a way of doing this or will I need to parse this
classification number into separate Excel columns?

Thanks,

George

 

[vezerid]

George,
I have been pondering on this problem for some time now. I can only
contribute this thought: If we replace 1 with A, 2 with B, 10 with J
etc, we ignore the "." and concatenate, then the words that are
created, if sorted alphanumerically, they will produce the desired
order.

However, there does not seem to be a single-cell formula that will do
this, it seems you have to break down in columns with something like:

=CHAR(64+MID(A1, 1, FIND(".",A1)-1))
=CHAR(64+MID(A1, FIND(".",A1)+1,
FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)))
etc,
or using helper columns to find the positions of the dots.

So yes, it seems that parsing is necessary, unless one builds a UDF to
produce the equivalent word in a single cell.

HTH
Kostis Vezerides

 

[Jim Cone]

George,

A lot of discussion has gone on about this type of sort...
http://groups.google.co.uk/groups?a...&as_ugroup=Microsoft.public.excel.*&lr=&hl=en

Also, David McRitchie has quite a bit of information...
http://www.mvps.org/dmcritchie/excel/sorttcp.htm

Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


in message
Hello,
This seems similar to Jaffo's problem of today, but a little different.
I am entering some numbers as part of a decimal classification scheme
similar to the version numbers used in computer programs or the
chapters and subchapters in some books and reports. It is in the form
of "1.1.1, 1.1.2 ...."

I am trying to get Excel to recognize this format as numbers rather
than text so that numbers like 1.1.10 are sorted in the proper sequence
and go after 1.1.9. I tried entering "#.#.#" and even "##.##.##" in the
Format Cells - Special box, but nothing changed when I pasted the
numbers into the cells.

Is there a way of doing this or will I need to parse this
classification number into separate Excel columns?
Thanks,
George