Making a list from range in Excel

Question

In original column I have years in range(s). Like

1865–1868

How do I transform it to comma separated list? Like:

1865, 1866, 1867, 1868

The current data format is TEXT. And I plan to change it to DATE.

NOTE: The original cell often consists mixed form. Like "1865–1868, 1870"

You could adapt this VBA approach to your specific use case: https://stackoverflow.com/questions/15144816/batch-string-concatenation-in-excel/15145268#15145268 — Marc, May 18 '18 at 13:58
How can you set a cell with multiple years in them to DATE, you would need to split each year to a seperate cell. — Luuklag, May 18 '18 at 14:10
Not sure if it's intentional, but that's not a `-` between those dates, that's a different symbol `–` , perhaps an *em-dash*. — ashleedawg, May 18 '18 at 14:18

Gary's Student · Answer 1 · 2018-05-18T14:45:35.723

2

How about:

Public Function CSVList(rng As Range) As String
    Dim r As Range, ary1, ary2
    For Each r In rng
        v = r.Text
        If InStr(1, v, "-") = 0 Then
            CSVList = CSVList & "," & v
        Else
            ary = Split(v, "-")
                For i = CLng(ary(0)) To CLng(ary(1))
                    CSVList = CSVList & "," & CStr(i)
                Next i
        End If
    Next r
    CSVList = Mid(CSVList, 2)
End Function

or:

EDIT#1:

This UDF treats any non-numeral as a separator:

Public Function CSVList2(rng As Range) As String
    Dim r As Range, ary1, ary2, L As Long, i As Long, C As String
    For Each r In rng
        v = r.Text
        L = Len(v)
        C = ""
        For i = 1 To L
            If Not Mid(v, i, 1) Like "[0-9]" Then C = Mid(v, i, 1)
        Next i

        If C = "" Then
            CSVList2 = CSVList2 & "," & v
        Else
            ary = Split(v, C)
                For i = CLng(ary(0)) To CLng(ary(1))
                    CSVList2 = CSVList2 & "," & CStr(i)
                Next i
        End If
    Next r
    CSVList2 = Mid(CSVList2, 2)
End Function

edited May 18 '18 at 14:45

answered May 18 '18 at 14:09

Gary's Student

95,722
10
59
99

@Vyach - Not sure if it's intentional, but that's not a `-` between those dates, that's a different symbol `–` , perhaps an *em-dash*. To make Gary's code work you'll need to change that either in the code or on your end (unless it was just a typo). – ashleedawg May 18 '18 at 14:20
1

@ashleedawg or the code could easily be changed to treat **any** non-numeric single character as the separator. – Gary's Student May 18 '18 at 14:23
@ashleedawg See my **EDIT#1** – Gary's Student May 18 '18 at 14:46
Wow, this is magic! It doesn't handle a cell like "1865-1868, 1870, 1880", but I'll split such cells into multiple by "," Thanks a lot! – Vyacheslav Butenko May 18 '18 at 15:13

Making a list from range in Excel

1 Answers1