Thanks Rick!

Doug

"Rick Rothstein (MVP - VB)" wrote in
message ...
No, to replace characters on the left, you would set the start value at
one, like this...

S = "12345678"
Mid(S, 1, 3) = "ABC"

To replace on the right, you would do it this way...

S = "12345678"
Mid(1, Len(S) - 2, 3) = "ABC"

By the way, it is -2 (not -3) because we are subtracting to produce a
counting position, hence the +1 comes into play (LenS - LenR + 1).

As an aside, using Mid as a statement is a very, very fast way to replace
characters in a piece of Text... much, much faster than using
concatenation. This speed advantage doesn't come into play if you are just
using it once or twice; but, in a large loop, the time difference can be
quite substantial.

One final note... if the number of characters that you are going to
replace is exactly equal to the length of the replacement text on the
right, you do not need to specify the length parameter of the Mid
statement... if you omit the Length statement, then all character in the
replacement text will be assigned. There is no difference between doing
this...

S = "12345678"
Mid(1,2, 3) = "ABC"

and this...

S = "12345678"
Mid(1, 2) = "ABC"

because we want the all of ABC to replace 234 in the source string.
However, there is a big difference between doing this...

S = "12345678"
Mid(1,2, 3) = "ABCDEF"

and this...

S = "12345678"
Mid(1, 2) = "ABCDEF"

because we only want **part** of the replacement string (its first 3
characters) so now the length argument is very important.

Rick



"Doug Glancy" wrote in message
...
That is interesting.

When you press F1 over the word "Mid" in XL2003 it gives a choice between
the Mid function and the Mid statement. It's the statement that allows
you to replace, and there is no analogous Left or Right statement (only
functions).

For overwriting the left part I guess I'd use:
s = "12345678"
s = "abc" & Right(s, Len(s) - 3)

or something like that.

hth,

Doug

wrote in message
...
Empirically I learned that the VBA function Mid() can be used on the
left side of an assignment. For example:

s = "12345678"
Mid(s, 4, 2) = "cd"

results in "123cd678". But the following does not work:

Left(s, 2) = "ab"
Right(s,2) = "ef"

Then I looked at the Help text for Mid, and I found nothing that
indicates that even Mid() can be on the left side of an assignment.

Where is written that I can use Mid() on the left side?

Is there something else that I should use to overwrite any part of a
string?

(Other than cumbersome concatenation like Mid(s,1,3) & "cd" & Mid(s,
6,3).)

Is there an easier way to overwrite the left and right parts of a
string other than using Mid()?