Extract last two words from string

**dimid** · 11-28-2008, 10:27 PM

I need to extract the last two words from strings, the words are not the same character length. Also each two words is after the word 'by' basically it's a product then says who it's by. So if anyone could help with a formula to extract the last two words or at least all words after the word 'by' I would really appreciate it.

**daddylonglegs** · 11-28-2008, 10:44 PM

if your data is in A1 try this formula to extract the last 2 words

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",60)),120))

**dimid** · 11-28-2008, 10:57 PM

Thanks that works great, i just noticed not all cells have only two words others have 3 others just 1, is there a formula that can extract all words after the word 'by'. Thanks again for a fast reply

**Max, Singapore** · 11-28-2008, 11:04 PM

.. formula to extract .. at least all words after the word 'by'

Assuming text in A2 down

Try in B2, copied down:
=TRIM(MID(A2,SEARCH(" by ",A2)+3,99))

The single space before/after the word "by" is intentional
Replace SEARCH with FIND if you need it to be a stricter case sensitive search