for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

JCB Apr 2019 11:30am, 29 Apr 2019 403 posts	Quote Pin JCB While they are cool, are they not susceptible to multiple spaces between words? 😉 And for the first one there would be some word length limitations... Could use trim() around A1/A2 before doing the substitute().
Markymarkmark Apr 2019 11:33am, 29 Apr 2019 8,852 posts	Quote Pin Markymarkmark Those look like solutions looking for a problem..... Very ingenious though!
GregP Apr 2019 12:08pm, 29 Apr 2019 112,163 posts	Quote Pin GregP =RIGHT(LEFT(A1,LEN(A1)-(LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1)),LEN(LEFT(A1,LEN(A1)-(LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1)))-FIND("@",SUBSTITUTE(LEFT(A1,LEN(A1)-(LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1))," ","@",LEN(LEFT(A1,LEN(A1)-(LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1)))-LEN(SUBSTITUTE(LEFT(A1,LEN(A1)-(LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1))," ",""))))+1-1)
larkim Apr 2019 12:11pm, 29 Apr 2019 7,324 posts	Quote Pin larkim I was hoping that would return 55378008 and I'd have to turn my spreadsheet upside down
GregP Apr 2019 12:18pm, 29 Apr 2019 112,165 posts	Quote Pin GregP I gave the problem to my equivalent on P&P, and that's what he came up with...
Sam Jelfs Apr 2019 12:33pm, 29 Apr 2019 280 posts	Quote Pin Sam Jelfs is this not the time to go for vb and regex?
icenutter Apr 2019 1:29pm, 29 Apr 2019 199 posts	Quote Pin icenutter Looks like a formula for the Hokey Kokey.
oumaumau May 2019 1:56pm, 22 May 2019 1,104 posts	Quote Pin oumaumau Hi Excel experts, I have this problem. Three sets of race results, and need to find the quickest for each participant. To add to the complexity not all participants have completed all three races, so my query looks like this: IF(ISBLANK(D2),(IF(ISBLANK(E2),F2,IF(ISBLANK(F2),E2,IF(E2<F2,E2,F2)))),(IF(ISBLANK(E2),(IF(ISBLANK(F2),D2,IF(D2<F2,D2,F2))),(IF(ISBLANK(F2),IF(D2<E2,D2,E2),IF(D2<E2,IF(D2<F2,D2,F2),IF(E2<F2,E2,F2))))))) There must be an easier way.... And of course there will be g2 to consider next year...
SPR May 2019 2:05pm, 22 May 2019 27,918 posts	Quote Pin SPR =MIN(cell refs) would work and ignores blanks
oumaumau May 2019 2:09pm, 22 May 2019 1,105 posts	Quote Pin oumaumau Full disclosure I'm using libreoffice, and min doesn't seem to like times in hh:mm:ss format...