I need remove the entire value starting with matched value in other column, let me know for further questions. TIA Col1 Col2 EXPECTED XYNTHA, XPOVIO XYNTHA SOLOFUSE XPOVIO FLUTHOX DR, NATESTO, VOGELXO FLUTHOX HCL NATESTO,VOGELXO BUPROPION HCL, BUPROPION HCL SR BUPROPION XL SULFACETA SODIUM SODIUMSULFACETA

Hi @yeruvab , The 4th row makes the issue harder as it is not clear which "word" should be checked. So I relaxed the problem to deal with only the words separated by space, instead of any possible length of the string in Col2. I hope this helps. Input Col1Col2XYNTHA, XPOVIOXYNTHA SOLOFUSEFLUTHOX DR, NATESTO, VOGELXOFLUTHOX HCLBUPROPION HCL, BUPROPION HCL SRBUPROPION XLSULFACETA SODIUMSODIUM SULFACETA Output RecordIDCol1Col2Expected1XYNTHA, XPOVIOXYNTHA SOLOFUSEXPOVIO2FLUTHOX DR, NATESTO, VOGELXOFLUTHOX HCLNATESTO,VOGELXO3BUPROPION HCL, BUPROPION HCL SRBUPROPION XL 4SULFACETA SODIUMSODIUM SULFACETA Workflow

Find string value from col1 and remove that value from col2

yeruvab

I need remove the entire value starting with matched value in other column, let me know for further questions. TIA

Col1	Col2	EXPECTED
XYNTHA, XPOVIO	XYNTHA SOLOFUSE	XPOVIO
FLUTHOX DR, NATESTO, VOGELXO	FLUTHOX HCL	NATESTO,VOGELXO
BUPROPION HCL, BUPROPION HCL SR	BUPROPION XL
SULFACETA SODIUM	SODIUMSULFACETA

Data Investigation

Accepted answers

Yoshiro_Fujimori

Hi @yeruvab ,

The 4th row makes the issue harder as it is not clear which "word" should be checked.

So I relaxed the problem to deal with only the words separated by space, instead of any possible length of the string in Col2.

I hope this helps.

Input

Col1	Col2
XYNTHA, XPOVIO	XYNTHA SOLOFUSE
FLUTHOX DR, NATESTO, VOGELXO	FLUTHOX HCL
BUPROPION HCL, BUPROPION HCL SR	BUPROPION XL
SULFACETA SODIUM	SODIUM SULFACETA

Output

RecordID	Col1	Col2	Expected
1	XYNTHA, XPOVIO	XYNTHA SOLOFUSE	XPOVIO
2	FLUTHOX DR, NATESTO, VOGELXO	FLUTHOX HCL	NATESTO,VOGELXO
3	BUPROPION HCL, BUPROPION HCL SR	BUPROPION XL
4	SULFACETA SODIUM	SODIUM SULFACETA

Workflow

1280539.yxmd

All comments

Garrett_Stoker

I probably made this a little harder than it needed to be, but assuming you have a finite list of connector/trailing words, you could make something like this.

String Matching.yxmd

Yoshiro_Fujimori

Hi @yeruvab ,

The 4th row makes the issue harder as it is not clear which "word" should be checked.

So I relaxed the problem to deal with only the words separated by space, instead of any possible length of the string in Col2.

I hope this helps.

Input

Col1	Col2
XYNTHA, XPOVIO	XYNTHA SOLOFUSE
FLUTHOX DR, NATESTO, VOGELXO	FLUTHOX HCL
BUPROPION HCL, BUPROPION HCL SR	BUPROPION XL
SULFACETA SODIUM	SODIUM SULFACETA

Output

RecordID	Col1	Col2	Expected
1	XYNTHA, XPOVIO	XYNTHA SOLOFUSE	XPOVIO
2	FLUTHOX DR, NATESTO, VOGELXO	FLUTHOX HCL	NATESTO,VOGELXO
3	BUPROPION HCL, BUPROPION HCL SR	BUPROPION XL
4	SULFACETA SODIUM	SODIUM SULFACETA

Workflow

1280539.yxmd

terry10

@yeruvab

You might want to look into Fuzzy Matching!

Tool Mastery | Fuzzy Matching

yeruvab

Thank you for your answers, helped me understand how to solve such tricky scenarios.

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157