formula help

vf_guy

New Member
Joined
Oct 10, 2014
Messages
2
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!


textfirst valuetextsecond valuetextthird value
object-groupnamehostipanyequals first valueequals second valueequals thrid value
object-groupnamehostipeqcharactersequals first valueequals second valueequals thrid value
object-groupnamehostiprangecharactersequals first valueequals second valueequals thrid value
object-groupnamehostipobject-groupnameequals first valueequals second valueequals thrid value
object-groupnameobject-groupnameanyequals first valueequals second valueequals thrid value
object-groupnameobject-groupnameeqcharactersequals first valueequals second valueequals thrid value
object-groupnameobject-groupnamerangecharactersequals first valueequals second valueequals thrid value
object-groupnameobject-groupnameobject-groupnameequals first valueequals second valueequals thrid value
hostiphostipanyequals first valueequals second valueequals thrid value
hostiphostipeqcharactersequals first valueequals second valueequals thrid value
hostiphostiprangecharactersequals first valueequals second valueequals thrid value
hostiphostipobject-groupnameequals first valueequals second valueequals thrid value
hostipobject-groupnameanyequals first valueequals second valueequals thrid value
hostipobject-groupnameeqcharactersequals first valueequals second valueequals thrid value
hostipobject-groupnamerangecharactersequals first valueequals second valueequals thrid value
hostipobject-groupnameobject-groupnameequals first valueequals second valueequals thrid value

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>
 
Last edited:

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,873
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Oct 10, 2014
Messages
2
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,305
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,873
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,235
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top