Pulling IP Addresses out of log files.

[marksman681]

Hello I am having an issue and need some help. I have some SSH log files in excel. Column C is where the data is. There is a lot of text wrapped around the IP address and I want to be able to pull it out into a different column. The text that is around the IP address is random in length. I do not need anything that is missing an octet but if it grabs it I do not care.

 

[marksman681]

ok i must be really messing something up. I did what you said and it is all there but now I need to know how to run the macro.

ok still not working and I am not sure what I am doing wrong

also was saved as micro enabled

 

[Fluff]

I need to know how to run the macro

You don't call the macro, the formula does that for you.

 

[marksman681]

You don't call the macro, the formula does that for you.

ok well, I am not understanding what I need to do. is there a video or something that will show step-by-step instructions? I am obviously screwing something up

 

[Fluff]

You just use the formula as shown in post#2

 

[marksman681]

I did that and it is not working. it went from NAME to now VALUE.

 

[marksman681]

here is what I put in and here is what I am getting when I put in the formula. when I put in the formula the OCTET shows up but it is not calculating it properly. I even tried it on a line that had a full IP and it did the same thing.

 

[marksman681]

One way.

Book1 (version 2).xlsb
	A	B
1	ABC 1.1.1.1 QRS	1.1.1.1
2	DEF 255.255.255.255 TUV	255.255.255.255
3	GIH 1.28.255.68 XYZ	1.28.255.68
Sheet3

Cell Formulas
Range	Formula
B1:B3	B1	=OCTET(A1)

Function OCTET(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "(?:\d+.?)+"
    OCTET = .Execute(s)(0)
End With
End Function

ok, I have tried this and it is not working. It went from #name? to now #Value? any ideas on what I need to do to fix this? I have 65000 lines that I do not want to go through manual

 

[Fluff]

You are getting that error as you are using a Mac.

 

[marksman681]

well they have the Macros shut off on my work computer so I had to switch to my mac. is there a different formula i need to use for a MAC

 

[Fluff]

As RegEx does not work on a Mac, then you will need a formula if you cannot use macros.