parsing data in Excel

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.