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}))
|