Link to home
Start Free TrialLog in
Avatar of billyboy71
billyboy71

asked on

parsing data in Excel

Hi,
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  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?

ASKER CERTIFIED SOLUTION
Avatar of baboo_
baboo_
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hqa666
hqa666
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

 
Avatar of computerg33k
computerg33k
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