VBA: stripping text from PING output

  • Hi again.


    Could anybody possibly point me in the right direction for stripping unwanted text strings from the following output:


    Reply[1] from 212.23.2.197: bytes=32 time=16 ms TTL=117
    Reply[1] from 212.23.2.197: bytes=32 time=31 ms TTL=117
    Reply[1] from 212.23.2.197: bytes=32 time=31 ms TTL=117
    Reply[1] from 62.173.67.99: bytes=32 time=31 ms TTL=50
    Host not found: http://www.zenoc.net error 11001
    Host not found: http://www.zenoc.net error 11001
    Host not found: http://www.zenoc.net error 11001
    Host not found: http://www.zenoc.net error 11001
    Host not found: http://www.zenoc.net error 11001
    66.246.48.216: request timed out
    66.246.48.216: request timed out
    66.246.48.216: request timed out
    203.98.189.18: request timed out
    203.98.189.18: request timed out
    Reply[1] from 203.98.189.18: bytes=32 time=687 ms TTL=46


    In an ideal world, I'd like to be able to do the following:
    Create a few new variables (columns) which include:


    --A binary variable for ping response. i.e. 1 if ping reply, 0 otherwise.


    --IP address


    --time


    --TTL


    Additionally, the pings are carried out many times for the same URL, as there are duplicates in the list. Is it possible to, say, take an average of the pings for each URL, and report the same one average PING for all identical URLs?


    I realise that this is a big ask, as some of these things are probably not possible. The alternative is I go back to the old find and replace function, and autofilter...


    Thanks in advance,


    Mike


    File Format:


    URL -- PING_Output
    URL1 ping1
    URL2 ping2
    URL2 ping2 (different result)
    URL3 ping3

  • Hi MisterT,


    This might help get you started.


    I have used formula to strip out the 3 bits of info. The ping 0/1 binary status simple checks whether there is information extracted.


    Take a look and post back if I missed the mark or you still have problems.


    Cheers
    Andy

  • Hi Andy,


    Many thanks - I am enormously grateful. That is a very neat work around.


    I was attempting the same thing in VBA and not getting very far...


    You have also generated meaningful binaires for ping response, as I have just realised that "request timed out" responses can be eliminated by increasing the timeout settings, so will be taking an average over several long pings.


    BTW - I used Fping to ping multiple hosts, which is availabe at http://www.kwakkelflap.com/fping.html and 21,000 domains took under three hours.


    --Thanks again,


    Mike:wink2:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!