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

J

jonny

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
 
R

Ron Rosenfeld

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
Click to expand...


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
 
T

Teethless mama

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

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

jonny

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

Thanks again.
 
R

Ron Rosenfeld

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

Thanks again.
Click to expand...

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
 
J

jonny

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
 
R

Ron Rosenfeld

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
Click to expand...

You're welcome. Enjoy.


--ron
 
Top