How to remove desired text or numbers from a cell

[mohanprabhus]

HI All,

Can any help me with below format in excel formula,Please find the below mentioned details i need to remove text or numbers from a cell

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Raw Data[/TD]
[TD]Already Data Present[/TD]
[TD]Required Result[/TD]
[/TR]
[TR]
[TD]Shastri Market,Mohan Cinema[/TD]
[TD]Mohan Cinema[/TD]
[TD]Shastri Market[/TD]
[/TR]
[TR]
[TD]SCO.2 & 3, Sector-65/A, Phase-11, Near Punjab Mandi Board[/TD]
[TD]Sector-65/A[/TD]
[TD]SCO.2 & 3, Phase-11, Near Punjab Mandi Board[/TD]
[/TR]
[TR]
[TD]Birla Road, Near Vardhman Factory, Solan, Near Abbott[/TD]
[TD]Birla Road[/TD]
[TD]Near Vardhman Factory, Solan, Near Abbott[/TD]
[/TR]
[TR]
[TD]LH Electronics, Near Gurudwara Guru Aanad Dev, Janakpuri Vilas Park, Near Street No -11[/TD]
[TD]Janakpuri Vilas Park[/TD]
[TD]LH Electronics, Near Gurudwara Guru Aanad Dev, Near Street No -11[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Hi All,

Thank you for the solutions, but i have problem, If i have two word in sentence it removes both, But i want to remove exact match word only.




[TABLE="width: 500"]
<tbody>[TR]
[TD]Raw Data[/TD]
[TD]Already Data Present[/TD]
[TD]Required Result[/TD]
[/TR]
[TR]
[TD]BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam[/TD]
[TD]Mettupalayam[/TD]
[TD]BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road[/TD]
[/TR]
[TR]
[TD]BPCL Mettupalayam, 211 Karamadai RoadCnasps,Mettupalayam,
Coimbatore-Ooty Main Road,[/TD]
[TD]Mettupalayam[/TD]
[TD]BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road[/TD]
[/TR]
</tbody>[/TABLE]

The below mentioned data i need to extract numbers (exact 6 digits or i can choose how many numbers like 10 digit phone number) with comma & without comma separated, these numbers can in in b/w also.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Raw Data[/TD]
[TD]Required Result 1[/TD]
[TD]Required Result 2[/TD]
[/TR]
[TR]
[TD]BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam 123456[/TD]
[TD]BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam[/TD]
[TD]123456[/TD]
[/TR]
[TR]
[TD]BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam, 123456[/TD]
[TD]BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam[/TD]
[TD]123456[/TD]
[/TR]
</tbody>[/TABLE]

Upper exhibit, Sheet1...

In C2 enter and copy down:

=REPLACE(SUBSTITUTE(","&SUBSTITUTE(A2,", ",",")&",",","&B2&",",""),1,1,"")

Lower exhibit, Sheet2...

In c2 enter and copy down:

=REPLACE(SUBSTITUTE(A2,B2,""),1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A2,B2,"")&"0123456789"))-1,"")+0