Convert Index numbering to level number

[ketul shah]

Can anyone tell me how yo convert Index numbering to level format in excel ?
I have one column of bill of materials which consists Index numbering like 1,1.1,1.2....1.1.1,1.1.2

And i need to segregate index numbering level wise like level 1 , 2... Ect.

 

[Bosquedeguate]

Can anyone tell me how yo convert Index numbering to level format in excel ?
I have one column of bill of materials which consists Index numbering like 1,1.1,1.2....1.1.1,1.1.2

And i need to segregate index numbering level wise like level 1 , 2... Ect.

Please provide some sample data that you want to split or segregate.
Give before and after split examples.

 

[ketul shah]

Please provide some sample data that you want to split or segregate.
Give before and after split examples.

Thanks for reply
I have uploaded image for your reference.
Here I've received data as shown in column B and i want to formulate data for assigning level and position as shown in column C and D respectively.I have thousands of entries like this.

 

[AhoyNC]

Try:

Book1
	A	B	C	D
1
2		Index	level	Post#
3		1	1	1
4		1.1	2	1
5		1.1.1	3	1
6		1.1.1.1	4	1
7		1.1.1.2	4	2
8		2	1	2
9		2.1	2	1
Sheet2

Cell Formulas
Range	Formula
C3:C9	C3	=LEN(SUBSTITUTE(B3,".",""))
D3:D9	D3	=RIGHT(B3,1)

 

[ketul shah]

Try:

Book1
	A	B	C	D
1
2		Index	level	Post#
3		1	1	1
4		1.1	2	1
5		1.1.1	3	1
6		1.1.1.1	4	1
7		1.1.1.2	4	2
8		2	1	2
9		2.1	2	1
Sheet2

Cell Formulas
Range	Formula
C3:C9	C3	=LEN(SUBSTITUTE(B3,".",""))
D3:D9	D3	=RIGHT(B3,1)

Thanks for reply Ahoy....but this formula for column D will not work when i have Index like 100.1 or 100.1.10
I want conditional formula to count position upto 4 decimals index numbering system
Example :
Index 100.1.1.100
Level : 4
Postion: 10

 

[ketul shah]

Thanks for reply Ahoy....but this formula for column D will not work when i have Index like 100.1 or 100.1.10
I want conditional formula to count position upto 4 decimals index numbering system
Example :
Index 100.1.1.100
Level : 4
Postion: 10

Correction
Postion: 100

 

[Bosquedeguate]

Try this
Column with INDEXES must be formatted as Text as well as column for POST #

Book3
	A	B	C
1	INDEX	LEVEL	POST #
2	1	1	1
3	1.1	2	1
4	1.1.1	3	1
5	1.1.1.1	4	1
6	1.1.1.2	4	2
7	2	1	2
8	2.1	2	1
Sheet7

Cell Formulas
Range	Formula
B2:B8	B2	=LEN(A2)-LEN(SUBSTITUTE(A2,".",""))+1
C2:C8	C2	=IF(ISERROR(FIND(".",A2)),A2,MID(A2,FIND("~",SUBSTITUTE(A2,".","~",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))+1,LEN(A2)))

Book3
	A	B	C
9	100.1	2	1
10	100.1.10	3	10
11	100.1.1.100	4	100
Sheet7

Cell Formulas
Range	Formula
B9:B11	B9	=LEN(A9)-LEN(SUBSTITUTE(A9,".",""))+1
C9:C11	C9	=IF(ISERROR(FIND(".",A9)),A9,MID(A9,FIND("~",SUBSTITUTE(A9,".","~",LEN(A9)-LEN(SUBSTITUTE(A9,".",""))))+1,LEN(A9)))

 

[Peter_SSs]

Same suggestion as post #7 for Level, but a simpler one for Post

23 08 20.xlsm
	A	B	C
1	INDEX	LEVEL	POST #
2	1	1	1
3	1.1	2	1
4	1.1.1	3	1
5	1.1.1.1	4	1
6	1.1.1.2	4	2
7	2	1	2
8	2.1	2	1
9	100.1	2	1
10	100.1.10	3	10
11	100.1.1.100	4	100
Index to level & post

Cell Formulas
Range	Formula
B2:B11	B2	=LEN(A2)-LEN(SUBSTITUTE(A2,".",""))+1
C2:C11	C2	=--RIGHT(SUBSTITUTE(A2,".",REPT(" ",20)),20)

 

[Bosquedeguate]

Same suggestion as post #7 for Level, but a simpler one for Post

23 08 20.xlsm
	A	B	C
1	INDEX	LEVEL	POST #
2	1	1	1
3	1.1	2	1
4	1.1.1	3	1
5	1.1.1.1	4	1
6	1.1.1.2	4	2
7	2	1	2
8	2.1	2	1
9	100.1	2	1
10	100.1.10	3	10
11	100.1.1.100	4	100
Index to level & post

Cell Formulas
Range	Formula
B2:B11	B2	=LEN(A2)-LEN(SUBSTITUTE(A2,".",""))+1
C2:C11	C2	=--RIGHT(SUBSTITUTE(A2,".",REPT(" ",20)),20)

Wow! Awesome trick with the double "--"! I never knew this about Excel. Thanks!