How to remove desired text or numbers from a cell

M

mohanprabhus

New Member
Joined
Dec 7, 2016
Messages
17
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 DataAlready Data PresentRequired Result
Shastri Market,Mohan CinemaMohan CinemaShastri Market
SCO.2 & 3, Sector-65/A, Phase-11, Near Punjab Mandi BoardSector-65/ASCO.2 & 3, Phase-11, Near Punjab Mandi Board
Birla Road, Near Vardhman Factory, Solan, Near AbbottBirla RoadNear Vardhman Factory, Solan, Near Abbott
LH Electronics, Near Gurudwara Guru Aanad Dev, Janakpuri Vilas Park, Near Street No -11Janakpuri Vilas ParkLH Electronics, Near Gurudwara Guru Aanad Dev, Near Street No -11

<tbody>
</tbody>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
assuming you have the first 2 columns of data. maybe

=SUBSTITUTE(SUBSTITUTE(A1,B1,""),", ,",",")
 
Upvote 0
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
ABC
1Raw DataAlready Data PresentRequired Result
2Shastri Market,Mohan CinemaMohan CinemaShastri Market
3SCO.2 & 3, Sector-65/A, Phase-11, Near Punjab Mandi BoardSector-65/ASCO.2 & 3, Phase-11, Near Punjab Mandi Board
4Birla Road, Near Vardhman Factory, Solan, Near AbbottBirla RoadNear Vardhman Factory, Solan, Near Abbott
5LH Electronics, Near Gurudwara Guru Aanad Dev, Janakpuri Vilas Park, Near Street No -11Janakpuri Vilas ParkLH Electronics, Near Gurudwara Guru Aanad Dev, Near Street No -11
Sheet2
 
Upvote 0
=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>
 
Upvote 0
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 DataAlready Data PresentRequired Result
BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,MettupalayamMettupalayamBPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road
BPCL Mettupalayam, 211 Karamadai RoadCnasps,Mettupalayam,
Coimbatore-Ooty Main Road,		MettupalayamBPCL 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 DataRequired Result 1Required Result 2
BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam 123456BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam123456
BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam, 123456BPCL Mettupalayam, 211 Karamadai RoadCnasps, Coimbatore-Ooty Main Road,Mettupalayam123456

<tbody>
</tbody>




mohanprabhus said:
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 DataAlready Data PresentRequired Result
Shastri Market,Mohan CinemaMohan CinemaShastri Market
SCO.2 & 3, Sector-65/A, Phase-11, Near Punjab Mandi BoardSector-65/ASCO.2 & 3, Phase-11, Near Punjab Mandi Board
Birla Road, Near Vardhman Factory, Solan, Near AbbottBirla RoadNear Vardhman Factory, Solan, Near Abbott
LH Electronics, Near Gurudwara Guru Aanad Dev, Janakpuri Vilas Park, Near Street No -11Janakpuri Vilas ParkLH Electronics, Near Gurudwara Guru Aanad Dev, Near Street No -11

<tbody>
</tbody>
Click to expand...
 
Upvote 0
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.

István Hirsch said:
Ref. post #5 second sample.

How do you know which Mettupalayam to remove?
Click to expand...
 
Upvote 0
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.
 
Upvote 0
HI István Hirsch,

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

István Hirsch said:
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.
Click to expand...
 
Upvote 0
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

Scott Huish said:
=SUBSTITUTE(TRIM(SUBSTITUTE(A2&",",B2&",","")),",","",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))

Sheet1

*ABC
1Raw DataAlready Data PresentRequired Result
2Shastri Market,Mohan CinemaMohan CinemaShastri Market
3SCO.2 & 3, Sector-65/A, Phase-11, Near Punjab Mandi BoardSector-65/ASCO.2 & 3, Phase-11, Near Punjab Mandi Board
4Birla Road, Near Vardhman Factory, Solan, Near AbbottBirla RoadNear Vardhman Factory, Solan, Near Abbott
5LH Electronics, Near Gurudwara Guru Aanad Dev, Janakpuri Vilas Park, Near Street No -11Janakpuri Vilas ParkLH 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>
</tbody>

Spreadsheet Formulas
CellFormula
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 >>
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,805
Messages
6,121,656
Members
449,045
Latest member
Marcus05

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top