When gone through a lot of raw data like log data, you want to structure it into something more useful. I’m going to share all that I have learned. In this post I’m going to record all the command’s I use to manipulate data within Excel, I will try to update this post when I use a new command.

Feedback or useful commands are more than welcome, they can be placed below.

 

Update Tracking

2014-01-23 Initial setup
2015-04-22 Small touch up

IP addresses in excel:
When I past or type IP addresses into Excel 2003/2010, they seem to convert to numbers. This tends to happen even when the cell is configured as text, see figure 1: IP as number

Excel-ip-number-01

Figure 1: IP as number

This might give some problems when you want to match it to some thing else, you need to convert the cells to the same format some how.To convert the number to text I use the following formula

=IF(ISNUMBER(A1);TEXT(A1;"0.000.000.000");A1)

When you convert the IP that is a number with this formula you are able to copy and past as value to another cell. The formula converts the number notation to a “0.000.000.000” format, see figure 2: IP as Text as example

Excel-ip-number-01
figure 2: IP as Text

Leave a Reply

Your email address will not be published. Required fields are marked *