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

 

[Norie]

Do you know what character these 'bullet points' are?

 

[desibouy]

Not sure what you mean? They come to me as dotted feature:

• Crisscross Structure
• Velcro Closure
• 2 Angle Pulls

or like

-Crisscross Structure
-Velcro Closure
-2 Angle Pulls


I remove these using find and replace.

 

[desibouy]

Bump Anyone? I really need to do this quite urgently. Just been sent another 2 spreadsheets

 

[desibouy]

Maybe this...

Select the cells and run this macro - try it on a copy of your workbook

Sub aTest()
    With Selection
        .Value = Evaluate(Replace("IF(@<>"""",""""&@&"""","""")", "@", .Address))
     End With
End Sub

M.

Hi,
How do I use it? I've created a module and pasted this in but then how do I get it to run? Don't I need to press a key or use a formula?

Also I can't see

in the macro, so how would I know if it's worked.

Thanks


- Edit -


I think that was a mistake on your behalf. Responded to the wrong post :P

 

[Marcelo Branco]

I deleted the previous post (forum software truncated the string because of html tags)

Maybe this macro - try it on a copy of your workbook

Select the cells run the macro

Sub aTest()
    Dim strFormula As String
    
    With Selection
        strFormula = "=IF(@<>"""",""" & Chr(60) & "li" & Chr(62) & """&@&""" & Chr(60) & "/li" & Chr(62) & ""","""")"
        myStr = Replace(strFormula, "@", .Address)
        .Value = Evaluate(strFormula)
    End With
End Sub

M.

 

[Marcelo Branco]

Sorry. there is a typo in my macro

Try

Sub aTest()
    Dim strFormula As String
    
    With Selection
        strFormula = "=IF(@<>"""",""" & Chr(60) & "li" & Chr(62) & """&@&""" & Chr(60) & "/li" & Chr(62) & ""","""")"
        strFormula = Replace(strFormula, "@", .Address)
        .Value = Evaluate(strFormula)
    End With
End Sub

M.

 

[desibouy]

Sorry. there is a typo in my macro

Try

Sub aTest()
    Dim strFormula As String
    
    With Selection
        strFormula = "=IF(@<>"""",""" & Chr(60) & "li" & Chr(62) & """&@&""" & Chr(60) & "/li" & Chr(62) & ""","""")"
        strFormula = Replace(strFormula, "@", .Address)
        .Value = Evaluate(strFormula)
    End With
End Sub

M.

Thanks lol. It happens to a lot of us :D

Anyway, I've created a Module and pasted this, however, how do I get this to run? Thanks

oh by the way do it include '.address' and can't see < >

 

[Marcelo Branco]

To run the code

Select the cells
Alt+F8 and select the macro

Chr(60) is <
Chr(62) is >

M.

 

[desibouy]

To run the code

Select the cells
Alt+F8 and select the macro

Chr(60) is <
Chr(62) is >

M.

Hi,

It's working but not correctly.


Instead of showing like this -

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

You macro is showing this -

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

So it's only the first and last where the tags are being added. I need them on every line when the sentence starts and ends.

Thanks