Newbie excel question (most likely)

M

Merlock

Veteran X
I want to make a simple database of IPs in a class-B subnet.

192.168.x.y

I want to keep these values in a column, and basically list every IP in that subnet, so...

192.168.1.1
192.168.1.2
.
.
.
192.168.255.255

Is there any formula or other way to get excel to produce this data for me? I would rather not have to enter in 255^2 values in by hand :lol:

Yes, I know I suck at excel :)
 
If the values were in standard numerical format you could just add 1 to each cell based on the value in the cell above it. That would still leave you manually creating a formula in each cell though, which may turn out to be more work than just typing the values.

This is less of a noob question than you think, and I'm interested in the answer as much as you are...
 
Lucky for you both, Excel is smart

Start the list

192.168.0.1
192.168.0.2
192.168.0.3

Highlight all 3 boxes, then, click the little black box in the lower right hand corner and drag down...it will increment them automatically.

Tested this in Excel 2000
 
Xaphan said:
Lucky for you both, Excel is smart

Start the list

192.168.0.1
192.168.0.2
192.168.0.3

Highlight all 3 boxes, then, click the little black box in the lower right hand corner and drag down...it will increment them automatically.

Tested this in Excel 2000
Click to expand...

You sir, are the man.
:bandit:
 
well, even though you have your solution....


i couldn't do it with one column, but i did it with 4

data is as follows for the columns:

192
B: 168
C: =QUOTIENT(ROW(),257)
D: =ROW()-($C1*256)-1

Enter that in for one row, copy and paste it into all the rows. Produces range from from 192.168.0.0 to 192.168.255.255 although Xaphan's method works better I suppose.
 
Xaphan said:
Lucky for you both, Excel is smart

Start the list

192.168.0.1
192.168.0.2
192.168.0.3

Highlight all 3 boxes, then, click the little black box in the lower right hand corner and drag down...it will increment them automatically.

Tested this in Excel 2000
Click to expand...

You sir, deserve a :bigthumb:

Muchas Gracias.
 
If you knew VBA, you could have done this using that, too...probably have to "split()" it and then increment and then put it back together again...but its possible...
 
You must log in or register to reply here.
Back
Top