find and edit data from list

hi guys

I am desperate for something that will do the following:

I have a list on part numbers i.e 00111222
Some of them start with 00 some of them dont. What I need to do is find all of the ones that have 00 at the start and edit them. they need to have the first 0 removed and a . put two characters in. so the part number above would become 01.11222.
Any ideas?

Thanks

Hi,

thanks for the reply.

my boss has now changed his mind on what he wants

i now need it to remove any 000s at the start of the number and put the . two characters in. i.e. 00012345 would change to 12.345 and 0123654 would change to 12.3654.

Any ideas?

bump bump bump

Code:

=IF(LEFT(A1,1)="0",MID(A1,1,2)&"."&MID(A1,3,LEN(A1)-1),A1)
=IF(LEFT(A1,2)="00",MID(A1,2,2)&"."&MID(A1,4,LEN(A1)-1),A1)
=IF(LEFT(A1,3)="000",MID(A1,3,2)&"."&MID(A1,5,LEN(A1)-1),A1)

Try this code.
Not sure whether you meant this.

hey, thanks for the reply

this isnt quite it.

That formula adds the . two characters in but doesnt remove the 000s at the start.

It needs to remove any 0 characters at the start of the product code, then add a . two characters in.

so 0011122 would change to 11.122