Possible Replace or Substitution issue

[tronchetti]

I need to take the data in column A and have it fill in the data in columns B-H as shown below. As you can see, the number after the second "." is increasing by 1 starting at column B and "0" is replaced by "1" after the third "." Any suggestions would be great!
Thank You...

A B C D E F G H 10.144.0.0/20 10.144.0.1 10.144.1.1 10.144.2.1 10.144.3.1 10.144.4.1 10.144.5.1 10.144.6.1 10.144.16.0/20 10.144.16.1 10.144.17.1 10.144.18.1 10.144.19.1 10.144.20.1 10.144.21.1 10.144.22.1 10.144.32.0/20 10.144.32.1 10.144.33.1 10.144.34.1 10.144.35.1 10.144.36.1 10.144.37.1 10.144.38.1 10.144.48.0/20 10.144.48.1 10.144.49.1 10.144.50.1 10.144.51.1 10.144.52.1 10.144.53.1 10.144.54.1 10.144.64.0/20 10.144.64.1 10.144.65.1 10.144.66.1 10.144.67.1 10.144.68.1 10.144.69.1 10.144.70.1 10.144.80.0/20 10.144.80.1 10.144.81.1 10.144.82.1 10.144.83.1 10.144.84.1 10.144.85.1 10.144.86.1 <tbody> </tbody>

<tbody>
</tbody>

 

[Special-K99]

I notice that each row the initial digit is (row number-1)*16
Row 1 is 10.144. 0
Row 2 is 10.144. 16
Row 3 is 10.144. 32
Row 4 is 10.144. 48


If this continues down the column then this will work

in B1
="10.144."&(ROW()-1)*16+COLUMN()-2&".1"
copy across and down

 

[MarkReddell]

Try this, It worked 4 me: =IF(LEN($A1)=13,LEFT($A1,7)&MID($A1,8,1)+(COLUMNS($B1:B1)-1)&".1",LEFT($A1,7)&MID($A1,8,2)+(COLUMNS($B1:B1)-1)&".1") Let me know If it worked 4 "U"!!!

 

[MarkReddell]

This would be better: =IF(LEN($A1)=13,LEFT($A1,7)&MID($A1,8,1)+(COLUMNS($B1:B1)-1),LEFT($A1,7)&MID($A1,8,2)+(COLUMNS($B1:B1)-1))&".1"

 

[tronchetti]

Hi Mark,
This works great except when the length in A1 is 15 or more. I missed these longer strings when I posted my original table, sorry...

A	B	C	D	E	F	G	H
10.144.0.0/20	10.144.0.1	10.144.1.1	10.144.2.1	10.144.3.1	10.144.4.1	10.144.5.1	10.144.6.1
10.144.16.0/20	10.144.16.1	10.144.17.1	10.144.18.1	10.144.19.1	10.144.20.1	10.144.21.1	10.144.22.1
10.144.32.0/20	10.144.32.1	10.144.33.1	10.144.34.1	10.144.35.1	10.144.36.1	10.144.37.1	10.144.38.1
10.144.112.0/20	10.144.11.1	10.144.12.1	10.144.13.1	10.144.14.1	10.144.15.1	10.144.16.1	10.144.17.1
172.144.128.0/20	172.1440.1.1	172.1441.1.1	172.1442.1.1	172.1443.1.1	172.1444.1.1	172.1445.1.1	172.1446.1.1
192.168.128.0/20	192.1680.1.1	192.1681.1.1	192.1682.1.1	192.1683.1.1	192.1684.1.1	192.1685.1.1	192.1686.1.1

<tbody>
</tbody>