Excel IP addresses

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

As a MCSE and system administrator, I often use Excel to make lists of
networks and IP-addresses. However, Excel doesn't support IP addresses as
data.

When I enter an IP address with each octet containing 3 digits, Excel
interprets it as a number (because in Europe, we use the dot to separate
numbers, not the comma). When I convert the field to text using the cell
properties, I get something like 1.92168E+11. When I delete this and enter
the address in a text cell, I get a little green corner, which tells me that
I entered a number in a text field.

Wouldn't it be great if I could just select "IP Address" as a data type in
the cell properties? Perhaps it could even align the 4 octets, so that octets
of 1, 2 or 3 digits appear nicely in columns? Or use diffenent notations for
addresses, like slashed subnet notation (192.168.1.0/24)?

I'm sure it would make Excel a better product for network administrators.

Thanks for your time.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...7-7b4765ea40cc&dg=microsoft.public.excel.misc
 
RefriedNoodle said:
As a MCSE and system administrator, I often use Excel to make lists of
networks and IP-addresses. However, Excel doesn't support IP addresses as
data.

When I enter an IP address with each octet containing 3 digits, Excel
interprets it as a number (because in Europe, we use the dot to separate
numbers, not the comma). When I convert the field to text using the cell
properties, I get something like 1.92168E+11. When I delete this and enter
the address in a text cell, I get a little green corner, which tells me that
I entered a number in a text field.

Wouldn't it be great if I could just select "IP Address" as a data type in
the cell properties? Perhaps it could even align the 4 octets, so that octets
of 1, 2 or 3 digits appear nicely in columns? Or use diffenent notations for
addresses, like slashed subnet notation (192.168.1.0/24)?

I'm sure it would make Excel a better product for network administrators.

Thanks for your time.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...7-7b4765ea40cc&dg=microsoft.public.excel.misc
Click to expand...
---------------------------------------


Have you tried entering it as '192.168.2.1 ? Note the apostrophe preceeding
the first digit. That tells Excel to throw away the apostrophe and treat what
follows as text.

Bill
 
Bill,
Thanks for the tip, it saves a few mouseclicks. However, all that the
apostrophe does is set the type of the cell to text. I can enter an IP
address, however I still get that little green corner with the tip that i
just tried to enter a number in a text field...
 
The little green corner I don't recognize. Either it's an Excel "improvement"
made since the Excel97 version that I use, or somewhere I've got it turned off.

Typically I turn off everything that lets the system natter at me, popups,
floaty help balloons, et al. Permanently driving a stake through the heart of
the dancing paper clip was a major PITA as I recall.

Good luck with it.

Bill
------------------------------
 
As a MCSE and system administrator, I often use Excel to make lists of
networks and IP-addresses. However, Excel doesn't support IP addresses as
data.

When I enter an IP address with each octet containing 3 digits, Excel
interprets it as a number (because in Europe, we use the dot to separate
numbers, not the comma). When I convert the field to text using the cell
properties, I get something like 1.92168E+11. When I delete this and enter
the address in a text cell, I get a little green corner, which tells me that
I entered a number in a text field.

Wouldn't it be great if I could just select "IP Address" as a data type in
the cell properties? Perhaps it could even align the 4 octets, so that octets
of 1, 2 or 3 digits appear nicely in columns? Or use diffenent notations for
addresses, like slashed subnet notation (192.168.1.0/24)?

I'm sure it would make Excel a better product for network administrators.

Thanks for your time.
Click to expand...

Have you tried formatting the cells as General?

I have a spreadsheet that I use to check the firewall log and that contains
thousands of IP addresses. They are all formatted as General and don't have
the small green corner flag that you mentioned.
 
Back
Top