Delete leading zeros

**pipoliveira** · 04-25-2013, 04:52 AM

Dear All,

I hope you are all well.

I have a cell (let's say cell L19) where I have a number as following:

0020079747 0000009857 2013

This number is taken from an internal system. What I need is a formula in Cell "K19" that populates only the following number: 9857 2013

Now, on some cases, where is the number "9857" (4 digits on this example), could have more numbers (e.g. 0020079747 0000078952 2013).

I am oly using this formula: "=MID(L19,12,15)" but this returns the leading zeros "0000009857 2013".

By any chance, anyone knows some formula or function?

You help will be highly appreciated.

Best regards,
Filipe Oliveira

**martindwilson** · 04-25-2013, 05:03 AM

=MID(L19,12,10)+0&" "&RIGHT(L19,4) maybe
or
=MID(L19,12,10)+0&RIGHT(L19,5)

**benishiryo** · 04-25-2013, 05:11 AM

hi pipoliveira. assuming length always different but always in sets of 3 numbers:
=MID(L19,FIND(" ",L19)+1,FIND(" ",L19,FIND(" ",L19)+1)-FIND(" ",L19)-1)+0&MID(L19,FIND(" ",L19,FIND(" ",L19,FIND(" ",L19)+1)),255)