Format zeros

[octet]

I have field that have numbers with zero,s like this 0002764357000 i would
like to
format the column so that the zeros before or after any number are not shown
does anyone know how to do this?

thanks
octet

 

[Mike]

=SUBSTITUTE(A1,0,"")

 

[octet]

hi Mike i am new to Exel do i insert this int condistional formating?

 

[Gary''s Student]

The formula will remove internal zeros as well as leading and trailing zeros.

 

[Gord Dibben]

Those are either numbers formatted with leading zeros or text.

If the latter, to get rid of the leading zeros, re-format as General, copy an
empty cell and select range of numbers and edit>paste special>add>ok>esc.

Now to deal with the trailing zeros...........do they all have 3 trailing zeros?

If so, enter 1000 in an empty cell and as above except use the "divide" option.


Gord Dibben MS Excel MVP