Adding/updating text between a string or cell with text

[WaqasTariq]

I have the following stored in a string (can be in a cell as well, if needed). Let's say s_String1(2,1) or cell A2:

This text is permanent //Changeable-Text-Starts-Here This text can be changed by macro //Changeable-Text-Ends-Here This is also permanent

I want to be able to remove /change / update the text "This text can be changed by macro" to something different that is stored in s_String2(2,1). I have been searching all over the forum but have not found an example of this being done before. Can anyone help with this?

This text is permanent //Changeable-Text-Starts-Here This text HAS been changed. And I can add more lines to it. //Changeable-Text-Ends-Here This is also permanent

 

[DanteAmor]

Try with this formula:

Book1
	A	B
1
2	Some text
3
4	This text is permanent //Changeable-Text-Starts-Here This text can be changed by macro //Changeable-Text-Ends-Here This is also permanent	This text is permanent //Changeable-Text-Starts-Here Some text //Changeable-Text-Ends-Here This is also permanent
Sheet2

Cell Formulas
Range	Formula
B4	B4	=SUBSTITUTE(A4,"This text can be changed by macro",A2)

___________________________________________________________
Or with macro:

Sub Macro1()
  Range("B4").Formula = "=SUBSTITUTE(A4,""This text can be changed by macro"",A2)"
End Sub

 

[Scott Huish]

In VBA, there is the Replace function:

Sub test()
Dim s As String
s = "This text is permanent //Changeable-Text-Starts-Here This text can be changed by macro //Changeable-Text-Ends-Here This is also permanent"
MsgBox Replace(s, "This text can be changed by macro", "New text")
End Sub

 

[WaqasTariq]

Thank you guys, but the "This text can be changed by macro" is dynamic, while "//Changeable-Text-Starts-Here" and "//Changeable-Text-Ends-Here" are the only constants.

 

[DanteAmor]

Try this

=REPLACE(A4,SEARCH("//Changeable-Text-Starts-Here",A4)+LEN("//Changeable-Text-Starts-Here")+2,SEARCH("//Changeable-Text-Ends-Here",A4)-SEARCH("//Changeable-Text-Starts-Here",A4)-LEN("//Changeable-Text-Starts-Here")-4,A2)

With macro

Sub Macro1a()
  Dim oText As String
  oText = "//Changeable-Text-Starts-Here"
  Range("B4").Formula = _
    Replace("=REPLACE(A4,SEARCH(""@"",A4)+LEN(""@"")+2,SEARCH(""//Changeable-Text-Ends-Here"",A4)-SEARCH(""@"",A4)-LEN(""@"")-4,A2)", "@", oText)
End Sub

 

[WaqasTariq]

Try this

=REPLACE(A4,SEARCH("//Changeable-Text-Starts-Here",A4)+LEN("//Changeable-Text-Starts-Here")+2,SEARCH("//Changeable-Text-Ends-Here",A4)-SEARCH("//Changeable-Text-Starts-Here",A4)-LEN("//Changeable-Text-Starts-Here")-4,A2)

With macro

Sub Macro1a()
  Dim oText As String
  oText = "//Changeable-Text-Starts-Here"
  Range("B4").Formula = _
    Replace("=REPLACE(RC[-1],SEARCH(""@"",RC[-1])+LEN(""@"")+2,SEARCH(""//Changeable-Text-Ends-Here"",RC[-1])-SEARCH(""@"",RC[-1])-LEN(""@"")-4,R[-2]C[-1])", "@", oText)
End Sub

Thank YOU! works perfectly.

Happy holidays!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.