View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
vezerid
 
Posts: n/a
Default 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