find and replace with zero as the first digit in a cell

[George]

I have a spreadsheet with part number, many of which begin
with 0 and Excel automatically removed the 0. I want to
use find and replace for blocks of this to convert it back
but while it indicates it found and replaced X number of
entries, there is no chage to the data. I've got the cells
formatted as text and if I do the replacement one at a
time by typing it seems to hold.
Thanks

 

[Earl Kiosterud]

George,

Apparently your part numbers are numbers, not text. Excel stores the value
of a number, not not its digits, and leading zeroes don't play a part. You
can format it for leading zeroes (Format - Cells - Number - Custom:
000000
Or something along those lines.

You may need to convert your numbers to text if you depend on the actual
digits, including leading zeroes. When you formatted the column for text,
it didn't change the numbers to text until you retyped them. Instead of
that, you can convert them all to text with the following:

Insert a helper column with the following formula, copied down:
=TEXT(A2, "00000")
Adjust the count of zeroes you want. Now copy the helper column down. Now
select and copy it, then paste it back directly over the original part
numbers with Paste-Special - Values. Be certain that they've been pasted in
the correct place before you proceed (not off a row or two). Now you don't
need the helper column any more. Keep your part number column formatted for
text to get leading zeroes for future part numbers.

 

[Don Guillett]

Here's one way to try
Sub addonezero()
For Each c In Selection
c.NumberFormat = (Application.Rept("0", Len(c) + 1))
c.Value = "0" & c
Next
End Sub