I want to delete the last two words in a string

Hi muharom.habib,

Here is a method to do this. I assume "words" are separated by spaces.

You first replace each space with 100 spaces in the string. Then take the last 200 characters in the string and TRIM it. This will leave the last two words.

I can't tell from your example above what the string is, but the above is how I would do this problem. *To replace each space with 100 spaces use the Substitute function and Rept function.

Maybe this...

Data Range

	A	B
1
2	Modern workshop Yokohama JP	Modern workshop
3	Lazada.co.id 454542145 US	Lazada.co.id
4	Tickt** 14511451 ID	Tickt**
5	agoda.com london UK	agoda.com
6	Jazzy	Jazzy
7
8	Five Four Three Two One	Five Four Three
9

This formula entered in B2 and copied down:

=SUBSTITUTE(A2," "&TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),100)),"")

Originally Posted by muharom.habib

It does not work when some string more than 7 or more words.

Works for me...

Data Range

	A	B
1	1 2 3 4 5 6 7 8 9 10	1 2 3 4 5 6 7 8
2	AA BB CC DD EE FF GG HH II	AA BB CC DD EE FF GG
3	One Two Three Four Five Six Seven Eight Nine Ten	One Two Three Four Five Six Seven Eight

This formula entered in B1 and copied down:

=SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100)),"")