If you already know the start and ending character positions of text you want to replace, this bypasses the need to calculate the length. It just helps eliminate a step by not requiring you to calculate the length because this uses start_num and end_num directly as parameters. It can be used as an Excel function or a VBA function.

Public Function Replace2(ByVal old_text As String, _
                  ByVal start_num As Long, _
                  ByVal end_num As Long, _
                  ByVal new_text As String) As String
    'Replaces old_text from character positions start_num to end_num with new_text;
    '   does not delete any old_text characters when end_num < start_num.
    '   e.g. when end_num is 0.
    'Replace2 is designed to help when it's more convenient to code using start and
    '   end positions rather than being forced to calculate a length.
    'Replace2 can be used as an Excel or VBA function as an alternative to the
    '   Worksheet.Replace function with the following design differences:
    '      1. Replace2 uses a start_num and end_num pair rather than a start_num
    '         with a length.
    '      2. Replace2 is also more forgiving of parameter error, it's designed to
    '         properly interpret and correct start_num and end_num value errors. E.g.
    '         This ensures the start_num falls between 1 and old_text length + 1
    '            and ensures the end_num falls between 0 and old_text length.
    '      3. An end_num of 0 or end_num < start_num does not delete any old_text characters.
    '      4. new_text is inserted just prior to the start_num position in the old_text.
    Dim LenOldText As Long
    LenOldText = Len(old_text)
    If start_num < 1 Then start_num = 1
    If end_num < 0 Then end_num = 0
    If start_num > LenOldText Then start_num = LenOldText + 1
    If end_num > LenOldText Then end_num = LenOldText
    If end_num < start_num Then end_num = start_num - 1
    Replace2 = Left(old_text, start_num - 1) & new_text & Right(old_text, LenOldText - end_num)
End Function
  • \$\begingroup\$ This is working code. I created it for those who would prefer to use the Replace function with a start and end character position and not need to calculate length in their code. I usually know a start and end character position and so this eliminates a step in my coding. \$\endgroup\$
    – Mark Main
    Apr 26, 2021 at 18:11
  • \$\begingroup\$ My suggestion would be to move the new_text parameter up to the second spot, then make start_num and end_num as Optional (with a default value). With this arrangement, calling Replace2 would default to replacing the entire string, or beginning to a given end_num or from a given start_num to the end of the original string. I'm just thinking it may make the function a bit more versatile. \$\endgroup\$
    – PeterT
    Apr 27, 2021 at 15:03
  • \$\begingroup\$ What is the purpose of If end_num < 0 Then end_num = 0? Seems if we set it to zero here, it will be overwritten by If end_num < start_num Then end_num = start_num - 1 since start_num is >=1. And the former statement has no impact on If end_num > LenOldText Then end_num = LenOldText between the two statements as negative and zero will never be greater than the length of the original text. \$\endgroup\$ Apr 27, 2021 at 17:44
  • \$\begingroup\$ In this era of Dynamic Array functions, you might want to rework it to Spill when passed ranges of > 1 cell \$\endgroup\$ Jun 15, 2021 at 5:17


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.