# MS Excel question: numbers that start with ZERO (such as 0258) **URL:** \ **Category:** Factual Questions\ **Created:** [August 10, 2005, 6:13pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480 "2005-08-10T18:13:46Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![OpalCat](https://avatars.discourse-cdn.com/v4/letter/o/8edcca/32.png) [@OpalCat](https://boards.straightdope.com/u/OpalCat)\ **Post date:** [August 10, 2005, 6:13pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/1 "2005-08-10T18:13:46Z")
I need to have a form generated with a list of numbers down a column starting with a particular number. I have a formula set up that just takes the previous row’s number and adds one, so the column automatically increments… …the problem is that the numbers have to be four digits, and I’m starting at 0569. I can’t make Excel stop changing “0569” into “569”. I mean, I can change the top one where I entered the number to be formatted as “text” and then IT shows up as 0569, but all the ones thereafter omit the zero. HEEEEELP!!! (The person who did this before me just manually entered every number… I don’t want to do that.) ---
**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\ **Post date:** [August 10, 2005, 6:16pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/2 "2005-08-10T18:16:00Z")
Highlight the cells that you want to enter data into and choose Format - Cells - Text. ---
**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\ **Post date:** [August 10, 2005, 6:19pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/3 "2005-08-10T18:19:17Z")
Sorry. That was a simplistic answer and incorrect. I will be back in a minute. ---
**Author:** ![MagicEyes](https://avatars.discourse-cdn.com/v4/letter/m/5daacb/32.png) [@MagicEyes](https://boards.straightdope.com/u/MagicEyes)\ **Post date:** [August 10, 2005, 6:23pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/4 "2005-08-10T18:23:28Z")
Format cells/Number–the last option is “Custom.” In the “Type” box, put a 0 for every digit you want (four 0’s for four digits). ---
**Author:** ![pulykamell](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/pulykamell/32/3166_2.png) [@pulykamell](https://boards.straightdope.com/u/pulykamell)\ **Post date:** [August 10, 2005, 6:23pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/5 "2005-08-10T18:23:51Z")
> [@Shagnasty](#): > > Highlight the cells that you want to enter data into and choose Format - Cells - Text. How about: Format - Cells - Custom And in the Custom input box, type “000#” If you type 1, it will appear as “0001” If you type 234, it will appear as “0234” Apply this to the rows or columns as needed. ---
**Author:** ![bordelond](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bordelond/32/150_2.png) [@bordelond](https://boards.straightdope.com/u/bordelond)\ **Post date:** [August 10, 2005, 6:24pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/6 "2005-08-10T18:24:21Z")
**Magic Eyes** beat me by moments, but has given the correct answer. ---
**Author:** ![pulykamell](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/pulykamell/32/3166_2.png) [@pulykamell](https://boards.straightdope.com/u/pulykamell)\ **Post date:** [August 10, 2005, 6:24pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/7 "2005-08-10T18:24:58Z")
> [@MagicEyes](#): > > Format cells/Number–the last option is “Custom.” In the “Type” box, put a 0 for every digit you want (four 0’s for four digits). Curses! Beat me to it. I guess “0000” will work just as well as “000#” ---
**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\ **Post date:** [August 10, 2005, 6:30pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/8 "2005-08-10T18:30:09Z")
Ok, enter the following formula in the cell directly below your starting value (e.g 0258). =RIGHT(CONCATENATE(10000+A1+1),4) Replace A1 with whatever cell your beginning number is in. This should display your starting value +1 with a zero in front of it right below your starting value. Highlight the cell with the formula in it and Edit - Fill Down for all the cells that you wish to diplay and increment of the original value. How the formula works: It looks at the cell above it and creates an incremented value with a one as the leading digit (e.g. 10258). Then it goes back and selects the first 4 digits from the right leaving out the 1. ---
**Author:** ![OpalCat](https://avatars.discourse-cdn.com/v4/letter/o/8edcca/32.png) [@OpalCat](https://boards.straightdope.com/u/OpalCat)\ **Post date:** [August 10, 2005, 6:50pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/9 "2005-08-10T18:50:55Z")
Hooray!!! Thanks everyone for your help! _puts ointment on forehead where repeated banging against desk didn’t seem to help much_ ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [August 10, 2005, 7:02pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/10 "2005-08-10T19:02:53Z")
In general, if you’re having trouble with Excel auto-interpreting your text as some sort of other data, just stick a ’ in front of it. '0258 will render as 0258 instead of 258. ---
**Author:** ![MissTake](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/misstake/32/3189_2.png) [@MissTake](https://boards.straightdope.com/u/MissTake)\ **Post date:** [August 10, 2005, 7:05pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/11 "2005-08-10T19:05:50Z")
Rather than opening a NEW Excel thread, may I piggyback your thread? Working a long spreadsheet with Row 1 as my header. How do I keep that row static? I don’t want to keep scrolling up/down to remember what goes in what cell. ---
**Author:** ![Madd\_Maxx](https://avatars.discourse-cdn.com/v4/letter/m/5f9b8f/32.png) [@Madd\_Maxx](https://boards.straightdope.com/u/Madd_Maxx)\ **Post date:** [August 10, 2005, 7:15pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/12 "2005-08-10T19:15:59Z")
Highlight the row, click window, then click freeze panes. ---
**Author:** ![misshannah](https://avatars.discourse-cdn.com/v4/letter/m/3e96dc/32.png) [@misshannah](https://boards.straightdope.com/u/misshannah)\ **Post date:** [August 10, 2005, 7:17pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/13 "2005-08-10T19:17:28Z")
> [@MissTake](#): > > Rather than opening a NEW Excel thread, may I piggyback your thread? > > Working a long spreadsheet with Row 1 as my header. How do I keep that row static? I don’t want to keep scrolling up/down to remember what goes in what cell. Select the row beneath the one you want as your header, go to Window:freeze panes ---
**Author:** ![OpalCat](https://avatars.discourse-cdn.com/v4/letter/o/8edcca/32.png) [@OpalCat](https://boards.straightdope.com/u/OpalCat)\ **Post date:** [August 10, 2005, 7:35pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/14 "2005-08-10T19:35:16Z")
> [@ultrafilter](#): > > In general, if you’re having trouble with Excel auto-interpreting your text as some sort of other data, just stick a ’ in front of it. '0258 will render as 0258 instead of 258. Would that mess up formulas using that cell’s contents, though? (using the ') ---
**Author:** ![OpalCat](https://avatars.discourse-cdn.com/v4/letter/o/8edcca/32.png) [@OpalCat](https://boards.straightdope.com/u/OpalCat)\ **Post date:** [August 10, 2005, 7:36pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/15 "2005-08-10T19:36:52Z")
(also, this is an official form that is printed and used in a log book and having ’ in there would look bad and I doubt they’d smile on that.) ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [August 10, 2005, 7:40pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/16 "2005-08-10T19:40:55Z")
> [@OpalCat](#): > > Would that mess up formulas using that cell’s contents, though? (using the ') Depens on the formula. Anything that’s treating the values as text should be fine, but anything that’s treating them as numbers may or may not have problems. If I have A1 = '0258 and A2 = '0569, A1 + A2 returns 827 but SUM(A1, A2) returns 0. Still, it’s a handy trick to know in some cases. ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [August 10, 2005, 7:41pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/17 "2005-08-10T19:41:39Z")
The apostrophe or the ’ will not appear on your prinouts nor should it appear in the cells of the workbook. Just as formulas will display totals as opposed to displaying formulas. ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [August 10, 2005, 7:42pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/18 "2005-08-10T19:42:43Z")
> [@OpalCat](#): > > (also, this is an official form that is printed and used in a log book and having ’ in there would look bad and I doubt they’d smile on that.) Don’t worry–it gets suppressed. Bonus homework: how do you get Excel to display ’ as the first character of a cell? ---
**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\ **Post date:** [August 10, 2005, 7:55pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/19 "2005-08-10T19:55:02Z")
> [@ultrafilter](#): > > Don’t worry–it gets suppressed. Bonus homework: how do you get Excel to display ’ as the first character of a cell? One way: if you want to add ’ to what’s in cell A1, then type in some other cell: ```auto =concatenate("'",A1) ``` ---
**Author:** ![Jake4](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@Jake4](https://boards.straightdope.com/u/Jake4)\ **Post date:** [August 10, 2005, 8:51pm UTC](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480/20 "2005-08-10T20:51:06Z")
> [@ultrafilter](#): > > Don’t worry–it gets suppressed. Bonus homework: how do you get Excel to display ’ as the first character of a cell? An easier way would be to type two apostrophes. [Next page](https://boards.straightdope.com/t/ms-excel-question-numbers-that-start-with-zero-such-as-0258/316480.md?page=2)