Finding Next Cell with Specific Text

[Askeau]

I need a formula for Column A that searches Column B for the next occurrence of the text, "Total" and then returns the text after a space. I'm frankly at a loss though. I'm fine using an additional Column if necessary
I've been making goes at it with Search, Match, Lookup (but I suck with array/DB formula) and a bunch of various combinations of ISNUMBER, IF, OR, AND, etc.

Optimally, I'd like the function to permanently contain the text as I'll need to use the "Text to Columns" feature to separate line numbers from the cells that don't contain the word "Total" after I complete the room type labeling, although I suppose I can delimit by the period which would potentially work.

Desired Result:

 

[duggie33]

Hi *Askeau,

Try this:

=RIGHT(INDEX($B2:$B$20,MATCH("Total:*",$B2:$B$20,0),1),LEN(INDEX($B2:$B$20,MATCH("Total:*",$B2:$B$20,0),1))-7)

You will have to adjust the ranges to fit your data. This could be condensed using the LET function.

I hope that helps,

Doug

 

[Askeau]

Hi *Askeau,

Try this:

=RIGHT(INDEX($B2:$B$20,MATCH("Total:*",$B2:$B$20,0),1),LEN(INDEX($B2:$B$20,MATCH("Total:*",$B2:$B$20,0),1))-7)

You will have to adjust the ranges to fit your data. This could be condensed using the LET function.

I hope that helps,

Doug

It didn't 100% work, but it was close enough that I was able to use the knowledge base I have to get there.

Wouldn't have made it without you! Thank you very much for the help!

Just needed to remove the colon in the "Total" search string as sometimes it is Totals, sometimes it's Total, and remove the $ by the row numbers, definitely wanted to keep the column $B one though. I did NOT want to manually edit each row of the formula for the search string when pasting this in. The document length varies between 20 rows and sometimes exceeds several thousand.

 

[duggie33]

You're welcome. Glad to help.