Find and replace multiple dash's (---)

p_grad

New Member
Joined
May 5, 2010
Messages
41
I cannot seem to use the find and replace function to remove unwanted dashs's.

Eg

<TABLE style="WIDTH: 230pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=306 border=0><COLGROUP><COL style="WIDTH: 230pt; mso-width-source: userset; mso-width-alt: 11190" width=306><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 230pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=306 height=20>ADAMS-Matthew-469312-</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ADAMS-Michele-360221--</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ADAMS-Nathan-265675---

Without going into to much detail I am stuck with betwen one and four unwanted dashs at the end of the info I want to keep. I have 5000 lines which I want to remove the dsh from but using find --- and replace " " doesnt seem to work.

Any Ideas?

Cheers
Paul
</TD></TR></TBODY></TABLE>
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hi Paul,

Perhaps;
Excel Workbook
AB
1ADAMS-Matthew-469312-ADAMS-Matthew-469312
2ADAMS-Michele-360221--ADAMS-Michele-360221
3ADAMS-Nathan-265675---ADAMS-Nathan-265675
Sheet1
Excel 2007
Cell Formulas
RangeFormula
B1=LEFT(A1,LOOKUP(-1,-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1)))))
 
Upvote 0
Simpler:
Excel Workbook
AB
1ADAMS-Matthew-469312-ADAMS-Matthew-469312
2ADAMS-Michele-360221--ADAMS-Michele-360221
3ADAMS-Nathan-265675---ADAMS-Nathan-265675
Sheet1
Excel 2007
Cell Formulas
RangeFormula
B1=LEFT(A1,FIND("--",A1&"--")-1)
 
Upvote 0
Perfect,

Could I be really cheeky and ask what the formula is actually doing? If I understand it I will be less likely to ask a similar question again.

I'm not really sure what the 'left' and '-1' bits are doing.

Cheers
 
Upvote 0
Hi again

Let me explain by showing you the individual steps of the formula:
Excel Workbook
ABCDE
1Original TextAdd "--" to end of textFind the position of "--"Length of text wantedResult
2ADAMS-Matthew-469312-ADAMS-Matthew-469312---2120ADAMS-Matthew-469312
3ADAMS-Michele-360221--ADAMS-Michele-360221----2120ADAMS-Michele-360221
4ADAMS-Nathan-265675---ADAMS-Nathan-265675-----2019ADAMS-Nathan-265675
Sheet1
Excel 2007
Cell Formulas
RangeFormula
B2=A2&"--"
C2=FIND("--",B2)
D2=C2-1
E2=LEFT(A2,D2)
 
Upvote 0
Jon, that is a really clever and elegant solution. Wow.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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