leading zeroes

  • Thread starter Thread starter PatD
  • Start date Start date
P

PatD

When I format my cells to show leading zeroes (i.e.
000141) it will not take. It always shows 141. In our
school managment system each school has a IRN number. In
my spreadsheet I am downloading info from the system and
want the school number to be change to the school name.
In my formula the zeroes will not show, so therefore the
formula will not work. For example: 000141 = Brown
Elementary. Help!
 
You should be able to go to format> custom> and use something like 000######

As for your formulas not working, you need to make sure you are looking up
numbers as numbers or text as text. You can convert these text numbers (like
'002132) to actual numbers by putting -- next to them.

=--A1=B1
 
Hi Pat,
Format your columns as text before entry. Since
you have existing entries you want to fix up you
might want to use a macro so greatly speed up
the process.

Changing the number formatting will not make the
existing numbers behave as text with leading zeros. You
need the usage to match in your data and your
lookup. You can use a macro to change the
numbers to text entries, so you can use your
existing tables. The following will not process
text cells, or formulas. Format the selection as
text before running the macro for best results.

Sub Force_TextDigits_6()
Dim cell As Range 'DMcRitchie 2004-05-06
On Error Resume Next 'in case nothing found
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlNumbers))
cell.NumberFormat = "@"
cell.Value = Format(cell.Value, "000000")
Next cell
Application.Calculation = xlAutomatic
Application.ScreenUpdating = False
End Sub

If not familiar with installing using macros see
my getstarted.htm

---HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

=>
 
Back
Top