Replacing characters with leading zeroes

[Demian Valle]

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?

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Demian Valle]

Thanks Ron. This is exactly what I needed.

Demian

 

[Ron Rosenfeld]

Thanks Ron. This is exactly what I needed.

Demian

You're welcome. Glad to help.


--ron