Look for text in column and see if it is contained in different column same row , in same table

[Pandadev]

I have a list codes that are located in the same table , they are shown in two seperate columns.

I am trying to find a way to detect a match , when they are not exactly the same.

Example

CodeA       CodeB

000123     123

678A         001/678A

HV/0189   0189

These should all be classed as a match as

123 is contained in CodeA

678A is contained in CodeB

0189 is contained Code A

Is there a formula that can help with this please

[v-gizhi-msft · Accepted solution]

Hi,

Please try this column:

Column = 
IF (
    CONTAINSSTRING ( 'Table'[CodeB], 'Table'[CodeA] )
        || CONTAINSSTRING ( 'Table'[CodeA], 'Table'[CodeB] )
        || IF (
            CONTAINSSTRING ( 'Table'[CodeB], "-" ),
            CONTAINSSTRING (
                'Table'[CodeA],
                LEFT ( 'Table'[CodeB], FIND ( "-", 'Table'[CodeB], 1, 1 ) - 1 )
            )
                && CONTAINSSTRING (
                    'Table'[CodeA],
                    RIGHT (
                        'Table'[CodeB],
                        LEN ( 'Table'[CodeB] ) - FIND ( "-", 'Table'[CodeB], 1, 1 )
                    )
                )
        ),
    "Match",
    "No Match"
)

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[amitchandak]

@Pandadev , Create a new column like

IF(SEARCH([CodeA],[CodeB],,0) >0 , [CodeA] & " is contained in CodeB",
if( SEARCH([CodeB],[CodeA],,0) >0 ,[CodeB] & " is contained in CodeA" , "No Match"))

[Greg_Deckler]

Sure, use SEARCH or FIND. Column would be like:

Column =
  IF(SEARCH([CodeA],[CodeB],,-1) <> -1 || SEARCH([CodeB],[CodeA],,-1) <> -1,"match","no match")

[Pandadev]

Thanks , that is nearly perfect.

Just a couple of codes that dont work with this method

TH1499 and TH-1499

TE 1191 AND TE-1191

which is caused by spaces , and ,/-

is there an easy way to remove these