OpenOffice trick to auto-increment IP addresses

I’ve been fleshing out my IP address spreadsheets, and I’ve been using a trick that I didn’t know about for a long time, till someone on a forum showed me. It was indispensable.

You know how, if you want a series of numbers down a row, you can click the lower right hand corner, and drag it down, and it autoincrements the numbers for you? Well, in oocalc, if you try that with IP addresses, it just copies the original IP address. I don’t know why, but it does. MS Office gets it right, heck I think even K Office will do it. OpenOffice, not so much.

The trick to doing it is to put a space in front of the IP address, first.
”″ will allow you to drag it down as far as you want, incrementing the last octet.

  • Robert Sander

    Unfortunately OOo only increments the last “byte” with your trick and will happily go over 255 when expanding.

  • Matt


    Yes, that’s true. I figured I was willing to put up with changing the octet every 256 times, rather than typing every number.

    What I can’t figure out is how they didn’t try it and say “oops, you know, someone someday may want to make an IP list with this”. It’s not like it’s uncommon. At least, I don’t think it is. I’ve got a skewed perspective on some things though ;-)

  • Robert Sander

    I just wrote a simple OOCalc Basic function the converts a numeric value into a dotted IP address.

    This can be used for the correct generation of IP addresses. You just have to increment a numeric column and in the second column long2ip() generates the ip addresses.

    Have a look at

  • Lloyd Collins

    I did this recently with office, but I found it quicker to write a quick VB for Office script to post them incrementally, and then just modify the scripts each time the last two octets completed.