Need to convert multiple Excel cells into a combined htlm bullet point format

[tiffw]

Hi all,

I use Excel frequently, but would only dare say I'm proficient with the basic processes. I need to import several thousand lines of inventory data to an online inventory program via a csv file. The new program I'm using only allows me to import one data field for ALL the bullet points. (On my old program, I could just import the bullet points in their individual cells.)

I have never used a macro, so all the online reading I've done is really confusing. I need a way to convert my bullet point data cells into one html data field, and to automate a process to do this with several thousand lines of data.

I have actually read over the following thread multiple times, but realize I need a bit more help understanding how to implement this type of process. Macros are still a foreign concept for me! --
https://www.mrexcel.com/forum/excel...vert-excel-cell-bullet-points-html-li-li.html

The inventory data I received via my previous system’s data export gives me all the product bullet point data in individual cells (for example, D5, E5, F5, G5…). Unfortunately, the new system I'll be using requires me to import only one HTLM formatted cell for all my bullet point data. I’ve spent a frightening number of hours trying to come up with a way to combine the cells and format them in HTML bullet point formatting, but I really need help.

So far, I have managed to use the Combine feature in the Dig DB add-on tool in order to combine all the bullet point data into one cell, separated by a comma and a space. Here's an example of what I ended up with after combining the the data from cells D5, E5, F5, and G5:

Made of 100% Cotton, Features a red geometric pattern, Measures 80 x 12 inches, Machine washable

So now I'm ready to convert those combined lines to html formula, and I’m absolutely stuck. I found an online tool (http://www.limaker.com/) that would work, but I’d have to copy and paste EVERY combined field into the tool. I have thousands of lines of data to do this with, so that take me weeks. The output is in html formatting, which looks like this on my product detail page after being imported --

• Made of 100% cotton
  
• Features a red geometric design
  
• Measures 80 x 12 inches

I’m at a loss for how to do this without taking the combined data out to a different program and copy/pasting every single line. I am hoping and praying someone can help me figure out a way to do this more easily! I’m at my wits end, and desperately need some help finishing this import process. Please keep in mind that I have never created a macro in my life, so if that's what I need to do, I may need the "instructions for noobs" version.

Many thanks in advance!!

Tiffany

 

[John_w]

Welcome to Mr Excel forums!

You've given a lot of detail about the previous and current systems/processes, but it's not completely clear to me what your current Excel data looks like and what you want the macro to produce, i.e. its output. Should the macro create a .csv file containing all the HTML bullet points? Or should the macro create a single cell containing all the HTML bullet points?

Therefore I'm not sure if the following macro will do exactly what you want.

The following macro assumes that each cell (e.g. D5:G5) contains an inventory item and creates a .csv file containing all the HTML bullet points - a single unordered list (one ul tag with multiple li child tags) - from all the cells you select.

Public Sub Create_HTML_Bullet_List_CSV_File()

    Dim CSVfile As Variant
    Dim HTML As String
    Dim cell As Range
    
    CSVfile = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.Path, FileFilter:="CSV file (*.csv), *.csv", Title:="HTML csv output file")
 
    If CSVfile <> False Then
        HTML = "< ul>"
        For Each cell In Selection
            If Not IsEmpty(cell.Value) Then
                HTML = HTML & vbCrLf & "< li>" & Trim(cell.Value) & "< /li>"
            End If
        Next
        HTML = HTML & vbCrLf & "< /ul>"
        HTML = Replace(HTML, "< ", "<")
        Open CSVfile For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , HTML
        Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    End If
    
End Sub

To use, copy and paste the code into a regular module - see http://www.contextures.com/xlvba01.html#videoreg - and save the workbook as a macro-enabled workbook (.xlsm file).

With the inventory items in multiple cells (e.g. D5:G5), first select all those cells and then run the code (macro), Create_HTML_Bullet_List_CSV_File. (The Contextures page also lists the steps to run the code). When run, the macro prompts for the HTML csv output file and then creates the .csv file from all the selected cells. Note - it ignores empty selected cells and overwrites the .csv file if it already exists.

Hopefully this macro works according to your requirements, but if not let us know and we can tweak it as needed.

 

[tiffw]

Thanks SO much for taking the time to help and respond John!

To better clarify, each product in my spreadsheet is on its own row, and the columns across the row contain different types of product data pertaining to that particular product. Therefore,column 2A might typically be the product SKU, 2B would be the title, 2C would be the color, etc. Somewhere along that row would be the bullet point cells. So for example, 2G may have the data for bullet point 1, 2H would be data for bullet point 2, 2I bullet point 3, etc.

In order to import the product data into my new system, I need to have all the product detail data in a single cell. The export I got from my previous system put each bullet point in its own cell. So since I like to display all my product details in bullet point format, I somehow need to combine the export's existing 4 or 5 excel cells into one, and have it formatted for html bullet points.

Please let me know if that helps, and if you have further suggestions as to how this might best be done!

 

[John_w]

Try this macro instead, which puts the HTML unordered list in cell A1. To use, select the cells containing the bullet point data (G2:I2 in your latest example) and run the macro.

Public Sub Create_HTML_Bullet_List_Cell()

    Dim destCell As Range
    Dim HTML As String
    Dim cell As Range
    
    Set destCell = ActiveSheet.Range("A1")
    
    HTML = "< ul>"
    For Each cell In Selection
        If Not IsEmpty(cell.Value) Then
            HTML = HTML & vbCrLf & "< li>" & Trim(cell.Value) & "< /li>"
        End If
    Next
    HTML = HTML & vbCrLf & "< /ul>"
    HTML = Replace(HTML, "< ", "<")
    
    destCell.Value = HTML
    
End Sub