Delete Duplicate Between Two Columns - Criteria from Right

[Blessy Clara]

Hello - Please help me delete Data between Columns

I have two Columns of Data

I wanna delete the Data (repetitive between Columns)
To Note - the term may occur mutliple times in address as in Example Number 4, 7 - Search from Right and delete Common data occurring first from right

ID	Address	City	Result
1	Po Box 197 Bassendean <tbody> </tbody>	BASSENDEAN <tbody> </tbody>	Po Box 197
2	15 Storey St Oak Flats <tbody> </tbody>	Oak Flats	15 Storey St
3	232 Brokers Rd Mount Pleasant <tbody> </tbody>	Mount Pleasant	232 Brokers Rd
4	494 - 496 Earlville Rd Earlville <tbody> </tbody>	Earlville	494 - 496 Earlville Rd
5	622 Wickham Street Fortitude Valley <tbody> </tbody>	Fortitude Valley	622 Wickham Street <tbody> </tbody>
6	1st Floor Burswood House 90 Burswood Road <tbody> </tbody> <tbody> </tbody>	Burswood	1st Floor Burswood House 90 Burswood Road (Nothing to delete here)
7	Adelaide Centre Ground Floor, 81 Flinders Street Adelaide <tbody> </tbody>	Adelaide	Adelaide Centre Ground Floor, 81 Flinders Street <tbody> </tbody>

<tbody>
</tbody>

Thank you for your time and efforts-

 

[Marcelo Branco]

Maybe this...

D2 copied copied down
=IF(ISNUMBER(SEARCH(C2&"|",B2&"|")),SUBSTITUTE(PROPER(B2),PROPER(C2),"",(LEN(B2)-LEN(SUBSTITUTE(PROPER(B2),PROPER(C2),"")))/LEN(C2)),B2)

Hope this helps

M.

 

[Blessy Clara]

Hi Marcelo Branco

Thank you very very much for that formula, Yes it worked super cool :D

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.