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

daniellouw

New Member
Joined
Aug 7, 2012
Messages
10
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.
 
Last edited by a moderator:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
Code:
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
 
Last edited:

daniellouw

New Member
Joined
Aug 7, 2012
Messages
10
Thank you very much. You helped me to find the solution I was looking for.
Here it is:

Code:
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.
 

Forum statistics

Threads
1,136,847
Messages
5,678,109
Members
419,746
Latest member
tysonboy82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top