MS Excel question: numbers that start with ZERO (such as 0258)

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.)

Highlight the cells that you want to enter data into and choose Format - Cells - Text.

Sorry. That was a simplistic answer and incorrect. I will be back in a minute.

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).

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.

Magic Eyes beat me by moments, but has given the correct answer.

Curses! Beat me to it. I guess “0000” will work just as well as “000#”

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.

Hooray!!! Thanks everyone for your help!

puts ointment on forehead where repeated banging against desk didn’t seem to help much

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.

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.

Highlight the row, click window, then click freeze panes.

Select the row beneath the one you want as your header, go to Window:freeze panes

Would that mess up formulas using that cell’s contents, though? (using the ')

(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.)

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.

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.

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:


=concatenate("'",A1)

An easier way would be to type two apostrophes.