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; (306234.38, « »)

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

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
    • 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