Automatically modify cell data

[DJSTYLi]

Hi,

I want to create an XL spreadsheet where I can analyse data from m
firewall log.

The main concern is to identify IP addresses that have accessed ou
firewall a large number of times. This should be simple enough in X
using the data filter tool, but it gets a little more complicated.

I need to be able to copy the firewall logs sent by email to me an
paste them into a spreadsheet. The emails are formatted in a way tha
allows the different columns of the log to be automatically put int
different columns in the XL spreadsheet, which is obviously ver
handy.

My only problem is, the cell I want to analyse contains more than on
item of data. that is, it shows IP address and port where I just wan
it to show IP address. I could go through each one and move the por
data to anohter column in each row, but I am dealing with a vast amout
of data (7000 columns for 3 weeks of logging) so cannot be an option.

So my question is, would I be able to record a macro that change
this:

Current contents of cell
Source:12.124.89.220, 2571, WAN -


to this:


Required contents of cell:
12.124.89.220

for a massive list of rows

 

[Bob]

You won't need to.

1. Select the range of cells that contain the IP
addresses and ports.
2. From the DATA menu, choose TEXT TO COLUMNS.
3. Choose DELIMITED and click on the NEXT button.
4. Choose COMMA and click on the NEXT button (or FINISH).
5. Click on the FINISH button.

Now you will have the IP addresses in one column and the
ports in the next column. Then use the SUBTOTALS feature
(also in the DATA menu) to see how many times a given IP
address hit your firewall and the ports they tried to hit.