Results 1 to 5 of 5

Thread: modify complex find formula

  1. #1

    modify complex find formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,
    my first post,
    I cant post link to sheet so I have put up a picture with formula below

    I need to modify my formula in B to give me the results I have listed in Colum C
    =IFERROR(IFERROR(MID(A2,FIND("available for",A2)+14,10),MID(A2,FIND("Error: Material",A2)+16,15)),"")

    my result varys in length so using +14,10 after search term is not working well as I'm either losing data or returning too much. can spaces be used ?

    I hope this makes sense
    Click image for larger version. 

Name:	Untitled.jpg 
Views:	14 
Size:	87.9 KB 
ID:	6269

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,666
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Could you please post the sheet on the forum ? ( click Go Advanced - Manage Attachments)

    Is the data to be extracted always between double quotes? (there is one string without them)
    Thank you Ken for this secure forum.

  3. #3
    thanks for the reply Peco. unfortunately double quotes are not guaranteed, there is always a space either side of the result, leaving in double quotes is not an issue, my problem is missing data or too much meaning my pivot is all over the place, i have attached a sample file as requested. Jay.Copy of EXCELGURU.xlsx

  4. #4
    Peco, VBA function may be an option but i dont know much about it, was reading on forum, workbook can have VBA / Macros . basically anything that will work. on my full workbook i have a macro to fill in my pivot so maybe I could use a macro to tidy up my result in column B

  5. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    In B2 cell try formula (copy down)
    Code:
    =IF(TRIM(MID(SUBSTITUTE(A2;"""";REPT(" ";255));255;255))="";MID(A2;FIND("available for";A2)+14;10);TRIM(MID(SUBSTITUTE(A2;"""";REPT(" ";255));255;255)))
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

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