Deciphering Formula!

[dannyok90]

Hi All,

please see the formula below.

I obtained this formula some time ago and always use it to number cells.


Id like to decipher it so instead of being 1.1, 1.2, 2.0, 2.1 etc.


When I set the top level number as 000 it numbers 001, 002 or when the top level is 100 its 101, 102 etc


Any help would be much appreciated


=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A9,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))),OFFSET(A9,-1,0,1,1)&".1",LEFT(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A9,-1,0,1,1),LEN(OFFSET(A9,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))-1)))+1)))

 

[Eric W]

Consider this formula:

	A
3	1
4	1.1
5	1.2
6	1.3
7	1.4
8	1.5
9	1.6
10	1.7
11	2
12	2.1
13	2.2
14	2.3
15	2.4
16	2.5
17	2.6
18	2.7
19	2.8
20	2.9
21	2.10
22	2.11
23	2.12
24	3
25	3.1

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula A4 =LEFT(A3,FIND(".",A3&".")-1)&"."&IFERROR(MID(A3,FIND(".",A3)+1,9)+1,1) <tbody> </tbody>

<tbody>
</tbody>

Put your starting value in A3. Put the formula in A4 and drag down as far as needed. If you want to start a new range, just put a whole number in and the formulas below it will adjust. I did that with rows 11 and 24.

 

[Scott Huish]

If you just want whole numbers, change the formula to =A8+1
Copy down.