Remove Leading zero's up to the point of a positive number

[mattc_uk]

If I have a column with various numbers containing leading zero's (of different length), how can I get these leading zero's removed without removing any further zero's thereafter the first positive number in the cell

example

0000012340
0000006500
0007896541

I want these three cells to display as...

12340
6500
7896541

I can find no combination of =MID, Find and replace or =RIGHT that can do this for me

Please help

Regards
Matt

[Ali Kırksekiz]

Maybe

Please Login or Register  to view this content.

[NeedForExcel]

Hi, Just add 0 to all the entries..

So if those 3 Values are in Cells A1:A3, in B1, use =A1+0 and drag down..

[NeedForExcel]

If you do not wish to use a formula, on a blank cell enter 0 > copy that cell > Select the range on numbers with leading Zeros > Use Ctrl+Alt+V & V again > Select Add > Click On OK..

Cheers!

[mattc_uk]

Thanks msexcelathome

Your later example works. It would be handy to have a formula that could do the same thing. I may just resort to a macro if not.

Cheers

[NeedForExcel]

@ mattc_uk

Hi, there is no reason the 1st one should not work..

Can you attach a sample workbook where I can give you the solution?

[Pete_UK]

You have a formula in Post #3

Pete

[mattc_uk]

I've cracked it with an easier formula

The numbers I was getting were from part of a cell from another sheet (using an =MID statement). As I was already using an =MID all I had to do was bracket is and add -0) to the end ! So Simple!

Example

=(MID(Sheet1!A1:A500))-0

[NeedForExcel]

Just a tip, whenever a formula returns numbers that are really text, but look like numbers, all you have to do is perform any sort of mathematical calculation over it to convert it back to numbers.. Like you did, subtracting 0..

Adding 0, Subtracting 0, Multiplying by 1, will all do the same thing..

Cheers!