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

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

Hi Jonny,

Sure thing! You can easily copy the first one or two words from a cell containing a complete sentence to another cell by using the LEFT function in Excel. Here's how:

1. Select the cell where you want to copy the first one or two words to.
2. Type the following formula:
   Formula:

   ---

   =LEFT(A1,FIND(" ",A1)-1) 


   ---

   for the first word or
   Formula:

   ---

   =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1) 


   ---

   for the first two words. (Note: replace A1 with the cell reference of the cell containing the complete sentence you want to extract the words from).
3. Press Enter.

The LEFT function extracts a specified number of characters from the beginning of a text string. In this case, we're using the FIND function to locate the position of the first or second space in the cell containing the complete sentence, and then subtracting 1 from that position to exclude the space itself.

Hope this helps! Let me know if you need further assistance.

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

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

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

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

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

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