Count of replacements when using Replace function 1

[phudgens]

Is there a way to know whether or not any replacements were made when using the following line of code in VBA for Excel 2003?

LineString = Replace(LineString, "~", " ")

I sometimes need to know if any replacements were actually made. Nothing in the online help indicates a way to know one way or the other.

Thanks for the help,
Paul Hudgens

 

[PHV]

i = Len(LineString)
LineString = Replace(LineString, "~", " ")
CountOfReplacements = i - Len(LineString)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

1. in PHV code CountOfReplacements will always = 0, as the Replaced character is same length as the one replaced.

~ = 1
" " = 1

I was not sure if the replacement string was indeed a space, as IE still does funny things. However Compatability mode (either way) gives me a space.

2. It does not give the number of replacements. It gives the number of replaced characters.

Sub CountReplace()
Dim LineString As String
Dim CountOfReplacements As String
Dim i As Long

LineString = "This has ~ four instances ~ of a ~ tilde ~ mark"
i = Len(LineString)
LineString = Replace(LineString, "instances", " ")
MsgBox i - Len(LineString)

End Sub

returns 8.

9 characters - "instances" was replaced by 1 character = 8

But only ONE replacement was done.

So, yes, you can determine if a replacement happened (the Len count will be different), but it is not a countOfReplacement. It is the total count of replaced characters.

A replacement count (as in the number of replacements) can be done easily using variables.

Sub CountNumOfReplacements()
Dim strIn As String
Dim SearchFor As String
Dim ReplaceWith As String
Dim i As Long

SearchFor = "instances"
ReplaceWith = " "

strIn = "This has two instances of instances"
i = Len(strIn)
strIn = Replace(strIn, SearchFor, ReplaceWith)
MsgBox (i - Len(strIn)) / (Len(SearchFor) - Len(ReplaceWith))

End Sub

Returns 2, the number of replacements made.

Gerry

 

[strongm]

That might have a slight problem if SearchFor and ReplaceWith are the same length ...

 

[PHV]

OOps, didn't realize that the replacement character was a space.

CountOfReplacements = UBound(Split(LineString, "~"))
LineString = Replace(LineString, "~", " ")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TonyJollans]

The question asked whether there was a way to know if any - not how many - replacements were made. The answer, in either case, is that the Replace itself won't tell you so you need some extra code.

If that is really all that is needed, one way might be to start the replacement at the first occurrence ...

LineString = Replace(LineString, "~", " ", InStr(LineString, "~"))

This will give an error if the character to be replaced does not exist in the string, and you could trap the error ...

On Error Resume Next
LineString = Replace(LineString, "~", " ", InStr(LineString, "~"))
MsgBox "Replacements were " & IIf(Err.Number = 0, "", "NOT ") & "made."
On Error GoTo 0

This probably isn't as good, or as obvious, as PHV's approach, but I just thought I'd throw it into the mix for fun.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[strongm]

>I sometimes need to know if any replacements were actually made

If NewString = OldString

might be sufficient

 

[TonyJollans]

Quite so. Why does the obvious escape us so often?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[phudgens]

Thanks to everyone for your replies. I had considered comparing the length of the line before and after the replacement and realized that it didn't matter in this particular case whether or not the tilde was replaced with a space, or simply removed. So that approach did work.
PHV's approach using:

CountOfReplacements = UBound(Split(LineString, "~"))

works also. Since it's only one line and easily testable, I'm now using it in my code. Thanks very much.

 

[fumei]

That might have a slight problem if SearchFor and ReplaceWith are the same length ...

Indeed. And you could also end up with a negative number, if ReplaceWith is longer!!

Gerry