Stripping leading zeros

  • Thread starter Thread starter macsmaker
  • Start date Start date
M

macsmaker

Why does Excel 2007 insist upon stripping leading zeros? Here's an
example:
Assume this data in a column:
ABC0001
ABC0002
ABC0003

Even if this column is defined to be text, if you do a group replace
of ABC to nothing, what will left is
1
2
3
NOT
0001
0002
0003 which is the desired result. If you edit individual cells, the
leading zeros are preserved. But using replace, the leading zeros are
removed.
 
Why does Excel 2007 insist upon stripping leading zeros? Here's an
example:
Assume this data in a column:
ABC0001
ABC0002
ABC0003

Even if this column is defined to be text, if you do a group replace
of ABC to nothing, what will left is
1
2
3
NOT
0001
0002
0003 which is the desired result. If you edit individual cells, the
leading zeros are preserved. But using replace, the leading zeros are
removed.
Click to expand...

Yes, very annoying indeed. What happens if you change replacing the
string ABC with nothing to replacing it with with a single quotation
mark (')?
 
Format cells to: custom, 0000.

When you remove ABC via replace the numbers will have 4 leading zeros.
 
Back
Top