Search code examples
exceltextpartial

Find partial text in range, return cell value

I have an array of data that contains 3 columns and the rows increase as more data gets added. I have some known values such as XX-12345, XXX-12345 or XXXX-12345 that I need to identify and return the content in that cell. The problem is that the naming convention isn't standard, so the value may look like the following: XX-12345-Temp, XX-12345.Temp XXX-12345-Temp, XXX-12345.Temp or XXXX-12345-Temp, XXXX-12345.Temp

Below is the code that I have to perform this function, but it doesn't always work. Sometimes it returns a zero and I cannot figure out why it is doing that.

    =INDIRECT("R"&REPLACE(TEXT(MIN(IF(ISNUMBER(SEARCH(F2,$A$2:$C$15349)),(ROW($A$2:$C$15349))*1000+COLUMN($A$2:$C$15349))),"000000"),4,0,"C"),FALSE)
Solution

  • I'm not sure you really need to be involving REPLACE in this construction. You can use:

    =INDIRECT(TEXT(MIN(IF(ISNUMBER(SEARCH(F2,$A$2:$C$15349)),10^5*ROW($A$2:$C$15349)+COLUMN($A$2:$C$15349))),"R0C00000"),FALSE)

    Regards