Remove Duplicate Words from 2 Cells when 2nd cell has a sentance.

[manoi]

I have 2 columns. Column A has product name, Column B in some cases has product name + description. I want to strip out the product name in Column B if it exists, and then in a second operations put it back.

Example.

--------------------------------------------------------After Removed------------------END RESULTS
Column A---------------Column B-------------------------------Column C-------------------------------COLUMN D

Brown Fox--------The best of the best.-----------------------The best of the best.------------Brown Fox The best of the best.
Red Fox----------Red Fox is the best of the best.------------- is the best of the best.----------Red Fox is the best of the best.
Gray Fox 33------Grey Fox is the best of the best--------------is the best of the best----------Gray Fox 33 is the best of the best
Black Fox 22------Black Fox 22 is the best of the best.---------is the best of the best.----------Black Fox 22 is the best of the best.



*** COLUMN D IS DESIRED RESULTS -- NOTE HOW END RESULT = A+C=D

what are the function(S) to perform this?

[dilipandey]

Hi Manoj,


Welcome to the forum.
See the attached file, green cells. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[manoi]

The formula you provided removed the first 2 words in column A. I have between 1-15 words in Column A that I need to remove from column B. What is the formula that will remove all words (in specific order) A from B?

Example
A---------------------------B----------------------------------------------C
the quick brown fox---------The quick brown fox jumped over lazy dog-----over the lazy dog
red fox--------------------- Red Fox jumped over lazy dog----------------over lazy dog
Gray & Brown Fox ---------- Grey & Brown Fox over lazy dog --------------over lazy dog

[dilipandey]

Hi Manoj . . .

I have given what you asked in your original post.

I was not aware what you have in addition apart from what you had posted initially . . but anyways. . would suggest you to look into substitute function and make use of that . . thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[manoi]

Sorry for any miss-communication. Your response specifically addressed the first 2 words, it did not address the third word or rather the " 33" or " 22" in original post. I was under the assumption there is an easy command function that could match "All" contents of A to "Some" Contents" in B and delete the duplicates appearing in "B".

I do not know how to use the "substitute" function in to accomplish the task I am describing which is the reason for the post. I appreciate your response.

[dilipandey]

Hi Manoj,

Substitute function is explained below:-

SUBSTITUTE(text, old_text, new_text, [instance_num]) :-

Text Required. The text or the reference to a cell containing text for which you want to substitute characters.
Old_text Required. The text you want to replace.
New_text Required. The text you want to replace old_text with.
Instance_num Optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

In case you stuck somewhere, please post the sample workbook. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[manoi]

Attached please find a sample excel spread sheet. Possibly the sample will explain better what it is I am trying to accomplish. Please see "Note" at bottom of sample in spread sheet. There may be a better way to approach the resolution of my issue then the way I have described it in prior posts. Please advise.

[manoi]

If =A2&" "&B2 adds the contents together. What is the formula to subtract the contents of A2 from B2?

[manoi]

The formula to subtract contents of A2 from B2 is:
=trim(substitute(B2,A2,""))
----- but this will only subtract the contents of A2 from B2 if it there is an exact match. What is formula to subtract Partial Match of A1 From B1?

[dilipandey]

Hi Manoj,

For partial match... you can use Left OR Mid function..i.e.,
Left(a2,5) = left(b2,5).. will check a2 and b2 for 5 characters from left. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[manoi]

If I understand the formula you provided correctly, your solution is exact match of first 5 characters?

I am looking for a formula that will do a "partial match" of contents in cell A compared to Cell B.

If a=the quick brown fox (-) b=the quick fox has a lazy leg, (then) c=brown has a lazy leg.

Therefore, formula must do a partial match, not exact match, and not have specific number of characters.

Any ideas?