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.

 

[lrobbo314]

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

 

[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

when I do the formula all I get back is #NAME?

 

[marksman681]

does anyone else have any ideas?

 

[johnnyL]

@lrobbo314's code in post #2 works fine for me.

Perhaps you need to to change the 'A1' in the example to 'C1'? =OCTET(C1)

 

[marksman681]

@lrobbo314's code in post #2 works fine for me.

Perhaps you need to to change the 'A1' in the example to 'C1'? =OCTET(C1)

I did that and it does the same thing.

 

[Fluff]

Where did you put the code?
Is it in a normal module in that workbook?

 

[marksman681]

Where did you put the code?
Is it in a normal module in that workbook?

to be honest I have no idea where to put the code. I used the formula and it is not work. I tried it on a windows machine and a mac. neither worked. I know they older versions and when typing in the formula that one does not pop up.

 

[Fluff]

In the VB editor click "Insert" then "Module" & paste the code in there. You will need to save the workbook as macro enabled.

 

[marksman681]

In the VB editor click "Insert" then "Module" & paste the code in there. You will need to save the workbook as macro enabled.

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.

In the VB editor click "Insert" then "Module" & paste the code in there. You will need to save the workbook as macro enabled.

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