# Possible Replace or Substitution issue

• Jul 11th, 2013, 07:35 PM
tronchetti
Possible Replace or Substitution issue
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
• Jul 11th, 2013, 08:15 PM
Special-K99
Re: Possible Replace or Substitution issue
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
• Jul 11th, 2013, 09:52 PM
MarkReddell
Re: Possible Replace or Substitution issue
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"!!!
• Jul 11th, 2013, 10:07 PM
MarkReddell
Re: Possible Replace or Substitution issue
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"
• Jul 12th, 2013, 01:05 PM
tronchetti
Re: Possible Replace or Substitution issue
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