Excel IP addresses

[RefriedNoodle]

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

 

[Bill Martin]

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

---------------------------------------


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

 

[RefriedNoodle]

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...

 

[Bill Martin]

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
------------------------------

 

[Bill Martin]

Doing a quick Google search comes up with this link containing the following text:
http://www.officehelp.in/showthread.php?t=141669

You could try:
<Tools> <Options> <ErrorChecking> tab,
Make sure that "NumberStoredAsText" is *unchecked*.

This is apparently a new feature with Excel 2002.

Bill

-----------------------------

 

[JC]

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.

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.