Results 1 to 7 of 7

Thread: Nested Search Function Excel Formula

  1. #1
    Junior Member
    Join Date
    Mar 2012
    Posts
    21
    Rep Power
    0

    Question Nested Search Function Excel Formula

    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,1 8,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

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Here's one way to do it

    =MID(SUBSTITUTE(SUBSTITUTE(O1,"_AcrobatPro","|")," _AcrobatFamily","|"),SEARCH("|",SUBSTITUTE(SUBSTIT UTE(O1,"_AcrobatPro","|"),"_AcrobatFamily","|"))+2 ,255)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Couple more ways ..

    =REPLACE(SUBSTITUTE(A2,"AcrobatFamily","AcrobatPro "),1,SEARCH("AcrobatPro",SUBSTITUTE(A2,"AcrobatFam ily","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
    Last edited by Admin; 10-28-2012 at 05:09 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Junior Member
    Join Date
    Mar 2012
    Posts
    21
    Rep Power
    0
    Thank you...however, see below:

    1) Formula used: =MID(SUBSTITUTE(SUBSTITUTE(O9,"_AcrobatPro","|")," _AcrobatFamily","|"),SEARCH("|",SUBSTITUTE(SUBSTIT UTE(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,"Acroba tFam ily","AcrobatPro"))+10,"")
    This is the only formula that worked and gave the correct output for both AcrobatFamily and AcrobatPro. THANK YOU!!!!!
    Last edited by trankim; 10-29-2012 at 07:09 AM.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Quote Originally Posted by trankim View Post
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Junior Member Haseeb A's Avatar
    Join Date
    Apr 2011
    Posts
    21
    Rep Power
    0
    If always be a "_" after 'Acrobat*', try this version.

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

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Haseeb A View Post
    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,"AcrobatFamil y","AcrobatPro"),"_AcrobatPro_",REPT(" ",999)),999))

Similar Threads

  1. Use Native Excel Function Lookup Formula In VBA
    By ramananhrm in forum Excel Help
    Replies: 8
    Last Post: 05-03-2013, 09:10 AM
  2. Nested If Formula With Multiple Conditions
    By lprc in forum Excel Help
    Replies: 10
    Last Post: 04-22-2013, 07:27 PM
  3. Excel Nested IF Function With 3 Conditions
    By patsir in forum Excel Help
    Replies: 3
    Last Post: 08-25-2012, 07:15 PM
  4. VBA Function to Search in Array
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-10-2012, 11:34 AM
  5. Excel Nested IF 3 Condition Formula
    By yomgi in forum Excel Help
    Replies: 2
    Last Post: 02-20-2012, 11:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •