modify complex find formula

tintin1012000

New member
Joined
Jan 16, 2017
Messages
3
Reaction score
0
Points
0
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
Untitled.jpg
 
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)
 
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.View attachment Copy of EXCELGURU.xlsx
 
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
 
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)))
 
Back
Top