TOC2HTML

[smozgur]

TOC2HTML creates the table of contents HTML code by transforming the provided list to an HTML list element to be used in the book's HTML page.

I usually create the HTML codes for the table of contents of the book pages to be published in the MrExcel Bookstore by using the list that @MrExcel sends me in a worksheet. Although it is possible to use regex101.com online tool with a few simple patterns, it takes a few steps to complete the transformation with Regex. Therefore, I wrote a user-defined function (UDF) in VBA to create the HTML code for the table of contents in a single step.

The following shows how I converted the UDF named TOC to a recursive Lambda function named TOC2HTML. You can find the UDF at the bottom of this post to compare the processes.

=LAMBDA(rng,chapterKeyword,i,
    LET(rows, ROWS(rng),
        IF(i = rows,
            "",
            LET(i, i+1,
                cll, INDEX(rng,i),
                isFirstLevel, IFERROR(FIND(chapterKeyword, cll),0),
                IF(isFirstLevel,
                     IF(i=1,
                    "<ul>","</ul></li>") & "<li><p>" & cll & "</p><ul>",
                    "<li>" & cll & "</li>"
                )
                & TOC2HTML(rng, chapterKeyword, i)
                & IF(i = rows - 1,
                        "</ul></li></ul>",
                        ""
                    )
            )
        )
    )
)

Toc.xlsm
	A	B
1		HTML Code
2		<ul><li><p>Chapter 0: Not an Introduction, But a Continuation</p><ul></ul></li><li><p>Chapter 1: Recap </p><ul><li>1.1: Best Practice Concept</li><li>1.2: Time Series Analysis</li><li>1.3: Financial Statement Theory</li><li>1.4: Control Accounts</li></ul></li><li><p>Chapter 2: What-If? Analysis</p><ul><li>2.1: Conditional Formulae</li><li>2.2: Offset</li><li>2.3: Scenario Analysis</li><li>2.4: Data Tables</li><li>2.5: Index and Match</li><li>2.6: Using Tornado Charts for Sensitivity Analysis</li><li>2.7: Simulations Analysis</li><li>2.8: Variance Analysis</li><li>2.9: Breakeven Analysis</li></ul></li><li><p>Chapter 3: Forecasting Considerations</p><ul><li>3.1: Seasonal / Cyclical Forecasting</li><li>3.2: Revising Forecasts</li><li>3.3: Pro-Rating Forecasts Over Time</li><li>3.4: Changing Periodicities</li><li>3.5: Modelling Historical, Actual and Forecast Data</li><li>3.6: Rolling Budgets and Charts</li><li>3.7: Forecasting Maximum Cash Required</li></ul></li><li><p>Chapter 4: Modelling Inventory</p><ul></ul></li><li><p>Chapter 5: Capital Expenditure</p><ul></ul></li><li><p>Chapter 6: Debt</p><ul><li>6.1: Debt Sculpting</li><li>6.2: Calculating Interest Rates Correctly</li><li>6.3: Useful Repayment Functions and Formulae</li></ul></li><li><p>Chapter 7: Valuation Considerations</p><ul><li>7.1: Deriving the Correct Cash Flow For DCF</li><li>7.2: Considering Discount Factors</li><li>7.3: Common DCF Modelling Errors</li><li>7.4: Using the Dividend Discount Model</li><li>7.5: Irrelevant IRR</li><li>7.6: Modified Internal Rate of Return (MIRR)</li><li>7.7: Smoothing Capital Expenditure</li><li>7.8: Calculating Economic Lives</li></ul></li><li><p>Chapter 8: Linking Models</p><ul></ul></li><li><p>Chapter 9: Life’s Too Short</p><ul><li>9.1: Section Numbering</li><li>9.2: US vs. European Dates</li><li>9.3: Sheet Referencing</li><li>9.4: Reducing File Size</li><li>9.5: Taking It to the Limit</li><li>9.6: Order of Operations</li><li>9.7: Keeping Styles Under Control</li><li>9.8: Wearing Protection</li></ul></li><li><p>Chapter 10: Look To The Future</p><ul><li>10.1: Dynamic Arrays</li><li>10.2: XLOOKUP and XMATCH</li><li>10.3: Let It Be</li><li>10.4: New Data Types</li></ul></li></ul>
3	Chapter 0: Not an Introduction, But a Continuation
4	Chapter 1: Recap
5	1.1: Best Practice Concept
6	1.2: Time Series Analysis
7	1.3: Financial Statement Theory
8	1.4: Control Accounts
9	Chapter 2: What-If? Analysis
10	2.1: Conditional Formulae
11	2.2: Offset
12	2.3: Scenario Analysis
13	2.4: Data Tables
14	2.5: Index and Match
15	2.6: Using Tornado Charts for Sensitivity Analysis
16	2.7: Simulations Analysis
17	2.8: Variance Analysis
18	2.9: Breakeven Analysis
19	Chapter 3: Forecasting Considerations
20	3.1: Seasonal / Cyclical Forecasting
21	3.2: Revising Forecasts
22	3.3: Pro-Rating Forecasts Over Time
23	3.4: Changing Periodicities
24	3.5: Modelling Historical, Actual and Forecast Data
25	3.6: Rolling Budgets and Charts
26	3.7: Forecasting Maximum Cash Required
27	Chapter 4: Modelling Inventory
28	Chapter 5: Capital Expenditure
29	Chapter 6: Debt
30	6.1: Debt Sculpting
31	6.2: Calculating Interest Rates Correctly
32	6.3: Useful Repayment Functions and Formulae
33	Chapter 7: Valuation Considerations
34	7.1: Deriving the Correct Cash Flow For DCF
35	7.2: Considering Discount Factors
36	7.3: Common DCF Modelling Errors
37	7.4: Using the Dividend Discount Model
38	7.5: Irrelevant IRR
39	7.6: Modified Internal Rate of Return (MIRR)
40	7.7: Smoothing Capital Expenditure
41	7.8: Calculating Economic Lives
42	Chapter 8: Linking Models
43	Chapter 9: Life’s Too Short
44	9.1: Section Numbering
45	9.2: US vs. European Dates
46	9.3: Sheet Referencing
47	9.4: Reducing File Size
48	9.5: Taking It to the Limit
49	9.6: Order of Operations
50	9.7: Keeping Styles Under Control
51	9.8: Wearing Protection
52	Chapter 10: Look To The Future
53	10.1: Dynamic Arrays
54	10.2: XLOOKUP and XMATCH
55	10.3: Let It Be
56	10.4: New Data Types
Continuing Financial Modelling

Cell Formulas
Range	Formula
B2	B2	=TOC2HTML(toc_list,"Chapter ", )

Named Ranges
Name	Refers To	Cells
toc_list	='Continuing Financial Modelling'!$A$3:$A$56	B2

The following shows the result of the produced code in the browser.

HTML result

The VBA user-defined function. Although it is not necessary, I used indents and line breaks in the UDF version. It could be easily implemented in the Lambda version as well.

Function TOC(rng As Range, firstLevel As String)
Dim cll As Range
Dim i As Integer
    For i = 1 To rng.Rows.Count
        Set cll = rng.Cells(i)
        If InStr(cll.Value, "Chapter ") Then
            If i = 1 Then
                TOC = "<ul>"
            Else
                TOC = TOC & vbCrLf & String(2, vbTab) & "</ul>" & vbCrLf & vbTab & "</li>"
            End If
            TOC = TOC & vbCrLf & vbTab & "<li>" & vbCrLf & String(2, vbTab) & "<p>" & cll.Value & "</p>" & vbCrLf & String(2, vbTab) & "<ul>"
        Else
            TOC = TOC & vbCrLf & String(3, vbTab) & "<li>" & cll.Value & "</li>"
        End If
        If i = rng.Rows.Count Then
            TOC = TOC & vbCrLf & String(2, vbTab) & "</ul>" & vbCrLf & vbTab & "</li>" & vbCrLf & "</ul>"
        End If
    Next i
End Function