Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 28th 07, 06:06 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
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


  #2   Report Post  
Old January 28th 07, 06:31 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old January 28th 07, 06:37 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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   Report Post  
Old January 28th 07, 06:44 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
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   Report Post  
Old January 28th 07, 07:20 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old January 28th 07, 07:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
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   Report Post  
Old January 28th 07, 11:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 16th 06 12:37 AM
Urgent date/scheduling calc needed jct Excel Worksheet Functions 3 February 24th 06 02: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 05:43 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017