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

Raw Data	Already Data Present	Required Result
Shastri Market,Mohan Cinema	Mohan Cinema	Shastri Market
SCO.2 & 3, Sector-65/A, Phase-11, Near Punjab Mandi Board	Sector-65/A	SCO.2 & 3, Phase-11, Near Punjab Mandi Board
Birla Road, Near Vardhman Factory, Solan, Near Abbott	Birla Road	Near Vardhman Factory, Solan, Near Abbott
LH Electronics, Near Gurudwara Guru Aanad Dev, Janakpuri Vilas Park, Near Street No -11	Janakpuri Vilas Park	LH Electronics, Near Gurudwara Guru Aanad Dev, Near Street No -11

<tbody>
</tbody>

 

[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.

Raw Data	Already Data Present	Required Result
BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam	Mettupalayam	BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road
BPCL Mettupalayam, 211 Karamadai RoadCnasps,Mettupalayam, Coimbatore-Ooty Main Road,	Mettupalayam	BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road

<tbody>
</tbody>

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.

Raw Data	Required Result 1	Required Result 2
BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam 123456	BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam	123456
BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam, 123456	BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam	123456

<tbody>
</tbody>

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