How to format cells to recognize numbers like 1.1.1?
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 -- GStrawley ------------------------------------------------------------------------ GStrawley's Profile: http://www.excelforum.com/member.php...o&userid=26968 View this thread: http://www.excelforum.com/showthread...hreadid=505811 |
How to format cells to recognize numbers like 1.1.1?
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 |
How to format cells to recognize numbers like 1.1.1?
George,
A lot of discussion has gone on about this type of sort... http://groups.google.co.uk/groups?as...el.*&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 "GStrawley" wrote 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 |
All times are GMT +1. The time now is 09:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com