Bulk Text Macro - Require Assistance in separating each line in HTML

[JackBTM]

Hi everyone,

Hope you've all had a wonderful weekend.

I have received 250 cells which I need to format into HTML for the system I use on my computer for work. Each line/paragraph needs to start with <p> and close with a </p>, however the below macro I have tried using is putting <p></p> in between each paragraph in the space sections. I need this to be gone as the system we use won't accept empty <p></p> lines.

Is anyone able to assist or fix the below please? Would be much appreciated. Will attach an image of how it looks and the macro I am using.

Sub aTest()
    Dim rCell As Range, spl As Variant, i As Long
   
    For Each rCell In Selection
        spl = Split(rCell, Chr(10))
        For i = LBound(spl) To UBound(spl)
            spl(i) = Chr(60) & "p" & Chr(62) & spl(i) & Chr(60) & "/p" & Chr(62)
        Next i
        rCell = Join(spl, Chr(10))
    Next rCell
    Selection.ColumnWidth = 200
    Selection.EntireRow.AutoFit
    Selection.EntireColumn.AutoFit
End Sub

 

[mohadin]

Hi
what about

Sub aTest()
    Dim rCell As Range, spl As Variant, i As Long

    For Each rCell In Selection
        spl = Split(rCell, Chr(10))

        For i = LBound(spl) To UBound(spl)
            If spl(i) <> "" Then
                spl(i) = Chr(60) & "p" & Chr(62) & spl(i) & Chr(60) & "/p" & Chr(62)
            End If
        Next i
        rCell = Join(spl, Chr(10))

    Next rCell
    Selection.ColumnWidth = 200
    Selection.EntireRow.AutoFit
    Selection.EntireColumn.AutoFit
End Sub

 

[JackBTM]

Hi
what about

Sub aTest()
    Dim rCell As Range, spl As Variant, i As Long

    For Each rCell In Selection
        spl = Split(rCell, Chr(10))

        For i = LBound(spl) To UBound(spl)
            If spl(i) <> "" Then
                spl(i) = Chr(60) & "p" & Chr(62) & spl(i) & Chr(60) & "/p" & Chr(62)
            End If
        Next i
        rCell = Join(spl, Chr(10))

    Next rCell
    Selection.ColumnWidth = 200
    Selection.EntireRow.AutoFit
    Selection.EntireColumn.AutoFit
End Sub

Thank you so much! This worked!!

 

[mohadin]

You are welcome
And thank you for the feedback
Be happy & safe

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Bulk Text Macro - Require Assistance in seperating each line in HTML
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[JackBTM]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Bulk Text Macro - Require Assistance in seperating each line in HTML
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

My apologies - I posted on the site but a white filter came over my browser on the site, so I posted on here instead. I did not realise it had submitted.

I have messaged their Contact Team to see if they can delete the post - sorry once again!

 

[Peter_SSs]

Would this do the same thing without any looping at all? (Assumes the data is in column D starting at row 2 - but easily editable)

Sub Format_HTML()
  With Range("D2", Range("D" & Rows.Count).End(xlUp))
    .Value = Evaluate("substitute(substitute(""<p>""&" & .Address & "&""</p>"",char(10),""</p>"" & char(10)&""<p>""),""<p></p>"","""")")
  End With
End Sub