Re: Help with Replace()

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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




<EagleOne@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:va3m0594q3b02m8as0s6utm5dbbabgr0ne@xxxxxxxxxx
Hi Pat,

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

Forgot, only for the question, the " "

The actual Variable StrToSearch was valued with the " "

"Patrick Molloy" <patrick_molloy@xxxxxxxxxxx> wrote:

what does

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

actually have as its value?


<EagleOne@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:h30m05d7aonn6q3prs4p2akbb8aegbnsda@xxxxxxxxxx
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

.

Relevant Pages

  • Re: SEARCH: expected result?
    ... The null string is string. ... The null string is a substring of every string. ... since the range of s is the empty set. ... So why does SEARCH highlight every character? ...
    (comp.os.vms)
  • Re: Help with Replace()
    ... StrToSearch = 'Min. Int.'!D18-362 ... String expression containing sub string to replace. ... Substring being searched for. ... I "assume" that Start is "Position within expression where substring search is to begin." ...
    (microsoft.public.excel.programming)
  • Re: How 2 get a substring from a string?
    ... string lengths with a variable number of words... ... the last character, or the character after the 4th space, or the 5th ... Seek a reference such as <http://developer.mozilla.org/en/docs/Core_Java ... such as charAt, match, substring and substr. ...
    (comp.lang.javascript)
  • Re: Rules Problem (blank emails)
    ... "this old house" will not match unless you have that exact substring. ... A single character is also a string. ... having to specify 36 phrases to search for, ...
    (microsoft.public.outlook)
  • Re: Sheet Tab Names
    ... >How does that formula work? ... you want a portion of a string to the right of a ... particular character position measured from the left/beginning of the ... the substring, ...
    (microsoft.public.excel.worksheet.functions)