formula help

[vf_guy]

Hello,

I'm trying to come up with a formula that extracts the following:
(bold is info to be extracted. these are all the possible combos that it can appear.
A "name" is just text. "IP" is a IP address).

so an example of the first line would be:

access-list 95 extended permit tcp object-group File-Hosts host 1.1.1.1 any

an example of the second type would be:

access-list 95 extended permit tcp object-group Harrys-Group host 1.1.1.1 eq 4100

etc, etc, etc.

I don't know if it makes a difference, but the examples below are returned via a vlookup. Also, some lines might be blank.

I've figured out how to return the first and second value if it's an "IP"

=IFERROR(MID(B3,SEARCH("host",B3)+5,SEARCH(" ",B3,SEARCH("host",B3)+5)-(SEARCH("host",B3)+5)),"")

and the second value if it's an IP as well

=IFERROR(MID(B5, SEARCH("xx",SUBSTITUTE(B5,"host ","xx",(LEN(B5)-LEN(SUBSTITUTE(B5,"host ","")))/5),1)+5, SEARCH(" eq",B5,1)-SEARCH("xx",SUBSTITUTE(B5,"host ","xx",(LEN(B5)-LEN(SUBSTITUTE(B5,"host ","")))/5),1)-5),"")

and also the third value if it is preceeded by either "eq" or "range"

=IFERROR(MID(B5,FIND("eq ",B5,1)+3,10),IFERROR(MID(B5,FIND("range ",B5,1)+6,16),""))

but I'm unable to account for so many possibilities....

Any help is appreciated!

text	first value	text	second value	text	third value
object-group	name	host	ip	any		equals first value	equals second value	equals thrid value
object-group	name	host	ip	eq	characters	equals first value	equals second value	equals thrid value
object-group	name	host	ip	range	characters	equals first value	equals second value	equals thrid value
object-group	name	host	ip	object-group	name	equals first value	equals second value	equals thrid value
object-group	name	object-group	name	any		equals first value	equals second value	equals thrid value
object-group	name	object-group	name	eq	characters	equals first value	equals second value	equals thrid value
object-group	name	object-group	name	range	characters	equals first value	equals second value	equals thrid value
object-group	name	object-group	name	object-group	name	equals first value	equals second value	equals thrid value
host	ip	host	ip	any		equals first value	equals second value	equals thrid value
host	ip	host	ip	eq	characters	equals first value	equals second value	equals thrid value
host	ip	host	ip	range	characters	equals first value	equals second value	equals thrid value
host	ip	host	ip	object-group	name	equals first value	equals second value	equals thrid value
host	ip	object-group	name	any		equals first value	equals second value	equals thrid value
host	ip	object-group	name	eq	characters	equals first value	equals second value	equals thrid value
host	ip	object-group	name	range	characters	equals first value	equals second value	equals thrid value
host	ip	object-group	name	object-group	name	equals first value	equals second value	equals thrid value

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>

 

[jasonb75]

Is all of your data consistent with your examples?

"access-list 95 extended permit tcp" "Text" "First Value" "Text" "Second Value" "Optional Text" "Optional Third Value"

This formula assumes that the first part is always the same and chops of the first 49 characters, then splits the rest of the string using spaces to find the first, third and fifth unbroken text strings.

=TRIM(MID(SUBSTITUTE(MID($A2,49,LEN($A2))," ",REPT(" ",100)),(COLUMNS($B2:B2)-1)*200+1,100))

With your list in column A, assuming first entry in A2, enter the formula into B2, then fill right and down as needed.

 

[vf_guy]

Hi Jason, thanks much for the reply. I failed to mention that the entire text could be preceded by spaces and that the "95" could be numbers or text. Therefore there may be more than 49 characters. Here's another example (preceded by 2 spaces):

" access-list APP-SEGMNT line 19 extended permit tcp host 1.1.1.1 host 2.2.2.2 eq www"

also the IP can obviously be between 7-15 characters. The "line number" can be one or two characters, so I think trying to use a fixed length would be difficult.

Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

I am not finding your examples, layout or explanation very clear.
Could you try providing another set (say 10 rows) of sample data and expected results, with explanation of how to obtain (manually) the expected results?
My signature block below has suggestions for posting small screen shots which may help display data, results and layout more clearly.

 

[jasonb75]

I think that a modified version of my formula should work if we can find a pattern in the data, it might look fixed length, but done correctly it can still pick out the correct part of the string, whether it's 1 character or 100.

I'm going to take the chance that "tcp" could be a common factor to identify the end of the preceding text string, in which case this variation of my formula should work,

=TRIM(MID(SUBSTITUTE(MID($A2,SEARCH("tcp",$A2)+4,LEN($A2))," ",REPT(" ",LEN($A2))),(COLUMNS($B2:B2)-1)*(LEN($A2)*2)+1,LEN($A2)))

If not, then, as Peter said, we would need to see a few more variations in order to try and identify a pattern.