parsing data in Excel

Posted on 2004-11-18
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
    LVL 3

    Accepted Solution

    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

    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

    LVL 4

    Expert Comment

    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 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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
    If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now