VBA Replace using wildcard?

  • Thread starter Post Tenebras Lux
  • Start date
P

Post Tenebras Lux

I have a very long string (an HTML document).

I want to replace all instances of a substring for which I know the first
few characters and the end character, but the middle part of the substring
can vary in both length and additional characters.

For example, if I want to replace each of the following using the Replace
function and wildcards:
<td class="dkbluelt"> with <td>
<td valign="top" bgcolor=#99cbe5" align="left"> with <td>

I have built a for loop using Instr and Mid functions, but it takes a long
time to run.

Is there any way to use the replace function in VBA using wildcards? for
example:
Result = Replace(MyLongString, "<td*>", "<td>")

Thanks for any suggestions.
 
D

Dave Peterson

Try this (record a macro just in case it works!)

Select the range to fix
edit|replace
what: <td *> (I included a space character--just to be more specific)
with: <td>
replace all

Did it do what you want?

If yes, keep the macro. If no, then Edit|Undo and toss the macro.
 
P

Post Tenebras Lux

Nice idea, but unfortunately, your idea (using worksheet function as recorded
by Excel macro recorder) only works with the range objects, not string
variables. As the string is too large for a cell (without writing code to
break it up into smaller cells), I can't put it in a range first.

Fortunately, I found that there is a way to do it using RegExp (Regular
Expressions) - not easy to learn about, as the pieces of info you need are
all over the place and not (for me) easily assembled into workable code, but
Tushar Mehta has some examples on his website that pointed me in the right
direction.

Of course, if someone else has a better idea, I'd love to hear about it.
 
D

Dave Peterson

Ah. I thought you were modifying data in a worksheet.

I like your idea of regular expressions--even though I don't use them.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top