Reference other cell but only show last word

[Excel Pooh]

Good afternoon friends, I am calling on your wisdom yet again. I have an IF formula that pulls a text string from a referenced cell, but I would like it to only show the last word. Right now it may pull "Los Angeles Millwork" or "Texas Truss" or the like, I would like it to only display "Millwork" or "Truss". I have been able to do this inside more simple formulas in the past, but am not sure how to apply it to my current formula. ANy ideas how I could adjust the below to only show the last word? Thanks in advance!

=IFERROR(IF(INDIRECT("'"&$Q$1&"'!E3")="Home",INDIRECT("'"&$Q$1&"'!C3"),INDIRECT("'"&$Q$1&"'!A3")),"-")

 

[PeteWright]

Since you did not provide any data, I can't tell what the code should look like.

If you only need a formula for the last word, use this and change all "A1" instances to the cell you need the word from:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

 

[Peter_SSs]

Try adjusting your formula to this

=IFERROR(TEXTAFTER(IF(INDIRECT("'"&$Q$1&"'!E3")="Home",INDIRECT("'"&$Q$1&"'!C3"),INDIRECT("'"&$Q$1&"'!A3"))," ",-1),"-")

Or, if the formula is rearranged it can be shortened somewhat like this

=IFERROR(TEXTAFTER(INDIRECT("'"&$Q$1&"'!"&IF(INDIRECT("'"&$Q$1&"'!E3")="Home","C3","A3"))," ",-1),"-")