SpreadEveryone: The Fetchland Excel wire

101 watchers
Nessie
Nessie
Apr 2019
3:27pm, 25 Apr 2019
760 posts
  •  
  • 0
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
larkim
Apr 2019
3:31pm, 25 Apr 2019
7,276 posts
  •  
  • 0
larkim
(Pointing and laughing)

tbf, it is a handy trick.
larkim
larkim
Apr 2019
3:36pm, 25 Apr 2019
7,277 posts
  •  
  • 0
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
SPR
Apr 2019
3:51pm, 25 Apr 2019
27,757 posts
  •  
  • 0
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
GregP
Apr 2019
8:49am, 29 Apr 2019
112,144 posts
  •  
  • 0
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
SPR
Apr 2019
9:12am, 29 Apr 2019
27,806 posts
  •  
  • 0
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
SPR
Apr 2019
9:14am, 29 Apr 2019
27,807 posts
  •  
  • 0
SPR
I like it!
larkim
larkim
Apr 2019
9:14am, 29 Apr 2019
7,316 posts
  •  
  • 0
larkim
A clever kludge!
GregP
GregP
Apr 2019
9:51am, 29 Apr 2019
112,146 posts
  •  
  • 0
GregP
Beautifully pragmatic :)
Diogenes
Diogenes
Apr 2019
9:58am, 29 Apr 2019
36,239 posts
  •  
  • 0
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)))

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...

Related Threads

  • excel
  • support
  • tech
  • work








Back To Top

Tag A User

To tag a user, start typing their name here:
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 113,884 Fetchies!
Already a Fetchie? Sign in here