Extract more than 2 words into various cells

[rlong98]

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

=LEFT(F2; SEARCH(" ";F2;1)-1)

SECOND WORD

=MID(F2; SEARCH(" ";F2) + 1; SEARCH(" ";F2;SEARCH(" ";F2)+1) - SEARCH(" ";F2) - 1)

THIRD WORD
???????

FOURTH WORD
???????

Thank you!
Rob

 

[DRSteele]

This article might help, although I can't vouch for Google Sheets' functionality with these formulas.

Split text cell into columns of words.

If you have a cell containing a text string with spaces in it and you want to split it by spaces into columns, you can easily do so by using the Text-to-Columns functionality. Or you can use Power Query to clean up and split everything. Or if...

www.mrexcel.com

 

[Rick Rothstein]

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]

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