Search content in another cell as word, not sentence.

tokyoboy

New Member
Joined
Jun 12, 2019
Messages
2
Hi,

I am working on formula to help my staff on their data entry.

We have the primary code in column A and we need to input color of each item in column B
In column C, we then have this formula, =IF(A2<>A1,B2,IF(ISNUMBER(SEARCH(B2,C1)),C1,C1&","&B2))

it basically check if all these 3 input is in the same group by checking if they have the same primary key. If yes, then it will check if the cell above (in column C) already have the same data as the one in the same row in column B. So at the last line, the formula see that the cell above do not yet have "grey" which is the in column B, then the formula combine this into the cell and become "Dark Black, Grey"

However, I actually need the result at the last line like this > "Dark Black, Black, Grey". I realize it actually because the "black" is in the sentence "Dark black" that why I could get them into the "Dark Black, Black, Grey"

Is there anyway I can make formula to work so that Excel can include "Black" in row2 into the sentence, even-though we have this "Black" word in "Dark Black" ?


ABCD
1Dark BlackDark BlackDark Black,Grey
1BlackDark BlackDark Black,Grey
1GreyDark Black,GreyDark Black,Grey

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


Thank you very much
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi
Welcome to the board

Include the separator in the search

Instead of

SEARCH(B2,C1)

try

SEARCH(","&B2&",",","&C1&",")
 
Upvote 0
HI pgc01,

Thank you very much ! It works !
Have been trying several days to look for solution but really couldn't

Thank for your help
pgc01 !

Best Regards,
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top