Extracting words

[Adrac]

Hello All,

I have a cell A1 that has more than 4 words in a cell. is there a formula to extract the first 4 words to B1?

Cell example: DR GREEN POSSITION TOTAL FINAL COMPLETE

Adrac

 

[István Hirsch]

Re: Extacting words

Is this what you need?

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",200)),200,800))

 

[AliGW]

Re: Extacting words

You could try this:

=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",4))-1)

 

[AliGW]

Re: Extacting words

Is this what you need?

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",200)),200,800))[/QUOTE

That seems to extract words 2, 3, 4 and 5.

 

[Adrac]

Re: Extacting words

Thank you to everyone for their input, they are all work fine so ill use one.

Thanks again

Adrac

 

[István Hirsch]

Re: Extacting words

Deleted.

 

[István Hirsch]

Re: Extacting words

Is this what you need?

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",200)),200,800))[/QUOTE

That seems to extract words 2, 3, 4 and 5.

Thanks, you are right. Corrected version:

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",200)),200,800))

 

[AliGW]

Re: Extacting words

Glad to have helped.

 

[Rick Rothstein]

Re: Extacting words

Is this what you need?

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",200)),200,800))[/QUOTE

That seems to extract words 2, 3, 4 and 5.

István should have written it either this way...

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",200)),1,800))

or this way...

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",200)),200,800))

 

[István Hirsch]

Re: Extacting words

István should have written it either this way...

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",200)),1,800))

or this way...

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",200)),200,800))

Thanks Rick, modified in the meantime to the second version. Still, the formula in post #3 is better than mine.