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?

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

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?

Another thought. If you want to change the values in place, and have them come
out as a formatted number, you could use this UDF:

===================================
Sub foo()
Dim c As Range
Dim NumZeros As Integer

For Each c In Selection
NumZeros = Len(Trim(c.Text))
c.Value = Replace(c.Text, "O", "")
c.NumberFormat = Application.WorksheetFunction.Rept("0", NumZeros)
Next c

End Sub
=================================

--ron

Thanks Ron. This is exactly what I needed.

Demian


-----Original Message-----
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?

Another thought. If you want to change the values in
place, and have them come
out as a formatted number, you could use this UDF:

===================================
Sub foo()
Dim c As Range
Dim NumZeros As Integer

For Each c In Selection
NumZeros = Len(Trim(c.Text))
c.Value = Replace(c.Text, "O", "")
c.NumberFormat = Application.WorksheetFunction.Rept
("0", NumZeros)
Next c

End Sub
=================================

--ron
.

On Wed, 28 Jul 2004 08:53:27 -0700, "Demian Valle"
wrote:

Thanks Ron. This is exactly what I needed.

Demian

You're welcome. Glad to help.


--ron