Thursday, June 11, 2009

Numbers as Text in Excel 2007

It’s an old issue. Goes back as far as Lotus 1-2-3. Yes, the classic DOS edition. Maybe even to Visicalc but I’m not sure. These spreadsheet programs of course accept numbers and calculate numbers. Text can be secondary. But what happens when you have to enter leading zeroes in product codes or mobile phone numbers? The leading zero is swallowed up by the numeric expectation.

Excel 2007 has a nice solution. Thing is, it’s not phrased in help in such a way that easily comes to surface. Search Excel Help for Format As Text.

For pro-active work, select a range of cells and apply numeric formatting. Yes, Yes. Numeric formatting. But you nominate the numeric format called Text. Guess what? Not only is the selection formatted as text (e.g. with leading apostrophe) but future data entry into that range will retain the text entry behaviour. So you don’t have precede your intended number with an apostrophe – and your leading zeroes are preserved, of course.

For post processing, your zeroes have already been sucked into the null bucket. So using this technique will not restore them. Use the TEXT function to manufacture the leading zeroes in a new cell.

No comments: