data transpose as per digit

Hi Experts,
I need formula to transpose data like result should be like that, if 1.0 it will be level 1, 1.1,1.2 etc it will be level 2, if 1.1.1 or 1.1.2 etc it will be level 3, colculsion is if 1 digit it will be level 1, if 2 digit it will level 2, if 3 digit 3 it will level 3 so on....


Thanks in Advance.

Farhan

If you are happy to change the 1.0 to 1 you could use this in E3 copied down and acroos

Formula:

Please Login or Register  to view this content.

Then change the red 1 to 3, 5 7 & 9 for columns F, G etc

not showing any result.

It does for me

Excel 2013 32 bit

	E	F	G	H	I
2	Level 1	Level 2	Level 3	Level 4	Level 5
3	Farhan	Inam	Shah	Noman	Word
4			Khan	Qadir	Duty
5			Gul	Excel	Book
6			Rizwan		Booker

Sheet: Sheet1

Excel 2013 32 bit

	E	F	G	H	I
3	=IFERROR(INDEX($B$3:$B$15,AGGREGATE(15,6,ROW($B$3:$B$15)-ROW($B$3)+1/(LEN($A$3:$A$15)=1),ROWS($1:1))),"")	=IFERROR(INDEX($B$3:$B$15,AGGREGATE(15,6,ROW($B$3:$B$15)-ROW($B$3)+1/(LEN($A$3:$A$15)=3),ROWS($1:1))),"")	=IFERROR(INDEX($B$3:$B$15,AGGREGATE(15,6,ROW($B$3:$B$15)-ROW($B$3)+1/(LEN($A$3:$A$15)=5),ROWS($1:1))),"")	=IFERROR(INDEX($B$3:$B$15,AGGREGATE(15,6,ROW($B$3:$B$15)-ROW($B$3)+1/(LEN($A$3:$A$15)=7),ROWS($1:1))),"")	=IFERROR(INDEX($B$3:$B$15,AGGREGATE(15,6,ROW($B$3:$B$15)-ROW($B$3)+1/(LEN($A$3:$A$15)=9),ROWS($1:1))),"")

Sheet: Sheet1

I'm using Microsoft Excel 2007

As far as I'm aware all those functions are available in 2007, if they weren't you'd get the #NAME error.

Thank You Experts

You're welcome & thanks for the feedback