Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

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!


Check out our Excel Resources

Re: columns to text?

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

assumes original number is in A2


Re: columns to text?

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?

Re: columns to text?

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

Ian: Hope this one is less uglier...

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


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.

This archive is from the original message board at
All contents © 1998-2004
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.