On Tue, 27 Jul 2004 17:51:50 -0700, "Demian Valle"
wrote:

I am attempting to replace the character "O" with a
leading zero in a column. The column currently looks
something like this:

OOO55
OOO3
OOOOO567
OO1

I would like it to look like:

00055
0003
00000567
001

I have tried formatting the column as text and then doing
a find and replace. However, as soon as the replace
finishes the column format switches back to a number
format and strips the zeroes. Unfortunately, I can't use
a custom number format because there is no consistency to
the length of the numbers.

Any ideas?


=TEXT(SUBSTITUTE(A1,"O",""),REPT("0",LEN(A1)))


--ron