Excel Tables to Markdown Table Routine

Update: The same day that I wrote this blog post I also discovered Markdown Table Generator. They allow import of CVS files as well as direct copy and paste tables which then get coverted to Markdown tables. That’s a lot faster. My routine below no longer works.

I compose my blog and emails and even Evernote with Markdown, I typically use Markdown Here for quick and easy converts. However, sometimes I needed a table to be sent via emails to other folks. Typing up a markdown table is a real pain, so I just create a function in excel to do the routine for me.

A Custom Function

To get the table below, we define a custom function:

1
2
3
4
5
6
7
8
9
10
11
12
13
Function ColumnToText(rng As Range, Optional sep As String = ",") As String
    Dim rngCell As Range
    Dim strResult As String
    For Each rngCell In rng
        If rngCell.Value <> "" Then
            strResult = strResult & sep & Trim(rngCell.Text)
        End If
    Next rngCell
    If strResult <> "" Then
        strResult = Mid(strResult, Len(sep) + 1)
    End If
    ColumnToText = strResult
End Function

Source

The function takes in the range and the optional choice is the type of separator. The default separator is comma (

1

'

1

|

1
= ColumnToText(A1:F1,"|")

For the header separator such as this

1

-|-|-|-|-|-

1
=REPT("-|",COUNTA(A1:F1)-1)&"-"

The function just counts the number Of course this does not define alignment. I usually don’t care much for alignment anyway. Then just paste the resulting header separator right underneath your header line.

And we have the combined values:

1
2
3
4
5
PSV Tag|Size|Capacity|New Capacity|Restrict %|Area (sqin)
-|-|-|-|-|-
29.2-93-0004|8T10|210,079|174,219|83%|24
29.2-93-0005|8T11|210,079|166,960|79%|23
29.2-93-0006|8T12|210,079|130,664|62%|17

The resulting table is: