Solved

parsing data in Excel

Posted on 2004-11-18
Medium Priority
222 Views
Hi,
I have a firewall log that shows the source IP address with a port number in format like the following:

The length of the digits in the IP address can be from  192.168.0.1 to 192.168.0.254

The port can be anyway from 3 to 4 digits long.

I view the log in my Excel spreadsheet with one column the data.  I want to be able to take out the ":port number" and just show the IP address .

Is this possible?

0
Question by:billyboy71

LVL 3

Accepted Solution

baboo_ earned 1000 total points
ID: 12617063
If your data is in column A, use this formula

=LEFT(A1,SEARCH(":",A1,1)-1)

Copy and paste it into cell B1.
Press Enter so that the formula evaluates.
Right-click on cell B1, and select copy.
Select cells B2 through B(n), where n is the number of entries you have in column A.
Right click on the selected cells (B2 through B?)
Then select paste.

This should strip the port number and the colon.

baboo_
0

Expert Comment

ID: 12620584
theres also a standard way of doing it in excel:
select the column u want to edit
in the menu data, select text to columns, select seperated,next
as seperator sign fill : in the box

0

LVL 4

Expert Comment

ID: 12630723
Dim a As String = IPAddress
Dim b As Integer
Dim IP, PortNum As String
b = a.IndexOf(":")
IP= (a.Substring(0, b))
PortNum = (a.Substring(b))
PortNumber = PortNum

in vb.net this searches the string for ":" and seperates the port number from the ip address--then u can delete the first character on portnumber (the colon) and insert one into column a and one into column b and so on
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Progress
Suggested Courses
Course of the Month16 days, 13 hours left to enroll