Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-23-2021, 01:55 AM
yacov yacov is online now extract or delete part of rows Windows 10 extract or delete part of rows Office 2016
Competent Performer
extract or delete part of rows
 
Join Date: Oct 2019
Posts: 111
yacov is on a distinguished road
Default extract or delete part of rows

Hi.


I have a column with a lot of lines, each line contains a sentence with a different number of words. I would like in each line to leave only the first two words. Is it possible?
Thanks.
Reply With Quote
  #2  
Old 08-23-2021, 02:24 AM
ArviLaanemets ArviLaanemets is offline extract or delete part of rows Windows 8 extract or delete part of rows Office 2016
Expert
 
Join Date: May 2017
Posts: 640
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

With your sentences in column A, starting from A2, into any other column enter the formula like
Code:
=IFERROR(LEFT($A2,FIND(" ",$A2,FIND(" ",A$2,1)+1)-1),$A2)
, and copy down.
Reply With Quote
  #3  
Old 08-23-2021, 02:39 AM
yacov yacov is online now extract or delete part of rows Windows 10 extract or delete part of rows Office 2016
Competent Performer
extract or delete part of rows
 
Join Date: Oct 2019
Posts: 111
yacov is on a distinguished road
Default

thanks a lot. works great.
Reply With Quote
  #4  
Old 08-23-2021, 03:05 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline extract or delete part of rows Windows 7 64bit extract or delete part of rows Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Another approach
Code:
=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99),2),100))
Reply With Quote
  #5  
Old 08-23-2021, 05:07 AM
Purfleet Purfleet is offline extract or delete part of rows Windows 10 extract or delete part of rows Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 335
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Not sure with out an example but flash fill should work
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
extract or delete part of rows Delete blank table rows in merged document and split document in docx and pdf based on excel rows Alex1s85 Word VBA 5 05-22-2021 12:05 PM
Macro to extract CCIs as separate entries when part of a text string Cray_Z Word VBA 5 01-13-2021 03:45 PM
extract or delete part of rows Delete blank rows between the two rows that contain data beginner Excel Programming 5 12-26-2014 12:29 AM
extract or delete part of rows Delete All empty Rows - Print - Undo all Rows deleted Bathroth Word VBA 1 10-01-2014 01:40 PM
Is there a way to delete the beginning part of an array so it doesn't overflow? omahadivision Excel Programming 4 01-15-2014 11:22 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:13 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft