parsing data in Excel

Posted on 2004-11-18
Medium Priority
Last Modified: 2010-04-17
I have a firewall log that shows the source IP address with a port number in format like the following:

<ip address:port number>

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

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?

Question by:billyboy71

Accepted Solution

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


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.


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


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))
            IPAddress = IP
            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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question