for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

1 lurker | 102 watchers

Nessie Apr 2019 3:27pm, 25 Apr 2019 760 posts	Quote Pin Nessie Someone showed me that once, but I'd forgotten how to do it. I'm going to print that out and keep it with my formula to get rid of #N/As from lookup results.
larkim Apr 2019 3:31pm, 25 Apr 2019 7,276 posts	Quote Pin larkim (Pointing and laughing) tbf, it is a handy trick.
larkim Apr 2019 3:36pm, 25 Apr 2019 7,277 posts	Quote Pin larkim Though be aware it a) applies to all blank cells, whether or not you tick the "skip blanks" and b) will apply the format of the "-1" cell. It also affects hard values differently to formulas, so "850" becomes "-850" but "=power(t17,2)" becomes "=power(T17,2)*-1"
SPR Apr 2019 3:51pm, 25 Apr 2019 27,757 posts	Quote Pin SPR Yeah knew that one here. Also knew about the formula 'issue'. Never knew the other issue but would generally use filter or go to special to select the cells if I needed to.
GregP Apr 2019 8:49am, 29 Apr 2019 112,144 posts	Quote Pin GregP This is brilliant - I got it from Mr Excel and it returns the penultimate word in a sentence. Blowed if I know how it works though. Anyone able to explain? =TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ", REPT(" ",100)),200),100))
SPR Apr 2019 9:12am, 29 Apr 2019 27,806 posts	Quote Pin SPR It adds, 100 spaces between each word using REPT and substitute, takes the 200 to the right which will include two words and a load of spaces, then takes 100 to the left which will drop the last word in the sentence, then uses trim to remove the spaces.
SPR Apr 2019 9:14am, 29 Apr 2019 27,807 posts	Quote Pin SPR I like it!
larkim Apr 2019 9:14am, 29 Apr 2019 7,316 posts	Quote Pin larkim A clever kludge!
GregP Apr 2019 9:51am, 29 Apr 2019 112,146 posts	Quote Pin GregP Beautifully pragmatic
Diogenes Apr 2019 9:58am, 29 Apr 2019 36,239 posts	Quote Pin Diogenes I'm always gobsmacked by the clever ways people find of doing things in Excel, that I'd never be able to work out myself, or contemplate trying. I found this similar alternative method of achieving the same thing (this example returns the last 3 words) =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),3*LEN(A2)))