Replace " </p><p>" with new line using regular expressions

[daniellouw]

I am trying to replace " " with a new line in visual basic using regular expressions.
I tried to find: ([\s]*)("")
and replace with: \n\n.
However, in my text
is replaced but with "\n\n", not two physical new lines.
I also tried to replace it with \r\r, but it does the same.

 

[Yongle]

EDIT : there is a problem uploading certain strings to the forum
replaceWhat string needs correcting

To test this place sample strings in range A1:A5 and run InsertLineBreaks

Sub InsertLineBreaks()
    Dim replaceWhat As String, replaceWith As String, Cel As Range
    replaceWhat = " < / p > < p > "   [COLOR=#ff0000]'AFTER PASTING TO MODULE REMOVE SPACES INSIDE THIS STRING[/COLOR]
     replaceWith = Chr(10) & Chr(10)
    For Each Cel In Range("[COLOR=#ff0000]A1:A5[/COLOR]")
        Cel.Offset(, 1) = FindReplaceRegex(Cel, replaceWhat, replaceWith)
    Next
End Sub
   
Function FindReplaceRegex(rng As Range, RegEx As String, replace As String)
    With CreateObject("vbscript.regexp")
        .IgnoreCase = False
        .Global = True
        .Pattern = RegEx
        FindReplaceRegex = .replace(rng.Value, replace)
    End With
End Function

Note
Chr(10) is the character that is inserted inside cell with {ALT}{ENTER}
Chr(13) is also an option
Chr(10) = Line Feed character , and Chr(13) = Carriage Return

Sub Ten_or_Thirteen()
    Dim Msg As String
    Msg = "Interchangeable..." & [COLOR=#ff0000]vbCr[/COLOR] & "CarriageReturn = " & Asc([COLOR=#ff0000]vbCr[/COLOR]) & [COLOR=#ff0000]vbLf[/COLOR] & "LineFeed =" & Asc([COLOR=#ff0000]vbLf[/COLOR])
    MsgBox Msg, , "Spot the difference?"
End Sub

 

[daniellouw]

Thank you very much. You helped me to find the solution I was looking for.
Here it is:

Sub Replace_with_linebreaks()

Dim StrText1 As String, StrText2 As String, ReplaceWhat As String, ReplaceWith As String

File_Name = "C:\0DATA\test.txt"
StrText1 = "AAA p BBB p CCC"

' Replace " p " with linebreak
ReplaceWhat = " p "
ReplaceWith = Chr(13) & Chr(10)
StrText2 = FindReplaceRegex(StrText1, ReplaceWhat, ReplaceWith)

' Write string to text file
FF = FreeFile
Open File_Name For Output As FF
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] , StrText2
Close FF

' Open file
Shell ("C:\Windows\system32\notepad.exe" & " " & File_Name), vbNormalFocus

End Sub


Function FindReplaceRegex(StrText As String, ReplaceWhat As String, ReplaceWith As String)
    With CreateObject("vbscript.regexp")
        .IgnoreCase = False
        .Global = True
        .Pattern = ReplaceWhat
        FindReplaceRegex = .Replace(StrText, ReplaceWith)
    End With
End Function

It seems a linebreak is both "Chr(13) & Chr(10)" in that order. I have tried "Chr(10) & Chr(10)", "Chr(13) & Chr(13)" and "Chr(10) & Chr(13)" but it didn't work.