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

what does

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

actually have as its value?


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

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.

--
Rick (MVP - Excel)


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

Hi Pat,

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

Forgot, only for the question, the " "

The actual Variable StrToSearch was valued with the " "

"Patrick Molloy" wrote:

what does

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

actually have as its value?


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

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





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

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


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

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")+"


"Rick Rothstein" wrote:

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.

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




wrote in message
...
Hi Pat,

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

Forgot, only for the question, the " "

The actual Variable StrToSearch was valued with the " "

"Patrick Molloy" wrote:

what does

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

actually have as its value?


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

Excellent Bernie

"Bernie Deitrick" <deitbe @ consumer dot org wrote:

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


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

Works!!! Thanks Jim


"Jim Cone" wrote:

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.

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)

--
Rick (MVP - Excel)


wrote in message
...
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")+"


"Rick Rothstein" wrote:

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.

Rick,

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

EagleOne

"Rick Rothstein" wrote:

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)