Help with Replace()

E

EagleOne

2003, 2007


PER Excel VBA Help: (I guess you knew that!)

Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing sub string to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is to begin. If
omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If omitted, the
default value is 1, which means make all possible substitutions.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating
substrings. See Settings section for values.

That said, I "assume" that Start is "Position within expression where substring search is to begin."

Facts:

StrToSearch = 'Min. Int.'!D18-362
StrToFind = "-362"

All is OK with:
Replace(StrToSearch, StrToFind, "",1,1,vbTextCompare)

What I want is to assure that the -362 is in the 16th start position - to be more accurate.

But if I specify Replace(StrToSearch, StrToFind, "",16,1,vbTextCompare) then
I get an empty string

What am I doing wrong?

Further, I know that the string I wish to delete from StrToSearch is Mid(StrToSearch,16,4)
How can I delete/Replace that exact string "-362" from its exact position in StrToSearch?

Any Thoughts appreciated,

Thanks, EagleOne
 
R

Rick Rothstein

What I want is to assure that the -362 is in the 16th start position

If you are simply trying to insure that the -362, then you are trying to use
the wrong function to do it; use the InStr function instead...

If InStr(StrToSearch, StrToFind) = 16 Then

The InStr function has some optional arguments (specific to if you want to
start searching from a position other than 1 and/or if you want to do case
insensitive searches.
 
E

EagleOne

Hi Pat,

StrToSearch = "'Min. Int.'!D18-362"

Forgot, only for the question, the " "

The actual Variable StrToSearch was valued with the " "

Patrick Molloy said:
what does

StrToSearch = 'Min. Int.'!D18-362

actually have as its value?


2003, 2007


PER Excel VBA Help: (I guess you knew that!)

Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing sub string to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is to
begin. If
omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If omitted,
the
default value is 1, which means make all possible substitutions.
compare Optional. Numeric value indicating the kind of comparison to use
when evaluating
substrings. See Settings section for values.

That said, I "assume" that Start is "Position within expression where
substring search is to begin."

Facts:

StrToSearch = 'Min. Int.'!D18-362
StrToFind = "-362"

All is OK with:
Replace(StrToSearch, StrToFind, "",1,1,vbTextCompare)

What I want is to assure that the -362 is in the 16th start position - to
be more accurate.

But if I specify Replace(StrToSearch, StrToFind, "",16,1,vbTextCompare)
then
I get an empty string

What am I doing wrong?

Further, I know that the string I wish to delete from StrToSearch is
Mid(StrToSearch,16,4)
How can I delete/Replace that exact string "-362" from its exact position
in StrToSearch?

Any Thoughts appreciated,

Thanks, EagleOne
Click to expand...
Click to expand...
 
J

Jim Cone

XL 97 vba help...
expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte)
XL2003 vba help...
expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)

"expression" is a Range object.
--
Jim Cone
Portland, Oregon USA




<[email protected]>
wrote in message
2003, 2007

PER Excel VBA Help: (I guess you knew that!)

Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing sub string to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is to begin. If
omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If omitted, the
default value is 1, which means make all possible substitutions.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating
substrings. See Settings section for values.

That said, I "assume" that Start is "Position within expression where substring search is to begin."

Facts:
StrToSearch = 'Min. Int.'!D18-362
StrToFind = "-362"

All is OK with:
Replace(StrToSearch, StrToFind, "",1,1,vbTextCompare)

What I want is to assure that the -362 is in the 16th start position - to be more accurate.

But if I specify Replace(StrToSearch, StrToFind, "",16,1,vbTextCompare) then
I get an empty string

What am I doing wrong?

Further, I know that the string I wish to delete from StrToSearch is Mid(StrToSearch,16,4)
How can I delete/Replace that exact string "-362" from its exact position in StrToSearch?

Any Thoughts appreciated,

Thanks, EagleOne
 
B

Bernie Deitrick

Dim StrToSearch As String
Dim StrToFind As String

StrToSearch = "'Min. Int.'!D18-362 and some more stuff"
StrToFind = "-362"


MsgBox Replace(StrToSearch, StrToFind, "", 1, 1, vbTextCompare)

'What I want is to assure that the -362 is in the 16th start position - to be more accurate.

If Mid(StrToSearch, 16, Len(StrToFind)) = StrToFind Then
MsgBox Left(StrToSearch, 15) & Mid(StrToSearch, 16 + Len(StrToFind))
Else
MsgBox "It wasn't there"
End If

HTH,
Bernie
MS Excel MVP
 
E

EagleOne

Hello Rick,

I realize that I can I can ascertain if -362 starts in position with InStr. That said, what
function can I then use to replace that exact -362?

Example:

Assume SearchToStr = "=Sum("A1:A20")+20"
StrToFind = "20"
ReplaceWith = ""

X = Replace(SearchToStr, StrToFind, ReplaceWith,1,-1,vbTextCompare)

? X yields "=Sum("A1:A")+"

How do I assure that that X is Sum("A1:A20")+"
 
P

Patrick Molloy

if you used

Replace(StrToSearch, StrToFind, "")

you'd simply replace the "-362" with null , returning the left 16
characters, ie
'Min. Int.'!D18

but you're asking to return 1 character, from the 16th....this is the "-"
letter, which is now null, hence the null string returned.

for example, if you changed the 16 to 15, the "8" , then you'd get back one
character, the letter 8




Hi Pat,

StrToSearch = "'Min. Int.'!D18-362"

Forgot, only for the question, the " "

The actual Variable StrToSearch was valued with the " "

Patrick Molloy said:
what does

StrToSearch = 'Min. Int.'!D18-362

actually have as its value?


2003, 2007


PER Excel VBA Help: (I guess you knew that!)

Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing sub string to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is to
begin. If
omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If
omitted,
the
default value is 1, which means make all possible substitutions.
compare Optional. Numeric value indicating the kind of comparison to use
when evaluating
substrings. See Settings section for values.

That said, I "assume" that Start is "Position within expression where
substring search is to begin."

Facts:

StrToSearch = 'Min. Int.'!D18-362
StrToFind = "-362"

All is OK with:
Replace(StrToSearch, StrToFind, "",1,1,vbTextCompare)

What I want is to assure that the -362 is in the 16th start position -
to
be more accurate.

But if I specify Replace(StrToSearch, StrToFind, "",16,1,vbTextCompare)
then
I get an empty string

What am I doing wrong?

Further, I know that the string I wish to delete from StrToSearch is
Mid(StrToSearch,16,4)
How can I delete/Replace that exact string "-362" from its exact
position
in StrToSearch?

Any Thoughts appreciated,

Thanks, EagleOne
Click to expand...
Click to expand...
Click to expand...
 
E

EagleOne

Excellent Bernie

Bernie Deitrick said:
Dim StrToSearch As String
Dim StrToFind As String

StrToSearch = "'Min. Int.'!D18-362 and some more stuff"
StrToFind = "-362"


MsgBox Replace(StrToSearch, StrToFind, "", 1, 1, vbTextCompare)

'What I want is to assure that the -362 is in the 16th start position - to be more accurate.

If Mid(StrToSearch, 16, Len(StrToFind)) = StrToFind Then
MsgBox Left(StrToSearch, 15) & Mid(StrToSearch, 16 + Len(StrToFind))
Else
MsgBox "It wasn't there"
End If

HTH,
Bernie
MS Excel MVP


2003, 2007


PER Excel VBA Help: (I guess you knew that!)

Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing sub string to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is to begin. If
omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If omitted, the
default value is 1, which means make all possible substitutions.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating
substrings. See Settings section for values.

That said, I "assume" that Start is "Position within expression where substring search is to
begin."

Facts:

StrToSearch = 'Min. Int.'!D18-362
StrToFind = "-362"

All is OK with:
Replace(StrToSearch, StrToFind, "",1,1,vbTextCompare)

What I want is to assure that the -362 is in the 16th start position - to be more accurate.

But if I specify Replace(StrToSearch, StrToFind, "",16,1,vbTextCompare) then
I get an empty string

What am I doing wrong?

Further, I know that the string I wish to delete from StrToSearch is Mid(StrToSearch,16,4)
How can I delete/Replace that exact string "-362" from its exact position in StrToSearch?

Any Thoughts appreciated,

Thanks, EagleOne
Click to expand...
Click to expand...
 
R

Rick Rothstein

Replace is an odd function (in my view) in that if you specify a starting
position to do the replacement at, then it returns a substring starting at
that position before making the substitution (see the Remarks section for
the Replace function). So, to do the replace and keep the front part of the
string, you will need to concatenate it back together...

Position = 16
AfterReplacement = Left(StrToSearch, Position - 1) & Replace(StrToSearch, _
StrToFind, "", Position, , vbTextCompare)
 
E

EagleOne

Rick,

Clever! I was either blind or VBA Replace() was not operating as documented.
I am not blind.

EagleOne
 

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

Similar Threads

Behaviour of "Replace" 4
Help with Regexp, please 12
Replace function - Is this normal? 4
Replace() Function -- Syntax Error 1
Use 'like' or regex to match number-letter pattern substrings 1
Len and InStr problem manipulating strings 3
InStrRev Question 4
Replace Function 2

Top