Need help with replacing text

[imerictoo]

Hello All,

Need help with replacing text as I have tried some formulas as well as a simple find & replace.
I have a string within a cell:
abc\Joe,abc\Mary,abc\harry,abc\,abc\,abc\

I want to remove the ,abc\,abc\,abc\

Any ideas?

Thanks,
Eric

 

[JoeMo]

Is this what you want or is it the next one?
Sheet1

	A	B
abc\Joe,abc\Mary,abc\harry,abc\,abc\,abc\	JoeMaryharry

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:280px;"><col style="width:92px;"></colgroup>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

Spreadsheet Formulas

Cell Formula B1 =SUBSTITUTE(SUBSTITUTE(A1,",abc",""),"abc","")

Excel tables to the web >> Excel Jeanie HTML 4

Excel Workbook
	A	B
1	abc\Joe,abc\Mary,abc\harry,abc\,abc\,abc\	abc\Joe,abc\Mary,abc\harry
Sheet1

 

[jproffer]

Maybe something like this:

Excel 2013/2016

	A	B
abc\Joe,abc\Mary,abc\harry,abc\,abc\,abc\	\Joe\Mary\harry\

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"abc",""),",",""),"",""),"","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The most inner iteration of the Substitute function is just in case you end up with 3 ""s together. If you'll have more than that, we may have to go a different route.

This is the actual formula I put in...it's not posting it right for some reason.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"abc",""),",",""),"\\",""),"\\\","")

 

[Scott Huish]

This assumes you have no spaces in the data you want to keep as in your example:

=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1,",abc",""),"\"," "))," ","\")

If there are spaces like Joe Smith for example, then try this:

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","~"),",abc",""),"\"," "))," ","\"),"~"," ")