Find & replace duplicate digits with in a cell

[Sultan786]

I would like to find specific digits in a cell and replace it with a symbol or some text like "duplicate", not the whole cell only the duplicate characters in the cell.
I have one column with more then 2500 rows which contains contact numbers. Every cell contains more then one number. then i have a another column in which i have the numbers that i want to be deleted from the first column.
I am placing the example so everybody can get an idea of what i want.


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Column 1 looking for duplicates in this one [/TD]
[TD]Column 2 Numbers to find from first column and replace with symbol[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]

<tbody>
[TD="class: xl65"]051-5506664,051-5518677,0331-5689456[/TD]

</tbody>

[/TD]
[TD]

0331-5689456

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 302"]
<tbody>[TR]
[TD="class: xl65, width: 302"],0301-6278688, 0300-6245388,0331-5689457[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

0331-5689457

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 302"]
<tbody>[TR]
[TD="class: xl65, width: 302"]0537610640,0333-8564866,0331-5689451[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

0331-5689458

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"],0344-5329610 / 0044-7864922101,0331-5689458[/TD]

</tbody>

[/TD]
[TD]

0331-5689459

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"]544-646789/345-5624363,0331-5689459[/TD]

</tbody>

[/TD]
[TD]

0331-5689450

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"],051-5826034,0331-5689450[/TD]

</tbody>

[/TD]
[TD]

0331-5689451

<tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

I am new to fourms, Could not find the attachment button to attach excel file, so made a table in the post. Help in this regard is highly appreciated. Thanks

 

[kweaver]

Is what you're looking for (column B) always the string after the last comma in column A?
And, what happens when the string in column B is not found in column A?

 

[kweaver]

Is it something like this?

Excel 2010
	A	B	C	D	E
1	ORIGINAL STRING	FIND	WHERE	REPL WITH	NEW STRING
2	051-5506664,051-5518677,0331-5689456	0331-5689456	24	AAA	051-5506664,051-5518677,AAA
3	,0301-6278688, 0300-6245388,0331-5689457	0331-5689457	28	BBB	,0301-6278688, 0300-6245388,BBB
4	0537610640,0333-8564866,0331-5689451	0331-5689458	#VALUE!	CCC	0537610640,0333-8564866,0331-5689451
5	,0344-5329610 / 0044-7864922101,0331-5689458	0331-5689459	#VALUE!	DDD	,0344-5329610 / 0044-7864922101,0331-5689458
6	0537610640,0333-8564866,0331-5689451	0333-8564866	11	EEE	0537610640,EEE,0331-5689451
Sheet4

Cell Formulas
Range	Formula
C2	=FIND(B2,A2)-1
E2	=IF(ISERROR(C2),A2,LEFT(A2,C2)&D2&RIGHT(A2,LEN(A2)-(C2+LEN(B2))))

 

[sandy666]

Post a link to shared excel file with your representative example of source and expected result. Use GoogleDrive, OneDrive, DropBox or any similar service.