Excel IP addresses


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/community/en-us/default.mspx?mid=c6af0317-7030-4414-b107-7b4765ea40cc&dg=microsoft.public.excel.misc
 
Ad

Advertisements

B

Bill Martin

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/community/en-us/default.mspx?mid=c6af0317-7030-4414-b107-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
 
G

Guest

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

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

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

-----------------------------
 
Ad

Advertisements

J

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top