MrExcel Publishing
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:

to get:

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!


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

assumes original number is in A2


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:

any other ideas?

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

You can put columns back together using concatenate
For example

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.


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

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

Ian: Hope this one is less uglier...


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


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


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.