Re: extract word function

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Typo
.....If B1 were left blank, then it would pick up all of the text
from A1

Should have read if D1 were left blank.

--
Regards

Roger Govier


"Roger Govier" <roger@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:u7IYa3oDHHA.3396@xxxxxxxxxxxxxxxxxxxxxxx
Hi

It is the number 3 in Luc's formula that you would need to amend, to
the number of words you wanted.
It might be easier to refer to another cell, e,g D1, and enter the
number of words required in that cell.
A single change of value here would save having to keep modifying the
formula. If B1 were left blank, then it would pick up all of the text
from A1

=LEFT( TRIM( A1 ),
IF( ISERR(
SEARCH( " ", SUBSTITUTE( TRIM( A1 ), " ", " ", $D$1 ) ) ),
LEN( A1 ),
SEARCH( " ", SUBSTITUTE( TRIM( A1 ), " ", " ", $D$1 ) )- 1 )
)

--
Regards

Roger Govier


"Dinesh" <Dinesh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F96D6810-CB54-4752-8409-5DFE9FB8470C@xxxxxxxxxxxxxxxx
Thanks a lot. What criteria do I change if I want to extract 2 words
or 4
words from this formula?

"Dinesh" wrote:

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

Thanks.

Dinesh Shah





.

Relevant Pages

  • Re: extract word function
    ... It is the number 3 in Luc's formula that you would need to amend, ... It might be easier to refer to another cell, e,g D1, and enter the ... Dinesh Shah ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Choose a specific calculating formula for a cell
    ... Roger Govier ... Amend to suit. ... cell has the value 07/04/2007 14.30 Time delivered ...
    (microsoft.public.excel.programming)
  • Re: Formula help
    ... Roger Govier ... These are mutually exclusive however for each cell within the range. ... Create an array of Ottawa and Toronto, ...
    (microsoft.public.excel.misc)
  • Re: shade cell shortcut without bringing up palette
    ... "Roger Govier" wrote: ... Alt+1 will fill the active cell with Yellow. ... I have the shortcut set up already - I am trying to highlight the cell ... without the color palette showing up. ...
    (microsoft.public.excel.misc)
  • Re: Sort alphanumeric
    ... "Roger Govier" wrote: ... The reason is, if you had entered a number like 1 or 23 in the cell, ... would fail, as it would be trying to take string of 0 or negative ... Try using a helper column. ...
    (microsoft.public.excel.misc)