IP and MAC lists

  • Thread starter Thread starter Wayne Chandler
  • Start date Start date
W

Wayne Chandler

I have an spreadsheet with several IP addresses and associated MAC
addresses. How do I format the cells to accept only that type of
input?
For example, I type in 10123234016 and it shows in the cell as
10.123.234.16.
For MACs, I input 0001b2d380c1, and it shows as 00-01-B2-D3-80-C1.
One would think this would be a simple template or solution to find,
but no luck so far.
 
Hi Wayne
For example, I type in 10123234016 and it shows in the cell as
10.123.234.16.
Click to expand...

How would the old lady know it itn't supposed to be
101.232.340.16
?

Best wishes Harald
 
This may not be what you want, but it might make life easier (sorting!) later:

Use a helper cell that contains this formula:
=TEXT(A1,"000\.000\.000\.000")

10123234016
showed as:
010.123.234.016

And for the MAC address, I'd use a formula like:
=MID(A1,1,2)&"-"&MID(A1,3,2)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2)
&"-"&MID(A1,9,2)&"-"&MID(A1,11,2)
(all one cell)

0001b2d380c2
showed as:
00-01-b2-d3-80-c2


In fact, I'd leave my original data in the worksheet--maybe hide those columns
to make it look prettier, though.
 
Hi! "Wayne Chandler <" ,
If you want to only display (not value) , then
you can apply format | cell for one of your matters:

Try this:
Select cell which contains: 10123234016
Format Menu | Cell | Category Box : Custom , Type Box: 000-000-000-000
Show as: 010-123-234-016
OR
Format Menu | Cell | Category Box : Custom , Type Box: 000"."000"."000"."000
Show as: 010.123.234.016

Good Luck!
 
Great! Thanks very much for the suggestions. Just what I needed t
solve the problem
 
Back
Top