Convert multiple text lines in 1 cell to an unordered list

[rajsidhu]

I read a post in this forum that shows how to make multiple text lines, in 1 cell, into bullet points which works great. Here is the code:-

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) & "li" & Chr(62) & spl(i) & Chr(60) & "/li" & Chr(62)
        Next i
        rCell = Join(spl, Chr(10))
    Next rCell
    Selection.ColumnWidth = 200
    Selection.EntireRow.AutoFit
    Selection.EntireColumn.AutoFit
End Sub

What i need to do is create an unordered list out of these bullet points.

Is there any one who can help me modify the above code so that it spits out an unordered list of bullet points please?

 

[Fluff]

Hi & welcome to MrExcel.
What exactly do you mean by

an unordered list

 

[JoeMo]

I assume by "an unordered list" you mean the code should return the bullets in a random order, not necessarily in the exact order in which they appear prior to code execution.
Lightly tested, but I think this will do that:

Sub aTest()
    Dim rCell As Range, splIN As Variant, splOUT As Variant, i As Long, j As Long, d As Object
    Set d = CreateObject("Scripting.dictionary")
    For Each rCell In Selection
        d.RemoveAll
        splIN = Split(rCell, Chr(10))
        ReDim splOUT(LBound(splIN) To UBound(splIN))
        For i = LBound(splIN) To UBound(splIN)
Again:      j = WorksheetFunction.RandBetween(LBound(splIN), UBound(splIN))
            If Not d.exists(j) Then
                d.Add j, d.Count
                splOUT(j) = Chr(60) & "li" & Chr(62) & splIN(i) & Chr(60) & "/li" & Chr(62)
            ElseIf d.Count < UBound(splIN) + 1 Then
                GoTo Again
            End If
        Next i
        rCell = Join(splOUT, Chr(10))
    Next rCell
Selection.ColumnWidth = 200
Selection.EntireRow.AutoFit
Selection.EntireColumn.AutoFit
End Sub

 

[rajsidhu]

I assume by "an unordered list" you mean the code should return the bullets in a random order, not necessarily in the exact order in which they appear prior to code execution.
Lightly tested, but I think this will do that:

Sub aTest()
    Dim rCell As Range, splIN As Variant, splOUT As Variant, i As Long, j As Long, d As Object
    Set d = CreateObject("Scripting.dictionary")
    For Each rCell In Selection
        d.RemoveAll
        splIN = Split(rCell, Chr(10))
        ReDim splOUT(LBound(splIN) To UBound(splIN))
        For i = LBound(splIN) To UBound(splIN)
Again:      j = WorksheetFunction.RandBetween(LBound(splIN), UBound(splIN))
            If Not d.exists(j) Then
                d.Add j, d.Count
                splOUT(j) = Chr(60) & "li" & Chr(62) & splIN(i) & Chr(60) & "/li" & Chr(62)
            ElseIf d.Count < UBound(splIN) + 1 Then
                GoTo Again
            End If
        Next i
        rCell = Join(splOUT, Chr(10))
    Next rCell
Selection.ColumnWidth = 200
Selection.EntireRow.AutoFit
Selection.EntireColumn.AutoFit
End Sub

Thanks JoMoe for replying.

What i need is my bullet points wrapped in <ul>.....</ul>

So that this (which is all in one cell)

Min./Max. Saddle Height: 170/576mm.
Chassis Length: 1605mm.
European style trolley jack manufactured to exacting standards.
Lifting arm has 2-speed lifting and lowering; lowers rapidly without load and provides smooth, slow, controlled lowering with load.
Lift-and-twist 'Dead Man' mechanism operated via 'D' shaped handle prevents accidental lowering.
Nylon wheels fitted which are quiet in operation and help prevent damage to garage floors.
Foot pedal for quick approach of the lifting saddle to the vehicle.
Includes removable rubber saddle pad and built-in tool tray for fasteners and tools.
This item is heavy. Extra assistance must be provided at the delivery point to help its safe delivery.
Model No. 10000ES

Becomes this (all in one cell)

<ul>
<li> Min./Max. Saddle Height: 170/576mm.</li>
<li> Chassis Length: 1605mm.</li>
<li> European style trolley jack manufactured to exacting standards.</li>
<li> Lifting arm has 2-speed lifting and lowering; lowers rapidly without load and provides smooth, slow, controlled lowering with load.</li>
<li> Lift-and-twist 'Dead Man' mechanism operated via 'D' shaped handle prevents accidental lowering.</li>
<li> Nylon wheels fitted which are quiet in operation and help prevent damage to garage floors.</li>
<li> Foot pedal for quick approach of the lifting saddle to the vehicle.</li>
<li> Includes removable rubber saddle pad and built-in tool tray for fasteners and tools.</li>
<li> This item is heavy. Extra assistance must be provided at the delivery point to help its safe delivery.</li>
<li> Model No. 10000ES</li>
</ul>

I tried your code but it is only wrapping each line with <li>....</li>

I'm hoping that it would only take a small modification to your code to add <ul> to the start of the cell contents and </ul> to the end of the cell contents ?

 

[Fluff]

If all you want to do is add the outer tags, how about this mod to the code you posted

rCell = "<ul>" & Chr(10) & Join(spl, Chr(10)) & Chr(10) & "</ul>"

But I still have no idea what you mean by

an unordered list of bullet points

 

[rajsidhu]

If all you want to do is add the outer tags, how about this mod to the code you posted

rCell = "<ul>" & Chr(10) & Join(spl, Chr(10)) & Chr(10) & "</ul>"

But I still have no idea what you mean by

Yes i need to add <ul> and </ul> as the outer tags as you said.

Where in the code should i add the line

rCell = "<ul>" & Chr(10) & Join(spl, Chr(10)) & Chr(10) & "</ul>"

 

[Fluff]

Replace this line with the code I posted

rCell = Join(spl, Chr(10))

 

[rajsidhu]

Replace this line with the code I posted

rCell = Join(spl, Chr(10))

I don't see that line.

I do see this line

rCell = Join(splOUT, Chr(10))

However when i replaced this line with your code, there was an error.

 

[JoeMo]

What exactly is "unordered" about the desired result you show in post #4?
What is the error message and what line is highlighted when you get the error you refer to in post #8?

 

[Fluff]

I don't see that line.

It's in the code you originally posted.