Use first two words only

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hi

I have a column showing trade names, each cell can contain up to six words
but I want to retain the first two words only.

Is there a way for me to save the column with the first two words only
showing?

thanks

Daniel
 
One way.....
select the column/area
go to <>data<>text to columns
select "delimited" , next, space, next, select the last column(s) and tick
"do not import"
select a destination
finish

You may need to combine the two resulting columns using the concatenate
function.
 
Daniel,

If you want a formula

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

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bill

thanks for your help, it worked well and I learnt
something in the process.

regards

Daniel
from Australia "World Cricket Champions"
 
Hi,

Although I prefer Bob's solution for this specific case, if you have XL2000
or higher and many non-consequent words to extract, you could also use the
following UDF in order to extract any words you want from your string:

Function ExtractWords(txt As String) As Variant
ExtractWords = Split(txt, " ")
End Function

you can call it from the worksheet like this:

=INDEX(ExtractWords(A1),1) & " " & INDEX(ExtractWords(A1),2)

Regards,
KL
 
Thanks KL, it's all good stuff and I appreciate
all I can get.
I save it all in an Excel Help file on my PC and
refer to them when the need arises.

regards

Daniel
 
Thanks for the feed backs
I did like the solutions Bob Phillips and KL gave us.
Bill K
from New Zealand " World Cricket Champions in the making"
 
Back
Top