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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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)))))
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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)
 

p_grad

New Member
Joined
May 5, 2010
Messages
41
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
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,875
Messages
5,655,756
Members
418,234
Latest member
jdorfma

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
Top