MrExcel.com - Your One Stop for Excel Tips & Solutions

columns to text?

Posted by BigLou on October 05, 2001 12:15 PM

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-

Posted by faster on October 05, 2001 12:21 PM

assumes original number is in A2

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


Posted by BigLou on October 05, 2001 12:46 PM

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?


Posted by IML on October 05, 2001 2:04 PM

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

Posted by Aladin Akyurek on October 05, 2001 2:35 PM

Ian: Hope this one is less uglier...


=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

Posted by IML on October 08, 2001 6:27 AM

Yes

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.