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]

 

[BarryL]

assuming you have the first 2 columns of data. maybe

=SUBSTITUTE(SUBSTITUTE(A1,B1,""),", ,",",")

 

[AlKey]

Enter formula in C2 and Copy down

=TRIM(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,B2&",","")),",",REPT(" ",99),LEN(A2)-LEN(SUBSTITUTE(A2,",",""))),99))

Excel 2016 (Windows) 32 bit
	A	B	C
1	Raw Data	Already Data Present	Required Result
2	Shastri Market,Mohan Cinema	Mohan Cinema	Shastri Market
3	SCO.2 & 3, Sector-65/A, Phase-11, Near Punjab Mandi Board	Sector-65/A	SCO.2 & 3, Phase-11, Near Punjab Mandi Board
4	Birla Road, Near Vardhman Factory, Solan, Near Abbott	Birla Road	Near Vardhman Factory, Solan, Near Abbott
5	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
Sheet2

 

[Scott Huish]

=SUBSTITUTE(TRIM(SUBSTITUTE(A2&",",B2&",","")),",","",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:196px;" /><col style="width:179px;" /><col style="width:245px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Raw Data</td><td >Already Data Present</td><td >Required Result</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Shastri Market,Mohan Cinema</td><td >Mohan Cinema</td><td >Shastri Market</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><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 style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Birla Road, Near Vardhman Factory, Solan, Near Abbott</td><td >Birla Road</td><td >Near Vardhman Factory, Solan, Near Abbott</td></tr><tr style="height:73px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><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></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=SUBSTITUTE(TRIM<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A2&",",B2&",","")</span>)</span>,",","",LEN<span style=' color:008000; '>(A2)</span>-LEN<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A2,",","")</span>)</span>)</td></tr><tr><td >C3</td><td >=SUBSTITUTE(TRIM<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A3&",",B3&",","")</span>)</span>,",","",LEN<span style=' color:008000; '>(A3)</span>-LEN<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A3,",","")</span>)</span>)</td></tr><tr><td >C4</td><td >=SUBSTITUTE(TRIM<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A4&",",B4&",","")</span>)</span>,",","",LEN<span style=' color:008000; '>(A4)</span>-LEN<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A4,",","")</span>)</span>)</td></tr><tr><td >C5</td><td >=SUBSTITUTE(TRIM<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A5&",",B5&",","")</span>)</span>,",","",LEN<span style=' color:008000; '>(A5)</span>-LEN<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A5,",","")</span>)</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[mohanprabhus]

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]

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]

 

[István Hirsch]

Ref. post #5 second sample.

How do you know which Mettupalayam to remove?

 

[mohanprabhus]

Hi István Hirsch,

I wanted to remove the exact match word, because in the first word it is with BPCL Mettupalayam, in the last it is only Mettupalayam, according to that i need to remove i have many rows like this, Please if you would helpme that would be greater help for me.

Ref. post #5 second sample.

How do you know which Mettupalayam to remove?

 

[István Hirsch]

For Excel each Mettupalayam seems the same. Therefore you should set up rules which one the remove. For example, if Mettupalayam is between two commas, or one space and one comma or if it is at the end of the string etc.

 

[mohanprabhus]

HI István Hirsch,

Can you at least helpme to extract the word after comma of a sentence.

For Excel each Mettupalayam seems the same. Therefore you should set up rules which one the remove. For example, if Mettupalayam is between two commas, or one space and one comma or if it is at the end of the string etc.

 

[mohanprabhus]

Hi Scott Huish,


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. example are in below thread post

=SUBSTITUTE(TRIM(SUBSTITUTE(A2&",",B2&",","")),",","",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))

Sheet1

*	A	B	C
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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:196px;"><col style="width:179px;"><col style="width:245px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =SUBSTITUTE(TRIM(SUBSTITUTE(A2&",",B2&",","")),",","",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))) C3 =SUBSTITUTE(TRIM(SUBSTITUTE(A3&",",B3&",","")),",","",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))) C4 =SUBSTITUTE(TRIM(SUBSTITUTE(A4&",",B4&",","")),",","",LEN(A4)-LEN(SUBSTITUTE(A4,",",""))) C5 =SUBSTITUTE(TRIM(SUBSTITUTE(A5&",",B5&",","")),",","",LEN(A5)-LEN(SUBSTITUTE(A5,",",""))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >>