View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default How to evaluate a 'dotted' number using formulae (not UDF)

Alan wrote...
....
I think the maximum I have seen is three dots and minimum is one dot
(never just a numeric). Happy to make that assumption.

....
If we could find a way to force all segments to have, say, three
digits it would become trivial:

8.1.81 - 008.001.081 - 008001081

8.17.1 - 008.017.001 008017001


Could any of the segments be 3 or more digits? I'll assume not.

One possibility for numeric encoding involves using a defined name like
seq referring to

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,64,1))

Then you could convert such a composite value in A1 into a distinct
number using the array formula

=SUM(MID(A1&".0.0.0",SMALL(IF(MID("."&A1&".0.0.0", seq,1)=".",seq),{1;2;3;4}),
SMALL(IF(MID(A1&".0.0.0.",seq,1)=".",seq),{1;2;3;4 })-SMALL(IF(MID("."&A1&".0.0.0",
seq,1)=".",seq),{1;2;3;4}))*100^(4-{1;2;3;4}))