Extract more than 2 words into various cells

rlong98

New Member
Joined
Dec 6, 2019
Messages
11
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Web
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.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,375
Office Version
  1. 365
Platform
  1. Windows
This article might help, although I can't vouch for Google Sheets' functionality with these formulas.

 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,970
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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!)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top