Nested Search Function Excel Formula

[trankim]

I am looking to see if there's a formula that will search for the words "_AcrobatFamily" or "_AcrobatPro" and returns the letters after that underscore. The number of underscores before these words can vary. I can get it to work on one half of the formula...but somehow it's not working with the other half.

My formula below will return "create-fillable-pdf-forms.html" for the first example, but that's about all I can do.......HELP?! Thank you in advance!
=IF(IFERROR(SEARCH("_AcrobatPro",$O9),0),MID($O9,18,65),"")


Data in Column O:
ad_us_AcrobatPro_create-fillable-pdf-forms.html
ad_us_AcrobatFamily_create-pdf-web-forms-builder.html
ad_mena_ar_AcrobatPro_create-fillable-pdf-forms.html
ad_be_fr_AcrobatFamily_create-pdf-web-forms-builder.html
ad_ru_AcrobatFamily_convert-existing-forms-to-fillable-pdfs.html
ad_be_nl_AcrobatFamily_create-pdf-web-forms-builder.html
ad_ap_AcrobatPro_create-fillable-pdf-forms.html
ad_es_AcrobatFamily_convert-existing-forms-to-fillable-pdfs.html
ad_africa_AcrobatFamily_form-creator.html
ad_dk_AcrobatFamily_create-pdf-web-forms-builder.html

[Excel Fox]

Here's one way to do it

=MID(SUBSTITUTE(SUBSTITUTE(O1,"_AcrobatPro","|"),"_AcrobatFamily","|"),SEARCH("|",SUBSTITUTE(SUBSTITUTE(O1,"_AcrobatPro","|"),"_AcrobatFamily","|"))+2,255)

[Admin]

Hi

Couple more ways ..

=REPLACE(SUBSTITUTE(A2,"AcrobatFamily","AcrobatPro"),1,SEARCH("AcrobatPro",SUBSTITUTE(A2,"AcrobatFamily","AcrobatPro"))+10,"")

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"AcrobatFamily_","AcrobatPro_"),"AcrobatPro_",REPT(" ",200)),200,255))

Edit: there is no space between the characters of Family or after AcrobatPro

[trankim]

Thank you...however, see below:

1) Formula used: =MID(SUBSTITUTE(SUBSTITUTE(O9,"_AcrobatPro","|")," _AcrobatFamily","|"),SEARCH("|",SUBSTITUTE(SUBSTITUTE(O9,"_AcrobatPro","|"),"_AcrobatFamily","|"))+2 ,255)

The formula worked for "AcrobatPro" but for "AcrobatFamily", it showed "crobatFamily...."
Results:
create-fillable-pdf-forms.html ad_us_AcrobatPro_create-fillable-pdf-forms.html
crobatFamily_create-pdf-web-forms-builder.html ad_us_AcrobatFamily_create-pdf-web-forms-builder.html


2) Formula used: =TRIM(MID(SUBSTITUTE(SUBSTITUTE(O9,"AcrobatFamily_ ","AcrobatPro_"),"AcrobatPro_",REPT(" ",200)),200,255))
Formula with the "Trim" worked for AcrobatPro, but the results were blank for "AcrobatFamily".

Results
create-fillable-pdf-forms.html ad_us_AcrobatPro_create-fillable-pdf-forms.html
ad_us_AcrobatFamily_create-pdf-web-forms-builder.html


3) Formula used: =REPLACE(SUBSTITUTE(O9,"AcrobatFamily","AcrobatPro"),1,SEARCH("AcrobatPro",SUBSTITUTE(O9,"AcrobatFam ily","AcrobatPro"))+10,"")
This is the only formula that worked and gave the correct output for both AcrobatFamily and AcrobatPro. THANK YOU!!!!!

[Admin]

Thank you...however, see below:

2) Formula used: =TRIM(MID(SUBSTITUTE(SUBSTITUTE(O9,"AcrobatFamily_ ","AcrobatPro_"),"AcrobatPro_",REPT(" ",200)),200,255))
Formula with the "Trim" worked for AcrobatPro, but the results were blank for "AcrobatFamily".

Results
create-fillable-pdf-forms.html ad_us_AcrobatPro_create-fillable-pdf-forms.html
ad_us_AcrobatFamily_create-pdf-web-forms-builder.html



I think that's because I see there is a space after AcrobatFamily. Try without the space.

[Haseeb A]

If always be a "_" after 'Acrobat*', try this version.

=REPLACE(O9,1,FIND("_",O9,SEARCH("Acrobat",O9)),"")

[Rick Rothstein]

If always be a "_" after 'Acrobat*', try this version.

=REPLACE(O9,1,FIND("_",O9,SEARCH("Acrobat",O9)),"")
Assuming the word "Acrobat" cannot appear before the words "AcrobatPro" or "AcrobatFamily", Haseeb's formula is nice and simple and gets my vote. However, if the word "Acrobat" could possibly appear before the words "AcrobatPro" or "AcrobatFamily", then you could consider a formula like this one...

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(O1,"AcrobatFamily","AcrobatPro"),"_AcrobatPro_",REPT(" ",999)),999))