Convert multiple text lines in 1 cell to an unordered list

rajsidhu

New Member
Joined
Mar 8, 2022
Messages
7
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:-

VBA 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? (y)
 
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?
Hi JoeMo,

So your code worked as so far it made each line in the cell a list item which is what i want. But i also need the code to add the outer tags <ul> and </ul>

The error I get when updating the code as suggested by Fluff is -

Can't execute code in break mode

and the line i add is highlighted yellow.
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
It's in the code you originally posted.
Excellent. I got it to work.

I was adding the line to the code given by JoMoe and didn't realise i should have added it to my original code.

Thank you guys so much (y)(y)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
For the record, the code below will produce the outer tags and return the bullets in random order. And I still wonder what the OP means by "unordered" ????????
VBA Code:
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 = "<ul>" & Chr(10) & Join(splOUT, Chr(10)) & Chr(10) & "</ul>"     'Join(splOUT, Chr(10))
    Next rCell
Selection.ColumnWidth = 200
Selection.EntireRow.AutoFit
Selection.EntireColumn.AutoFit
End Sub
 
Upvote 0
Here's a short video explaining what unordered and ordered lists are.

HTML Ordered and Unordered Lists

The reason i wanted to add the outer ul tags was because when you import the list this way into a WooCommerce page, it left aligns the list better.

Anyway, thanks for helping me with this, although I'm probably going to have ask more questions in the near future!

If anyone could recommend a simple to follow course on VBA for someone who doesn't code, that would really help?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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
Back
Top