Another approach, inelegant, more brutish, but easily understandable is to use TEXTJOIN(), now that we have it.
Using A2:C2 for the first row, put the following into D2, then copy and paste down. Or Fill, or... you get the idea:
For Textjoin string below, use the TEXTJOIN() function to concatenate the entire range of cells you wish to examine. Use "TRUE" to leave out blanks to shorten the string, and for a delimiter, use a character that realistically will NEVER appear in your data. I use "Ŧ" below (and for the character to substitute the last one with, "Ų"). Using commas like one often does with TEXTJOIN() and its relatives can cause problems.
=RIGHT( Textjoin string,
LEN( Textjoin string ) -
FIND( "Ų", SUBSTITUTE( Textjoin string, "Ŧ", "Ų",
LEN( Textjoin string **with** delimiter ) - LEN( Textjoin string **without** delimiter )
)))
and it's easier to understand. SUBSTITUTE() can do its work starting on an Instance # which would let you find the last use of your delimiter in Textjoin string with delimiter. In the last line, you find the LEN() of the Textjoin string with and with out the delimiter and finding the difference by subtraction. That is the number of delimiters and therefore the Instance # you need.
In the next to last line, you substitute a different character for that instance, then use FIND() to get its position in the strng.
The second line subtracts that position from the overall LEN() of the string to find out how many characters follow it. That tells you how many characters to strip off the right of the string you made.
The first line does just that, leaving you with the contents of whatever last cell was in the range.
The string length Excel will use varies by function, some in the 6-7,000 range, for instance, some more like 32,000. With that in mind (it is why you specify "TRUE"), one can do a HUGE range rather than A2:C2.
Notice that you are then working with the joined string, not cells so:
- You never have to find cell addresses, etc.
- You can actually use it on a range made up of joined "sub" ranges, and ranges made up of REALLY broken apart cells. Discontinuous ranges are you friends and allies.
Because the way the data exists inside pieces being evaluated by Excel inside a formula, breaking chunks out into Named Ranges can cause problems, or not, as the interim results being created and used by Excel evaluating the formula CAN be in a different form than the end result a Named Range presents forward, sometimes one cannot used Named Ranges on pieces to lay out the logic of a formula for future ease. But nothing in the above presents that problem, so you can create Named Ranges for, say, the TEXTJOIN's and enter the rest natively so anyone clicking on the cell can see the logic. Or break the pieces to something logical like "InstanceNumber" (Named Range) so it reads even more easily. Create it, then dump it all into a Named Range. Or don't bother at all with Named Ranges.
As I say, inelegant. Longer than some solutions, but not reallly "brutish" the way some things are. No helper columns, or other things people often cannot use. Or will not. No {array} formulas.
(And you can use the discontinuous ranges when needed.) The approach can also take a pile of text and data that a report engine PDF's and which is then extracted to Excel but chunked differently into cells for each related set (so info about 10 clients, each set in a 10 column by 13 row block but the address for one is in cell 4,6 yet in cell 3,8 for another, yet it follows the same flow, just populates different cells when imported) and by making it a single string, let you formulaically hunt up the parts. Often, anyway. Or take a block of cells and see if a bit of data appears anywhere inside them using functions, not macrros or arrays or one helper cell for each one in the block.