Thursday, September 9, 2010

Converting IP information in Excel

Over the past couple of months I've been compiling a report from a TCPDUMP that has been pulled on our old DNS servers to determine what internal IP addresses are still using the servers. I've been refining the report over time but have been frustrating myself with having to convert the IP addresses to a CSV to get rid of the port number of the source IP. Today I discovered a neat feature within Excel that helps me. Text to Columns
I am sure it is in earlier versions somewhere but it resides right on the ribbon in Excel 2007.
Now all I need to do is copy the column that I want to another spreadsheet and convert it to its own columns with the Text to Columns action.

That cut out a huge step for me, but now how do I consolidate those columns back into one cell with the IP address. It's actually pretty straightforward:
Add a new column to the beginning of the spreadsheet (new A)
Type the following in to A1 - =B1&". "&C1&"."&D1&"."&E1 and hit Enter
There is your IP
now copy and paste the formula down the line and it converts the remaining back
Copy those values back to the original spreadsheet and you are good.

It is also possible to do this via one spreadsheet and to hid the columns that you do not need, but that may come later.

Hope that helps someone else.

1 comment:

Etienne Liebetrau said...

Great post thanks for the tip!