With with replacing part of a string with another string

[Ayo]

How do I right a code to replace the string between <href> </href> in the
tag below:
<href>http://maps.google.com/mapfiles/ms/icons/red-dot.png</href>

Because this part of the tag "red-dot.png" always varies. I am trying to
find a function, or a code, that will replace that part of the line everytime
I click a button and supply the new value

 

[Ron Rosenfeld]

How do I right a code to replace the string between <href> </href> in the
tag below:
<href>http://maps.google.com/mapfiles/ms/icons/red-dot.png</href>

Because this part of the tag "red-dot.png" always varies. I am trying to
find a function, or a code, that will replace that part of the line everytime
I click a button and supply the new value

Your specifications are inconsistent.

First you write:

"How do I right a code to replace the string between <href> </href>"

Then you write:

"red-dot.png" always varies. I am trying to find a function, or a code, that
will replace that part of the line"

I'm guessing you only want to replace that last part of the line.

But we still have to pick out the correct line.

A regular expression approach, similar to the one I recommended in your
previous post about "scale", and which you haven't yet clarified with regard to
Rick's questions, will work, but more information is required.

For example, is this the only <href> on the page? If not, how does it differ
from others?

A general regex pattern, such as

"(<href>[\s\S]+?/)[^/]+(?=</href>)"

along with a replacement string like

"$1replace_string"

could replace the last part of every URL that is on the page. So it would work
if there is only one, but if there are multiples, then you need to provide a
method of knowing which you want to replace.

So a general, regular expression UDF like:

============================
Option Explicit
Function RegSub(s As String, sPat As String, sRepl As String)
Dim Re As Object
Set Re = CreateObject("vbscript.regexp")
With Re
.Global = True
.ignorecase = True
.Pattern = sPat
.MultiLine = False
End With
RegSub = Re.Replace(s, sRepl)
End Function
==============================

with a formula like:

=RegSub(A1,"(<href>[\s\S]+?/)[^/]+(?=</href>)","$1" & A2)

and with

A2: green-dot.png


Would take

A1: <href>http://maps.google.com/mapfiles/ms/icons/red-dot.png</href>

and return:

<href>http://maps.google.com/mapfiles/ms/icons/green-dot.png</href>

And, as written, it would do that for every URL in A1.


--ron

 

[JLLeavitt]

This isn't difficult, there are a variety of methods.
I've used a combination of mid and instr to replace sql variables, here's an
UNTESTED function that illustrates the idea:

Public function replaceReference(sOriginalString as string) as string
Dim sReturnString as string
Dim nFirstTagPosition as long
Dim nLastTagPosition as long


nFirstTagPosition = InStr(1,sOriginalString,"<href>") + 6
'get the position the first tag starts and add 6 because <href> is six
characters long

nLastTagPosition = InStr(1,sOriginalString,"</href>" - 1
'get the position the last tag starts and subtract 1 getting char pos
before the </href> tag

sReturnString = mid(sOrignalString,1,nFirstTagPosition) & sNewReference &
mid(sOriginalString,nLastTagPosition)
'Construct the new string taking everything from up to the first tag,
concatenating the new text,
'then concatenating everything after the last reference position

replaceReference = sReturnString
end function

I just wrote this in notepad, and did not test it, however it gets the idea
across.