How to fish out 6-characters or more word from a cell's sentence?

[Rediska]

Halloooo Excelianssss,

PLease help!
How to detect a long word in a free-text sentence in a cell?
I need to detect a word which is at least 6 characters/digits long and extract it for further correction by data entry (occasionally two words are typed together).
Assumption: only one "sticky" word per cell.
Examples:

hot shot skyfly sun 23 b786 - i need to detect skyfly
choco2late is good for you - detect choco2late (may contain number)

I'll need a formula, and not a macro. Is it possible?

Thanks!
Cheers!

 

[Haseeb A]

Hello Rediska,

I hope you are using Excel 2007 or above. If so here is one way:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),MATCH(TRUE,MMULT(IFERROR(FIND("^",SUBSTITUTE(" "&A1&" "," ","^",ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))+{0,1}))*{-1,1},0),{1;1})-1>5,0)*99-98,99))

This is an Array Formula. So must be confirmed with CTRL+SHIFT+ENTER

 

[Rediska]

thanks a lot, it perfect!!!

 

[shrivallabha]

Here is one more Idea.

Assuming your data starts in A1, goto name manager and define a name "Distrib" and put following in it:
=FREQUENCY(ROW($A$1:INDEX(A:A,LEN($A1&" "))),IF(MID($A1&" ",ROW($A$1:INDEX(A:A,LEN($A1&" "))),1)=" ",ROW($A$1:INDEX(A:A,LEN($A1&" "))),0))

And then in cell B1 or any cell put following formula(normal entry):
=IF(LEN(MID(A1,MATCH(MAX(Distrib),Distrib,0)-MAX(Distrib)+1,MAX(Distrib)-1))>5,MID(A1,MATCH(MAX(Distrib),Distrib,0)-MAX(Distrib)+1,MAX(Distrib)-1),"")

Change the bold red part to suit like if you want n characters minimum then assign n-1 there.