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
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