Excel: How to find a string within a range and export the value of that cell (No VBA)-CodePudding

I'm looking to find a string value, in this case the words "Alert reference: " within a range, here shown as A1:A25 and then display all text after that in the cell using one line of formula.

I'm close I think using MATCH to find the row within the range, a combo of RIGHT, LEN and SEARCH to print the string required. Combining the two I tried LET to define the MATCH value as the RefRow and then tried a few versions of the below to set that as the value used in RIGHT/LEN/SEARCH but it's not quite coming together.

=LET(RefRow,MATCH("*Alert reference: *",A1:A25,0),RefTotal,A&RefRow,RIGHT("RefTotal",LEN("RefTotal")-SEARCH(":",RefTotal)-1))

I feel like it's close but I'm just missing something. Any suggestions other than VBA or giving up and using multiple cells?

CodePudding user response：

Try this to extract string after "Alert reference: "... A bit lengthy though... :D

=IFERROR(MID(OFFSET(A2,MATCH("*"&"Alert reference: "&"*",A2:A25,0)-1,0),SEARCH("Alert reference: ",OFFSET(A2,MATCH("*"&"Alert reference: "&"*",A2:A25,0)-1,0),1) LEN("Alert reference: "),LEN(OFFSET(A2,MATCH("*"&"Alert reference: "&"*",A2:A25,0)-1,0))-SEARCH("Alert reference: ",OFFSET(A2,MATCH("*"&"Alert reference: "&"*",A2:A25,0)-1,0),1)-LEN("Alert reference: ") 1),"NA")

CodePudding user response：

Hard to be sure without a realistic example. But maybe something like:

=TRIM(SUBSTITUTE(INDEX(A1:A25,MATCH("Alert Reference:*", A1:A25,0)),"alert reference:",""))