Excel fields as IP addresses

[reeceb]

Greetings (from a 'newbie').

I am developing a workbook to remove the chances of less qualified IT staff mucking up a Cisco router config and am trying (as much as possible) to automate the process.

I have a requirement to take a value in a field and add a single digit to the last number. The length of the field is variable, the only constant being 3 x "." in the field.

Possible options are...

<TABLE style="WIDTH: 131pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=174><COLGROUP><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6200" width=174><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 131pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 width=174>10.1.1.1</TD></TR>

<TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_843919 height=19>10.1.1.10</TD></TR>

<TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>10.1.1.100</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>10.10.1.1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>10.10.10.1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>10.10.10.10</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>10.10.10.100</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>10.100.1.1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>10.100.10.1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>10.100.100.1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>10.100.100.10</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>10.100.100.100</TD></TR></TBODY></TABLE>

In each of the above I want to change the last No. to be +1 (ie. 10.1.1.2, 10.1.1.11, etc.)

Any assistance would be greatly appreciated.

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">10.1.1.1</td><td style=";">10.1.1.2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">10.1.1.10</td><td style=";">10.1.1.11</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">10.1.1.100</td><td style=";">10.1.1.101</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">10.10.1.1</td><td style=";">10.10.1.2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">10.10.10.1</td><td style=";">10.10.10.2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">10.10.10.10</td><td style=";">10.10.10.11</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">10.10.10.100</td><td style=";">10.10.10.101</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">10.100.1.1</td><td style=";">10.100.1.2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">10.100.10.1</td><td style=";">10.100.10.2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">10.100.100.1</td><td style=";">10.100.100.2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">10.100.100.10</td><td style=";">10.100.100.11</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">10.100.100.100</td><td style=";">10.100.100.101</td></tr></tbody></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,FIND(<font color="Red">"^",SUBSTITUTE(<font color="Green">A1,".","^",3</font>)</font>)</font>)&MID(<font color="Blue">A1,FIND(<font color="Red">"^",SUBSTITUTE(<font color="Green">A1,".","^",3</font>)</font>)+1,9</font>)+1</td></tr></tbody></table></td></tr></table><br />

 

[Robert Mika]

or:
=LEFT(A1,LEN(A1)-1)&RIGHT(A1,1)+1

 

[AlphaFrog]

Good1 Robert Mika.
Boy did I have tunnel vision on that one.

 

[Scott Huish]

Here's another one:

=REPLACE(A1,LEN(A1),1,RIGHT(A1)+1)

 

[pgc01]

Hi

I tried with

1.1.1.19

and only AlphaFrog's formula worked.

reeceb
Welcome to the board

Can the case of the last number equal to 255 ever happen? If yes, what is the expected result?

 

[Scott Huish]

=replace(a1,find("~",substitute(a1,".","~",3))+1,3,right(substitute(a1,"."," ",3),3)+1)

 

[reeceb]

Thanks heaps guys. AlphaFrog's is the only one I have tried so far and it works a treat, but will give the others a go to to expand my knowledge.

The occurence of x.x.x.255 may come up, however this is intended as a configuration tool not an address repository, so the person using it should already have got the address from our IP address repository.

Apologies for the late response. Tried several times last night and the forum was unavailable.

Thank goodness for people who are far cleverer than I am, and they take the time to assist. Greatly appreciated.