### Tags: automatically, blocks, cell, digit, excel, microsoft, msdn, number, software, spreadsheet, zero

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

On Microsoft » Microsoft Excel

3,017 words with 2 Comments; publish: Thu, 22 May 2008 22:51:00 GMT; (30670.31, « »)

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

*http://excel.todaysummary.com/q_microsoft-excel_175665.html*

All Comments

Leave a comment...

- 2 Comments
- 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.

--

Earl Kiosterud

mvpearl omitthisword at verizon period net

---

"George" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message

news:2f32601c46cfc$c60548d0$a501280a.excel.todaysummary.com.phx.gbl...

> 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

#1; Thu, 22 May 2008 22:52:00 GMT

- George,
- 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

--

Don Guillett

SalesAid Software

donaldb.excel.todaysummary.com.281.com

"George" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message

news:2f32601c46cfc$c60548d0$a501280a.excel.todaysummary.com.phx.gbl...

> 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

#2; Thu, 22 May 2008 22:53:00 GMT

- Here's one way to try