Convert Excel Cell to Bullet Points HTML (<li></li>)

[desibouy]

Hello,

So I've gotten like 6 spreadsheets with Data, and one of them is product features, they've been sent to me with either the bullet point (just the dots) or hyphens. Each spreadsheet contains 70 products, so about 420.

I've used Replace and Find to remove them so it's just a simple list.

Crisscross Structure
Velcro Closure
2 Angle Pulls



So now, I want to upload these to my ERP/CRM backend but I gotta put the bullet points to be in HTML tags

< li >Crisscross Structure< / li >
< li >Velcro Closure< / li >
< li >2 Angle Pulls< / li >

So doing this manually using concat to add front and back
is taking a long long loooooong time! lol

Can anyone help me?

Thanks

 

[Marcelo Branco]

The macro assumed each text on a different cell.
Are the three texts on a single cell in different lines?

M.

 

[desibouy]

I must not have been clear. Each spreadsheet contains about 70 products lines. All which are a different product. Each column has different data. Description, Features, Specifications etc. However, the Features is per cell. So the example I had given would be in like Cell C5. All three lines in one cell, So I need them converted to HTML so when I upload whatever is in the cell will be translated accordingly. The next cell C6 would contain 12 different points which I need to correct to HTML. If that makes sense.

Thanks

 

[Marcelo Branco]

See if this does what you need

Select the cells and run the macro below

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

M.

 

[desibouy]

Brilliant!! It took like at least 5-10mins and crashed both Excel and Windows T.T

Any way to make it not so? I can live with the time it takes but crashing the whole laptop I can't do other things.

 

[Marcelo Branco]

It worked perfectly for me. How many cells have you selected?
I do not know how to help you. I hope someone really "bright" can do it.
Good luck

M.

 

[desibouy]

Hey Marcelo,

Sorry, didn't mean to be offensive. I selected 700 Cells I just merged all the files into one so that's why it took a long time and crashed my computer.

So I just went to the individual sheets and just used the macro on 50 lines and it worked without a hitch. AGAIN I really appreciate your help and this works wonders.Really happy.



Thank You.

 

[Marcelo Branco]

Ok, no problem.
Glad to help

M.