Formula to remove 2nd word from a string of text

Hi
you have posted in the wrong forum. i will move it for you.
Please read our rules before proceeding

If your original piece of text is in cell A2, then in B2 enter:-

=SUBSTITUTE(A2,MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-FIND(" ",A2,1)),"")

The above formula will remove the 88V from DRBS 88V XRD Database Management System, then in cell C2 enter:-

=SUBSTITUTE(B2,MID(B2,FIND(" ",B2,1)+1,FIND(" ",B2,FIND(" ",B2,1)+1)-FIND(" ",B2,1)),"")

This will give you DRBS Database Management System

Then in D2 enter:-

=SUBSTITUTE(C2,MID(C2,FIND(" ",C2,1)+1,FIND(" ",C2,FIND(" ",C2,1)+1)-FIND(" ",C2,1)),"")

This will leave you with:-

DRBS Management System

Originally Posted by Gary Brown

If your original piece of text is in cell A2, then in B2 enter:-

=SUBSTITUTE(A2,MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-FIND(" ",A2,1)),"")

Thanks a lot.

I will do as you suggested. Could you please suggest me what part of the above formula, i should tweak, if I need to remove 3rd word and not the second one, as suggested in the above formula.

Actually i need to remove 3rd words separately, and even 4th word ...

I want the following output from the sentence " "DRBS 88V XRD Database Management System"

"DRBS XRD Database Management System" (removing 2nd word)

"DRBS 88V Database Management System" (removing 3rd word)

"DRBS 88V XRD Management System" (removing 4th word)

"DRBS 88V XRD Database System" (removing 5th word)

I am new to excel, so will not be able to make sense of it without knowing which part to tweak,

Thanks again

Look this:

Originally Posted by zbor

Look this:

Thanks for the formula, but it does the same thing - remove 2nd word. what about 3rd, 4th and so on?

I liked the previous formula because it was not too lengthy. Please help me by giving the formula to remove 3rd word using either your or aforementioned formula then I can make head and tail of the formula

Originally Posted by MarvinP

Try a shorter formula with a single number change to remove the kth word.
If you want to remove the second word from the text in cell A1 use:

Please Login or Register  to view this content.

I owe the above to DonkeyOte who showed us how to do it.

Thanks a lot for this formula. This worked.

The above formula work as a charm when i am removing 2nd, 3rd, and 4th word from beginning.


I was wondering will this formula work if i want to remove 2nd, 3rd or 4th words from the end of the string? A string could be 3 word long or 12 or 32 word long.

Originally Posted by bikramksingh

Thanks for the formula, but it does the same thing - remove 2nd word. what about 3rd, 4th and so on?

Instead of number 2 enter number 1,2,3,4... or any other positive number in the box.

But glad you find solution....

Originally Posted by zbor

Instead of number 2 enter number 1,2,3,4... or any other positive number in the box.

But glad you find solution....

Thaks a lot. Now I have 2 and 3 different way to achieve this result..


I was wondering will the formula you suggested work if i want to remove 2nd, 3rd or 4th words from the end of the string?

A string could be 3 word long or 12 or 32 word long, so i cannot say remove 7th word if i want to remove second last word in a 8-word string, nor i can say remove 6th word in 8-word long string, if i want to remove 3rd word from the end.

Will this formula help?

Why don't you try it

Example removing words from the beggining...

So if you need to remove 2nd string from the end of 8 words you must enter 6.

Hi,

I have tried it, and I understand that if we have a sentence with a fixed number of words in each cell then this works, but what if i do not know the length of the sentence (or each cell has a sentence each of varied lengths) and still i need to remove 2nd or 3rd or 4th word from the end?

Thanks a lot. This worked.

You all have been very helpful