Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change CSV-load cell format from GENERAL to TEXT for numbers? | Excel Discussion (Misc queries) | |||
Format numbers in chart datatable | Charts and Charting in Excel | |||
Microsoft Excell does not format the data numbers right justified. | Excel Discussion (Misc queries) | |||
Format cells as date | Excel Discussion (Misc queries) | |||
Protected cells -automatically format to a different color | Excel Discussion (Misc queries) |