Modifying strings of text

mawilson12

New Member
Joined
Jan 17, 2008
Messages
24
As you may know, IP addresses are in A.B.C.D format.
I would like to take A.B.C.0 (Last digit "zero") and generate a list of A.B.C.252, A.B.C.253, .254, .255.

Alternatively, I would like to take
A.B.C.0 and just generate A.B.C.252 and A.B.C.255 in two different columns.

I don't expect someone to solve the problem for me, I would like someone to point me in the right direction.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
Mawilson,

To get a list from 0 to 255:
1. Type 192.168.1.0 in A1 (or any cell)
2. Type 192.168.1.1 in A2 (the cell below the first cell)
3. Select both cells
4. Click (and hold) the lower right corner of the second cell and drag your mouse downward (this is called the Fill Handle). Drag it down to row 256 and you'll then have a list of 192.168.1.0 to 192.168.1.255

As for your second request, with columns, you can do the same thing, but drag to the right (e.g. use A1, B1, then drag right).

If that's not what you meant for the second question, let us know.
 

mawilson12

New Member
Joined
Jan 17, 2008
Messages
24
Thanks. I wasn't clear. I didn't want all the numbers in a subnet, just the last ones. So I wrote:

=SUBSTITUTE(A2,".0",".252",1)

Where A2= A.B.C.0, so I get A.B.C.252
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top