Share via

Search for a specific string in a cell and assign the result in another cell based on priority

Anonymous
2014-06-18T02:19:58+00:00

Hello,

I need some help to search for a specific text in a string from a column of cells.If the text is found I want to assign 1 to the corresponding cells of another column, 0 if not found. Moreover, the assigning has to take into consideration the priority of text.

For eg,

Priority of text is : HH > IA > Ag > M8 > Ex

The expected result is as follows,

Test_data HH IA Ag M8 Ex
HHM8 1 0 0 0 0
ExAg 0 1 0 0 0
0 0 0 0 0
Ex 0 0 0 0 1
M8Ex 0 0 0 1 0

Excel version: Excel 2010

Operating System: Windows 7

Assuming "Test_Data" is in A1, I used

=IF(ISNUMBER(FIND($B$1,$A2,1)),1,0)

And this in C2

=IF(SUM($B2:B2)>0,0,IF(ISNUMBER(FIND(C$1,$A2,1)),1,0))

However it returns 1 in the corresponding cell by which the string starts.

Meaning, it will give the result as table no1(which is not expected since Ag has a higher priority than Ex), the expected result is displayed in table no2: 

Table1:

Test_data Ex Ag
ExAg 1 0
Table2:
Test_data Ex Ag
ExAg 0 1

Please help

Thanks in advance

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-06-18T16:46:19+00:00

    Correct, that is a typo and it should be in 1 cell to the right.

    0 comments
  2. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2014-06-18T14:32:16+00:00

    In case, you are looking at this result

    Formulas will be

    B2: =IFERROR(IF(SEARCH(B$1,$A2)>0,1,0),0)

    C2: =IFERROR(IF(B2=1,0,IF(SEARCH(C$1,$A2)>0,1,0)),0)

    D2: =IFERROR(IF(OR(B2=1,C2=1),0,IF(SEARCH(D$1,$A2)>0,1,0)),0)

    E2: =IFERROR(IF(OR(B2=1,C2=1,D2=1),0,IF(SEARCH(E$1,$A2)>0,1,0)),0)

    For more columns, include conditions accordingly, And columns should be in decreasing order of significance i.e. in above case - HH>Ag>Ey>M7

    0 comments
  3. Anonymous
    2014-06-18T14:31:00+00:00

    The expected result is as follows,

    Test_data HH IA Ag M8 Ex
    HHM8 1 0 0 0 0
    ExAg 0 1 0 0 0
    0 0 0 0 0
    Ex 0 0 0 0 1
    M8Ex 0 0 0 1 0

    Hi,

    Not sure I agree with your expected result as I've emboldened above (shouldn't this be in the column to the right?) but try this in B2 and copy across and down:

    =0+(LOOKUP(2^15,FIND({"","Ex","M8","Ag","IA","HH"},$A2),{"","Ex","M8","Ag","IA","HH"})=B$1)

    Regards

    0 comments
  4. Anonymous
    2014-06-18T13:47:10+00:00

    Apparently there seems to be a bug in the formula, it is assigning random priority and assigning a 1 even if no string is present in that cell.

    0 comments
  5. Anonymous
    2014-06-18T04:34:35+00:00

    Give this single formula at B2 and drag it right and down as per screen shot.

    =(LOOKUP(TRUE,ISNUMBER(SEARCH({"HH","IA","Ag","M8","Ex"},$A2)),{"HH","IA","Ag","M8","Ex"})=B$1)*1

    0 comments