A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

how to copy the first word or two words from a cell containing a complete sentence to another cell



 
 
Thread Tools Display Modes
  #1  
Old January 28th 07, 05:06 PM posted to microsoft.public.excel.misc
jonny
external usenet poster
 
Posts: 11
Default how to copy the first word or two words from a cell containing a complete sentence to another cell

Hi there,

I have a lot of cells containing descriptions of items - I need to
copy the first 1 or 2 words from each cell into another cell.

Any ideas greatly received!

Thank you,

Jonny

Ads
  #2  
Old January 28th 07, 05:31 PM posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default how to copy the first word or two words from a cell containing a complete sentence to another cell

On 28 Jan 2007 09:06:21 -0800, "jonny" > wrote:

>Hi there,
>
>I have a lot of cells containing descriptions of items - I need to
>copy the first 1 or 2 words from each cell into another cell.
>
>Any ideas greatly received!
>
>Thank you,
>
>Jonny



To return the first two words in cell A2:


=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2&" "," ",CHAR(1),2))-1)

Note that within the SUBSTITUTE function:

A2 & " " <-- 2 spaces between quotes

," " <-- 1 space between the quotes


--ron
  #3  
Old January 28th 07, 05:37 PM posted to microsoft.public.excel.misc
Teethless mama
external usenet poster
 
Posts: 3,718
Default how to copy the first word or two words from a cell containing a c

Copy one word
=LEFT(A1,FIND(" ",A1)-1)

Copy two words
=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

"jonny" wrote:

> Hi there,
>
> I have a lot of cells containing descriptions of items - I need to
> copy the first 1 or 2 words from each cell into another cell.
>
> Any ideas greatly received!
>
> Thank you,
>
> Jonny
>
>

  #4  
Old January 28th 07, 05:44 PM posted to microsoft.public.excel.misc
jonny
external usenet poster
 
Posts: 11
Default how to copy the first word or two words from a cell containing a c

Wow, thanks guys, any idea how to adapt this to do the first 3 and 4
words?

Thanks again.


On 28 Jan, 17:37, Teethless mama
> wrote:
> Copy one word
> =LEFT(A1,FIND(" ",A1)-1)
>
> Copy two words
> =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)
>
> "jonny" wrote:
> > Hi there,

>
> > I have a lot of cells containing descriptions of items - I need to
> > copy the first 1 or 2 words from each cell into another cell.

>
> > Any ideas greatly received!

>
> > Thank you,

>
> > Jonny


  #5  
Old January 28th 07, 06:20 PM posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default how to copy the first word or two words from a cell containing a c

On 28 Jan 2007 09:44:48 -0800, "jonny" > wrote:

>Wow, thanks guys, any idea how to adapt this to do the first 3 and 4
>words?
>
>Thanks again.


All you need to do is modify my function appropriately.

It would be a good idea for you to work through it and understand how it works.
Then you'd be able to figure this out on your own.

=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2&
REPT(" ",NumWords)," ",CHAR(1),NumWords))-1)

where NumWords is the number of words you wish to return.


Give a man a fish and you feed him for a day. Teach a man to fish and you feed
him for a lifetime.
Chinese Proverb


Variations:

Give a man a fish and you feed him for a day; teach him to use the Net and he
won't bother you for weeks!

Give a man a fish and he'll eat for a day. Give a man religion and he'll starve
to death praying for a fish.

Give a man a fish, and you feed him for a day; teach a man to fish, and you get
rid of him on weekends.

Sell a man a fish, and he can eat for a day, teach a man to fish, and you lose
a great business opportunity? Karl Marx


There is a fine line between fishing and just standing on the shore like an
idiot. Steven Wright
--ron
  #6  
Old January 28th 07, 06:28 PM posted to microsoft.public.excel.misc
jonny
external usenet poster
 
Posts: 11
Default how to copy the first word or two words from a cell containing a c

Thanks Ron,

Right I'm off to the shops to buy myself a fishing net - I'm catching
my own fishes from now on!

Thanks again,

Jon


On 28 Jan, 18:20, Ron Rosenfeld > wrote:
> On 28 Jan 2007 09:44:48 -0800, "jonny" > wrote:
>
> >Wow, thanks guys, any idea how to adapt this to do the first 3 and 4
> >words?

>
> >Thanks again.All you need to do is modify my function appropriately.

>
> It would be a good idea for you to work through it and understand how it works.
> Then you'd be able to figure this out on your own.
>
> =LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2&
> REPT(" ",NumWords)," ",CHAR(1),NumWords))-1)
>
> where NumWords is the number of words you wish to return.
>
> Give a man a fish and you feed him for a day. Teach a man to fish and you feed
> him for a lifetime.
> Chinese Proverb
>
> Variations:
>
> Give a man a fish and you feed him for a day; teach him to use the Net and he
> won't bother you for weeks!
>
> Give a man a fish and he'll eat for a day. Give a man religion and he'll starve
> to death praying for a fish.
>
> Give a man a fish, and you feed him for a day; teach a man to fish, and you get
> rid of him on weekends.
>
> Sell a man a fish, and he can eat for a day, teach a man to fish, and you lose
> a great business opportunity? Karl Marx
>
> There is a fine line between fishing and just standing on the shore like an
> idiot. Steven Wright
> --ron


  #7  
Old January 28th 07, 10:23 PM posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default how to copy the first word or two words from a cell containing a c

On 28 Jan 2007 10:28:56 -0800, "jonny" > wrote:

>Thanks Ron,
>
>Right I'm off to the shops to buy myself a fishing net - I'm catching
>my own fishes from now on!
>
>Thanks again,
>
>Jon


You're welcome. Enjoy.


--ron
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
All words and Symbols of keybord should be added to Auto Shapes Parantap Excel Discussion (Misc queries) 1 March 30th 07 04:16 PM
Cell References [email protected] Excel Discussion (Misc queries) 2 November 15th 06 11:37 PM
Urgent date/scheduling calc needed jct Excel Worksheet Functions 3 February 24th 06 01:36 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Copy Word table into Excel cell by cell hg Excel Discussion (Misc queries) 3 December 15th 04 04:43 PM


All times are GMT +1. The time now is 10:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.