Back to Forms in Excel VBA archive index

Back to archive home

how can i add a number to an IP address? for example, say i want to add 13 to the following IP address:

172.16.20.14

to get:

172.16.20.27

i can handle using the "text to columns" function to break it up, but then how do you put it back together again? is there a similar "columns to text" approach to rebuilding my original cell?

i'm sure there's a simple answer. thanks!

-BL-

Check out our Excel Resources | ||||

assumes original number is in A2

=LEFT(A2,LEN(A2)-2)&(RIGHT(A2,2)+13)

an interesting approach, but it doesn't work when the last set is not exactly two digits long. for example:

172.16.0.2

165.234.5.112

any other ideas?

You can put columns back together using concatenate

For example

=A1&"."&B1&"."&C1&"."&D1

I think this is the way to go. I came up with the ugliest formula in the world but it will work if you are adding 1 to and the fourth set of numbers is between 1 and 3 digits.

=LEFT(SUBSTITUTE(A1,".","z",3),FIND("z",SUBSTITUTE(A1,".","z",3))-1)&"."&1+IF(LEN(RIGHT(A1,LEN(SUBSTITUTE(A1,".","z",3))-FIND("z",SUBSTITUTE(A1,".","z",3))))=3,RIGHT(A1,LEN(SUBSTITUTE(A1,".","z",3))-FIND("z",SUBSTITUTE(A1,".","z",3)))+0,IF(LEN(RIGHT(A1,LEN(SUBSTITUTE(A1,".","z",3))-FIND("z",SUBSTITUTE(A1,".","z",3))))=2,RIGHT(A1,LEN(SUBSTITUTE(A1,".","z",3))-FIND("z",SUBSTITUTE(A1,".","z",3)))*10,RIGHT(A1,LEN(SUBSTITUTE(A1,".","z",3))-FIND("z",SUBSTITUTE(A1,".","z",3)))*100))

Using this adding 1 to 172.16.0.2 would be 172.16.0.201. Not sure if that is right

=LEFT(A1,SEARCH("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))&RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))+$G$1

where A1 houses the IP address and G1 the number to add.

Aladin

Much less ugly. I don't understand IP addresses. I went into the whole loop because I was thinking

100.400.200.1 plus one should equal 100.400.200.101 but wasn't sure. I was going to give you credit for the substitute function I stole from an early post of yours, but since it was so ugly in my use I changed the @ to a z to protect you from this horrific use of it.

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.