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

David Macarthur 0
Viewed 18

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?

excel
2 Answers

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:",""))
Ron Rosenfeld0

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")
Sachin Kohli0
Related