# Extracting words

##### Active Member
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Re: Extacting words

Is this what you need?

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

Re: Extacting words

You could try this:

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

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.

Re: Extacting words

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

Thanks again

Re: Extacting words

Deleted.

Last edited:
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))

Re: Extacting words

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))

Last edited:
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.

Replies
6
Views
373
Replies
5
Views
169
Replies
9
Views
635
Replies
4
Views
4K
Replies
5
Views
644

1,196,042
Messages
6,013,049
Members
441,746
Latest member
ArtemisAlex

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back