multiple statements IF

[JULIANA]

hi all,
iam new in excel, so dont have knowledge about it. I have the situation:
columns with 11 digit numbers. WE need to break it 5-4-2.
I need to remove the "0" when it is the first character of 5, then 4, then 2.
So i came up with this:

=REPLACE(B9,SEARCH("0",B9,6),1,"")

FIRST OF 5=SEARCH("0",B2,1)
FIRST OF 4=SEARCH("0",B9,6)
FIRST OF 2=SEARCH("0",B18,10)

BUT I NEED TO INCLUDE ALL THE 3 LOGIC TO THE FORMULA, SO I DID:

=REPLACE(B11, IF(ISNUMBER(SEARCH("0",B11,1)),SEARCH("0",B11,6),SEARCH("0",B11,10)),1,"")

THE LAST LOGIC (FIRST 0 OF 2 RIGHT NUMBERS) IS RESULTING IN A WRONG NUMBER. I DONT KNOW WHAT IM DOING WRONG HERE, I DONT WANT TO SPLIT THE NUMBERS AND DO EVERYTHING MANUALLY.

CAN SOMEONE HELP ME WITH THIS FORMULA, PLZ.
THANK YOU.

 

[Ingolf]

Try TextToColumn

 

[JULIANA]

Try TextToColumn

NO, THIS DOESNT WORK.

HERE R FEW EXEMPLES OF NUMBERS I HAVE:
00000010016
00000010020
00000010030
47335071483
49884083205
50383026915
50383026930
50383027060
51660014190
66685100100
66685100200
51672130200
51672131200

N I WANT THEM TO BECOME LIKE THIS:

[TABLE="width: 94"]
<colgroup><col></colgroup><tbody>[TR]
[TD]0000010016[/TD]
[/TR]
[TR]
[TD]0000010020[/TD]
[/TR]
[TR]
[TD]0000010030[/TD]
[/TR]
[TR]
[TD]4733571483[/TD]
[/TR]
[TR]
[TD]4988483205[/TD]
[/TR]
[TR]
[TD]5038326915[/TD]
[/TR]
[TR]
[TD]5038326930[/TD]
[/TR]
[TR]
[TD]5038327060[/TD]
[/TR]
[TR]
[TD]5166014190[/TD]
[/TR]
[TR]
[TD]6668510010[/TD]
[/TR]
[TR]
[TD]6668510020[/TD]
[/TR]
[TR]
[TD]5167213020[/TD]
[/TR]
[TR]
[TD]5167213120[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ingolf]

Sorry I could not reply until now, but I was in the hospital for surgery. Now I had access to the phone ...
Try this formula:
=IF(RIGHT(LEFT(A1,6),1)="0",LEFT(A1,5)&RIGHT(A1,5),REPLACE(A1,SEARCH("0",A1,11),1,""))