VBA offers a Mid function for returning substrings within a string, but it also offers the Mid Statement which can be used to assign substrings or individual characters withing a string.
Mid function will typically appear on the right-hand-side of an assignment statement or in a condition, but the
Mid Statement typically appears on the left hand side of an assignment statement.
Dim surname As String surname = "Smith" 'Use the Mid statement to change the 3rd character in a string Mid(surname, 3, 1) = "y" Debug.Print surname 'Output: 'Smyth
Note: If you need to assign to individual bytes in a string instead of individual characters within a string (see the Remarks below regarding the Multi-Byte Character Set), the
MidB statement can be used. In this instance, the second argument for the
MidB statement is the 1-based position of the byte where the replacement will start so the equivalent line to the example above would be
MidB(surname, 5, 2) = "y".