# Extract more than 2 words into various cells

#### rlong98

##### New Member
I have a string of text in Google Sheets - I would assume is the same formula
I can get the first 2 words - but not sure how to expand to get 3rd +

In Cell F2 I have typed: (no bold)
Wall panels for interior wall

FIRST WORD
Code:
``=LEFT(F2; SEARCH(" ";F2;1)-1)``

SECOND WORD
Code:
``=MID(F2; SEARCH(" ";F2) + 1; SEARCH(" ";F2;SEARCH(" ";F2)+1) - SEARCH(" ";F2) - 1)``

THIRD WORD

???????

FOURTH WORD
???????

Thank you!
Rob

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Rick Rothstein

##### MrExcel MVP
With your text in cell F2 and assuming you want to split the words out horizontally, put this formula in cell G2 and copy it across...

=TRIM(MID(SUBSTITUTE(" "&\$F2," ",REPT(" ",300)),COLUMNS(\$G:G)*300,300))

I would also note that you can do what you want using Excel's built-in Text To Columns dialog box (located on the Data tab in the Data Tools panel).

#### tyija1995

##### Well-known Member
Following up on Ricks solution above, if you ever wanted to extract the Nth word from a string and didn't want to cycle through the spanned columns until you hit N then use this generic formula:

=TRIM(MID(SUBSTITUTE(F2," ",REPT(" ",LEN(F2))),(N-1)*LEN(F2)+1,LEN(F2)))

Where N is the nth word you want in the string and F2 is the cell containing the string. I find this trick is very handful from time to time, so always good to know

Thankfully in Excel, TRIM takes out all spaces including excessive spaces in between words, unlike SQL which does not! (When you specify multiple words that is, i.e. M*LEN(F2) at the end of formula above.)

Note that you can put a multiplier in with the last parameter to pick up multiple words starting from the Nth word as well. So instead of just LEN(F2) at the end, you could even have 2*LEN(F2) to pickup 2 words, from the desired Nth word. (Again, this could be good to know!)

Replies
3
Views
72
Replies
10
Views
177
Replies
6
Views
218
Replies
5
Views
524
Replies
0
Views
147

1,129,430
Messages
5,636,227
Members
416,908
Latest member
Streetsweeper

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