Luc, thank you for your response. I want to apologize to all for not doing detailed testing. I shouldn't have asked ...... It was late and I wasn't thinking clearly. While I did my test on " *", I failed with " ^". I kept thinking that even if " ^" existed in the string it would still be okay because " ^" will be converted to " ^^". So, " ^" is still there to be searched. I focused on SUBSTITUTE which could be specified with instance_num and for a minute I forgot that SEARCH was a different story. It will grab the *first* instance. I used good test data today and " ^" failed miserably. On the bright side, this helps me realize that Luc's formula is more beautiful than I first thought - case in point TRIM and double spaces; TRIM will remove extra spaces even *between* words and not just the leading and trailing spaces of the string.

Thank you for an interesting question and a brilliant answer.

Epinn

"PapaDos" wrote in message ...
Using " ^" is not guaranteed to work as expected.

Since we don't know which characters could be part of the string, we need to
use a combination that will not be present in it.

With my first formula, only the double spaces is guaranteed to work.
With my second one (which is more robust), we have a few more choices;
CHAR(160), double spaces, one of the characters eliminated by CLEAN(), or a
combination of them...

--
Regards,
Luc.

"Festina Lente"


"Epinn" wrote:

Luc, brilliant! I can change the number of words I want easily by just changing the instance.

Roger, you said it. I played with the formula (first one) before you posted. I copied and pasted it and there was no problem whatsoever i.e. I got the correct result. Just because it was so smooth, I didn't even know we were supposed to substitute one space with two spaces. At this point, I had not figured out the logic yet. I only found out about the double spaces when I tried to remove some spaces in the formula. I deleted one space between double quotes thinking that it was an extra space and I got #VALUE error. When I finally sorted out everything (including the logic), I decided to make the double spaces more visible by changing it to " *". Of course, I didn't get the right result. It stopped after the first word. Please confirm that " *" is treated as wildcard. Now, I am going to use " ^" instead of double spaces purely for visibility reasons. Does anyone see a problem with my change?

Appreciate guidance.

Epinn

"Roger Govier" wrote in message ...
Hi Luc

An excellent solution, but because of text wrapping in the posting,
anybody copying and pasting the formula may get a problem.

In both of the Substitute terms,
it is a single space " " that is substituted by a double space " "

When I copied and pasted, it somehow dropped one of the double spaces in
the first term, and of course this returned the whole of the text from
cell A1.

--
Regards

Roger Govier


"PapaDos" wrote in message
...
=LEFT( TRIM( A1 ), IF( ISERR( SEARCH( " ", SUBSTITUTE( TRIM( A1 ), "
", "
", 3 ) ) ), LEN( A1 ), SEARCH( " ", SUBSTITUTE( TRIM( A1 ), " ", "
", 3 ) )
- 1 ) )
--
Regards,
Luc.

"Festina Lente"


"Dinesh" wrote:

Is their a function to extract first 3 words from a text string?

Thanks.

Dinesh Shah