Excel – How to convert a Excel row to a single csv text cell (inverse of “Text to Columns)

This will be a little convoluted but I think it should work. Note, I am using Excel 2007 for this, so some steps may be a little different.

After importing your file (post step 9 above) perform the following:

1. Insert two columns between Duration and Amount
2. Select the data of the Duration column and go to Data > Text to Columns
3. Choose Delimited (OK) and check Other under Delimiters, entering a colon ":" in the box. Then press Finish.
4. The first of the two new columns should have numbers in it now.
5. Change the Format of the second new column to General.
6. In the second new column, in the first row under the header row, enter the formula =TIME(0,F2,G2) (where column F is the Duration column, column G is the first new column and row 2 is the first row beneath the headers)
7. Fill the formula down for each row of data.
8. Change the Format to display the data as desired (ex. as hh:mm:ss)
9. Copy and paste Values and Number Formats from the second new column into the Duration column.
10. Delete the two new columns.

This should produce output similar to thus:

This works:

Sub OpenCsvAsText(ByVal strFilepath As String)

    Dim intFileNo As Integer
    Dim iCol As Long
    Dim nCol As Long
    Dim strLine As String
    Dim varColumnFormat As Variant
    Dim varTemp As Variant

    '// Read first line of file to figure out how many columns there are
    intFileNo = FreeFile()
    Open strFilepath For Input As #intFileNo
    Line Input #intFileNo, strLine
    Close #intFileNo
    varTemp = Split(strLine, ",")
    nCol = UBound(varTemp) + 1

    '// Prepare description of column format
    ReDim varColumnFormat(0 To nCol - 1)
    For iCol = 1 To nCol
        varColumnFormat(iCol - 1) = Array(iCol, xlTextFormat)
        ' What's this? See VBA help for OpenText method (FieldInfo argument).
    Next iCol

    '// Open the file using the specified column formats
    Workbooks.OpenText _
            Filename:=strFilepath, _
            DataType:=xlDelimited, _
            ConsecutiveDelimiter:=False, Comma:=True, _
            FieldInfo:=varColumnFormat

End Sub

Usage:

OpenCsvAsText "C:\MyDir\MyFile.txt"

Comma-separated file is now open as Excel sheet with all columns formatted as text.

Note that @Wetmelon's wizard solution works just fine, but if you're opening many files then you may, like me, grow weary of, each time, scrolling to column 60 in order to Shift-Click it.

EDIT @GSerg states in the comment below that this "doesn't work" and "eats spaces and leading zeroes". I'll just quote the comment to the question, which is more descriptive:

For reasons unknown, even if you explicitly provide formats for all columns in VBA, Excel will ignore it if the file extension is CSV. As soon as you change the extension, that same code will yield the correct results.

So the code above "works", but gets killed by this ridiculous Excel behaviour. Whichever way you cut it, you're stuck having to change the extension to something other than ".csv", sorry! After that, you're home free.