Excel is not…

a tragedy in 4 acts - or more.. I stopped counting

Overture

“no, we don’t have an IPAM, we can use Excel, it works fine!”

“are you sure?”

“we’ve always done it that way”

“ok…”

the digit-grouping-logic

After a long import session across the entire network.. I found out that some IP adresses were not represented with dots.. but with comma (,) – apparently something to do with the US/EU way of grouping digits/thousands. 

of course this caused the next system that wanted to work with this data to malfunction.. IP adresses are written in octets separated by dots.

Well, we don’t want that.. how to fix? Convert to Text.. then Excel can’t assume this is an number, right? 

Wrong! It’s too LATE – FORGET ABOUT YOUR DATA – it is corrupted now. – DO THE IMPORT AGAIN

ok.. so I probably need to change the settings of Windows AND Excel and change the default digit grouping symbol to a dot

(notice the UI is in Dutch? more about this later…)

small comfort: I can now use my NumPad for IP adresses.. at least!

but I should probably not use the 10. supernet – it’s only a /8 

(or avoid 1- and 2-digit IPs in this subnet.. ? )

the sorting logic

what do you mean? IP adresses are sorted by digit, not by numeric value? 

so 254 comes before 40 – sure…. WTF?

how can we fix this? 

oh.. we can split the octets apart and then sort the four cells separately

So.. select range > text to columns > fixed > by character > Other [ . ]

And now sort by first group.. then by second.. third and fourth.. 

nah.. there has to be a better way.. 

ahyes.. there is.. just insert leading zeroes for all IPs – so they can be sorted – extract the IP – check characters and insert zeroes as needed.

=TEXT(MID(A1,1,FIND(".",A1)),"000")&"."&TEXT(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-1-FIND(".",A1)),"000")&"."&TEXT(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-1-FIND(".",A1,FIND(".",A1)+1)),"000")&"."&TEXT(MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,LEN(A1)),"000")

of course this has to be done for every cell…

the language barrier

but wait.. I work for a NL company – my office is in Dutch… AND SO IS THE SCRIPTING ENGINE

so “TEXT” becomes “TEKST” and “MID” becomes “DEEL” and .. seriously? 

so also my vertical lookup is now: ZOEK.VERTICAAL

and after a while of search&replace I realized.. I now have IP Adresses with leading zeroes.. I Can’t paste those back into a switch. 

anyway.. I’ll just do this by hand. 

And I will also avoid sorting by IP. Not a handicap.. not at all…

the mac address confusion

Now about the MAC adresses.. let’s look them up by vendor OUI (first 24 bytes)

(similar to IP adresses.. but grouped by colons and written in hexadecimal pairs)

Hm… my search/lookup doesn’t work.. why?

oh. it seems this time Excel interprets this as TIME

sure.. 

let’s convert it to text so I can do search

and the data is gone. import from backup, start over. 

okay,,, so we can’t do this either. 

the internet enhancement

but what is that? the MACs have become hyperlinks???

so i’ll just do this step manually, too.. 

copy-paste-fail

now.. I want to copy all values from the cells that I selected with a filter and paste them in another column where I want to process them with a lookup.. oh.. that won’t work, too.. because excel pastes in the cells that are hidden by the filter… even if I select “visible only”

in a list of thousands of entries.. how can I guarantee accurate data after filtering? 

resignation

I give up. Why do I even try???

but no.. the boss said we don’t need IPAM – so at my current hourly I can calculate the damage I am doing to the company doing all this by hand and double-triple checking the data. 

finale

the internet knows, of course.. this is what your colleagues send you afterwards

Data Artisan on Twitter: "Always remember: excel is not a database!  #DataScience #100DaysOfCode #100DaysOfMLCode #AI #MachineLearning #RStats… "