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.

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.


Post Tenebras Lux wrote:

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.

--

Dave Peterson

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.




"Dave Peterson" wrote:

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.


Post Tenebras Lux wrote:

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.

--

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.

Post Tenebras Lux wrote:

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.

"Dave Peterson" wrote:

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.


Post Tenebras Lux wrote:

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.

--

Dave Peterson


--

Dave Peterson