Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel IP addresses
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/comm...lic.excel.misc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel IP addresses
RefriedNoodle wrote:
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/comm...lic.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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel IP addresses
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" wrote: RefriedNoodle wrote: 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/comm...lic.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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel IP addresses
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 ------------------------------ RefriedNoodle wrote: 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" wrote: RefriedNoodle wrote: 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/comm...lic.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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel IP addresses
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 ----------------------------- RefriedNoodle wrote: 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" wrote: RefriedNoodle wrote: 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/comm...lic.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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel IP addresses
On Wed, 11 Jan 2006 05:03:02 -0800, "RefriedNoodle"
wrote: 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. -- Cheers . . . JC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
How can I e-mail multiple persons whose addresses are in excel? | Excel Discussion (Misc queries) | |||
Excel 2003 addresses 1GB memory but only uses 200MB | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) |