HELP! Delete / replace characters using macro

[myfightingirishDOTnet]

I need a macro that replaces all spaces with underscores AND deletes all parenthasis and dashes.

For example,
<TABLE style="WIDTH: 372pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=496 border=0><COLGROUP><COL style="WIDTH: 372pt; mso-width-source: userset; mso-width-alt: 18139" width=496><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #aca899 1pt solid; BORDER-LEFT: #aca899 1pt solid; WIDTH: 372pt; BORDER-BOTTOM: #aca899 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #f2f4ec" width=496 height=21>Account Agreement - CFD & Spread Trade (AUS FX).doc</TD></TR></TBODY></TABLE>
needs to be changed to:
<TABLE style="WIDTH: 372pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=496 border=0><COLGROUP><COL style="WIDTH: 372pt; mso-width-source: userset; mso-width-alt: 18139" width=496><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #aca899 1pt solid; BORDER-LEFT: #aca899 1pt solid; WIDTH: 372pt; BORDER-BOTTOM: #aca899 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #f2f4ec" width=496 height=21>Account_Agreement_CFD_&_Spread_Trade_AUS FX.doc</TD></TR></TBODY></TABLE>


This macro will applied to one column.

Help anyone?

 

[jbeaucaire]

Turn on the macro recorder and let it record you using the CTRL-H (search/replace) tool. The resulting macro can be reused, usually.

 

[doofusboy]

Range(your_cell_address_here).Value = Replace(Replace(Replace(Replace(Range(your_cell_address_here).Value," ","_"),"-",""),"(",""),")","")

 

[myfightingirishDOTnet]

Thanks for the help guys! I'm all set!

 

[passinthru]

Posted for the sake of future readers....

Turn on the macro recorder and let it record you using the CTRL-H (search/replace) tool. The resulting macro can be reused, usually.

The problem with this approach is that Excel can search & replace (S&R) on the current worksheet, OR all worksheets in a workbook. This is a setable parameter under the "Advanced" settings on the S&R dialog. By default, the search is only on the current worksheet.

HOWEVER! Excel remembers the last setting used, and applies that to the current search, unless it is manually changed. That seems sensible, but if you forget, it can cause problems.

Now to the HUGE PROBLEM with the recorded code: This setting is not part of the recorded code. There is no way in a macro to specify which setting to use. That bit is stored somewhere within the system, and is (apparently) not accessible to the macro process. (If there's a way to do this, PLEASE correct me!)

So, when your intrepid macro coder wants to run S&R on a single worksheet, well, he'd better hope that was the last setting used. If the last setting was workBOOK, he's just applied that S&R to all worksheets in the workbook. Conversely, if he wants it for the whole workbook, but the last setting was workSHEET, he only gets the S&R on the active sheet.

NOW, your intrepid coder's macro proceeds on the assumption that the S&R was applied to the correct worksheets, but it was NOT.

Hilarity ensues.





doofusboy's approach looks to me like a much more reliable method.


However, I'm having trouble withg the syntax. I'll start a new thread for that.

 

[passinthru]

A good workaround for the S&R problem was posted by shg4421.

http://www.mrexcel.com/forum/showthread.php?p=2651861#post2651861


I've tested this in my own code and it works. I find it a bit cleaner than the nested replace suggested above.

Okay, maybe that just means I'm easily confused!