Results 1 to 2 of 2
Thread: IP-addresses in Excel (2000)
2002-07-12, 08:16 #1
- Join Date
- Apr 2002
- Thanked 0 Times in 0 Posts
IP-addresses in Excel (2000)
IN Excel 2000 I administerd some IP-address-ranges. The addresses (for those not familiar) are all 12 digits, 4 * 3 with a dot between (for instance 184.108.40.206 or 10.1.23.1)
Now I need to split these addresses from 1 cel to 4 cells, along the dotted annotation.
So within 1 cel: 220.127.116.11 should become 4 cell's all containing one part (1st cell: 123; 2nd cell: 123 etc)
But this should also work when the IP-address is not 12 digits but only 9 (address: 18.104.22.168) or 6 (10.1.2.12) or 4 (10.1.1.1).
Anybody know how Excel can split these IP-addresses for me into four cells, or have a macro that searches on the dots and then copies and pastes the info into the next four cells?
best regards, Wilco
2002-07-12, 08:56 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: IP-addresses in Excel (2000)
Assuming you have put the IP addresses in one column:
Select the IP addresses.
From the menu, select Data/Text to columns.
The Wizard appears.
In the first step, make sure Delimited is selected.
In the second step, check the Other box and enter a dot in the text box next to it.
Note: if you live in a region where the period (dot) is the thousands separator, an entry like 22.214.171.124 will be interpreted as a number by Excel, unless you formatted the column as text (before entering the IP address), or prefixed the entry by an apostrophe. The Text to Columns wizard ignores numbers.