extract parentheses or extract word without parentheses and check two IP subnet ranges

[esaji]

I have a column in sheet that contains three common information , two IP ranges , Hostname under parentheses and hostnames without parentheses

10.164.29.38
10.179.14.99
::1 (EXECUTIONSCHEDU)
10.179.5.171 (Teslaable-dms-ns)
10.179.6.152 (DMX-D42WDS.org.gov.local)
10.161.0.13
10.179.6.152 (DMX-D42WDS.org.gov.local)
10.169.3.122 (RPG-DC3-A1P-E5W32)


I am currently using two different formula to calculate , if text is in parentheses copy to new column and another forumula that calcuate two IP subnets and display results if its from a Fortinet VPN range. I wanted to run both forumla with If then else logic that if parentheses is present copy data and if IP range is there type it as Fortinet VPN.

Can I do it gurus?

this forumla is working fine for capturing text under parentheses

=IFERROR(MID(Z2, SEARCH("(", Z2)+1, SEARCH(")",Z2)-SEARCH("(",Z2)-1),"")

second formula in another column to check two subnets if any IP falls unders that range it display type as Fortinet VPN , I want to keep this condition +above together excel to generate the result

=IF(OR(ISNUMBER(SEARCH("10.179.26",Z2)),ISNUMBER(SEARCH("10.179.27",Z2))),"Fortinet VPN","")

can you guys help ?

[AliGW]

Thank you for opening your own thread.

There are instructions at the top of the page explaining how to attach your sample workbook.

[esaji]

thanks for guiding aliGW I have uploaded the sample attachement with forumulaz

[AliGW]

Is this what you are looking for?

=IFERROR(MID(A2, SEARCH("(", A2)+1, SEARCH(")",A2)-SEARCH("(",A2)-1),IF(OR(ISNUMBER(SEARCH("10.179.26",A2)),ISNUMBER(SEARCH("10.179.27",A2))),"Fortinet VPN",""))

[esaji]

yes almost , thanks for the quick response so only one thing its missing is if the hostname is not there IP should be pasted , currently its skipping IP address

[AliGW]

OK - please provide a copy of the workbook with a manually created column showing the answers you want. This was missing from your earlier attachment.

Wait - maybe this?

=IFERROR(MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",A2)-1),IF(OR(ISNUMBER(SEARCH("10.179.26",A2)),ISNUMBER(SEARCH("10.179.27",A2))),"Fortinet VPN",A2))

[esaji]

I think this make it fixed

=IFERROR(MID(A2, SEARCH("(", A2)+1, SEARCH(")",A2)-SEARCH("(",A2)-1),IF(OR(ISNUMBER(SEARCH("10.179.26",A2)),ISNUMBER(SEARCH("10.179.27",A2))),"Fortinet VPN",A2))

as fortinet VPN side of condition was copying empty value (for VALUE#) error , so I just replaced with A2 to bring IP here as there is no empty data in this column

thanks for the help

[AliGW]

Yes, that's what I've just suggested above!!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[esaji]

Yes, that's what I've just suggested above!!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Can I ask one more query realted to this the column contains these values as well Multiple(80) sometime local (8) and Remote(180) how i can get them in by adding third condition check ? I tired this to add in the existing query but its giving me error

=IF(OR(ISNUMBER(SEARCH("Multiple",A4)),ISNUMBER(SEARCH("local",A4))),A4),ISNUMBER(SEARCH("Remote",A4))),A4) - i will be glad if you can help

[esaji]

Can I used the same forumula to find third IP range ?
10.179.28
as below is not working for me ?
=IFERROR(MID(Z2, SEARCH("(", Z2)+1, SEARCH(")",Z2)-SEARCH("(",Z2)-1),IF(OR(ISNUMBER(SEARCH("10.179.26",Z2)),ISNUMBER(SEARCH("10.179.27",Z2))),ISNUMBER(SEARCH("10.179.28",Z2)))),"Fortinet VPN",Z2))