Thanks to Discovery Channel, we have a scientific answer to this question. A group of people there actually measured the amount of energy used by several different types of light bulbs during start up. In all cases, they found that light bulbs do have a spike of energy use during start up; it's significantly higher than the energy used during normal use. The spike did not last long, however.
For the standard incandescent light bulb, it would take less than one second of use to make up for the spike of energy when the bulb is first started. In other words, pretty much every time you leave the room and no longer need the light, it will save you energy to turn the light off.
The bulb that used the most energy during the start up phase was standard fluorescent bulbs, like those you see in offices and shop lights. Even with these, however, as long as the light won't be turned back on within 23 seconds, it's still going to save you energy to shut the light off.
Now, to be fair, I have to note that turning bulbs on and off more often can shorten the life of the bulb itself. The money saved in energy, however, will almost always make up for any increase in bulbs. It's really not even close. If you want to save money or if you want to use less energy for the good of the environment (or both!), turn off the lights when you leave the room.