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>
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Jon von der Heyden

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,163
Messages
5,509,538
Members
408,739
Latest member
Forre_Learning Excel

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top