How from Columns separated dataset, to a comma separated one?

Question

i need to separate the columns of my dataset into comma separated how con I do this? I need a dataset that looks like this:

@VBasic2008 here is what i get in the middle of the sheet: the first and second row are my tries with your function CONCAT. While i need something like the rightest part of the image.... like 6,40,45,52. Not all the values merged..

So i did it using CONCAT function but i had to manually compute that for each column i show how for the ones that eventually will need help (note i used ; instead of , beacause my excel seems not working with ,)

and this is finally the final output

Ok but what if I have a dataset of 1000 columns? This process need to be much much quicker than this. This is not optimized.

Answer 1

I have written several comments on your question which may be hard to follow. So I decided to make a full solution which actually a trick.

Note that Microsoft Excel tries to guess the data structure of the file content if the file is suffixed with .csv (extension). For that reason, whenever you open a .csv file, you get your data in columns instead of a single columns with comma separated values.

In order to achieve what you want, first, save your data as in the comma separated values (.csv) file format.

Then change your file extension from .csv to, i.e. .txt (text file) for example: if your file name is "data.csv", change it to "data.txt". Please make sure you see the file extension as csv before you change it because in some case you don't see the file extension; therefore when you rename it, it remains a csv file.

Note: If you don't see file extension, if you are on Microsoft Windows, follow this link.

Once you rename the file into txt file format, you can then open it in your Excel application by going to File -> Open -> then browse the txt file.

There you go and get what you one.

You don't need to code or use any functions to achieve that although you can choose to do so if you wish as it is also a good solution.

Answer 2

If you are looking for a formula solution

TEXTJOIN(", ", TRUE, Range)

where Range is the column span of expected values

---

Option Explicit

Sub CSV()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")

Dim LR As Long, xRow As Range
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
    For Each xRow In ws.Range("A1:A" & LR)
        xRow.Offset(0, 6).Value = WorksheetFunction.TextJoin(", ", True, xRow.Resize(1, 6))
    Next xRow
Application.ScreenUpdating = True

End Sub

Answer 3

CONCAT Excel UDF

I wrote this a while ago. In VBE add a module and copy the following code to it.

The Code

'*******************************************************************************
' Purpose:        Concatenates the values of cells of a specified range.
'                 Read only. String.
' Syntax:         CONCAT(Range, Separator, Row0Column1)
' Inputs:
'   Range         A valid range in any open workbook. Required.
'   Separator     A string to be used as a separator between the values.
'                 Default is the space character. Optional.
'   Row0Column1   If 0 it concatenates by row, if 1 then by column.
'                 Default is 0. Optional.
' Returns:        A string containing all the values of the specified range.
'*******************************************************************************
Function CONCAT(ByVal Range As Range, Optional Separator As String = _
    " ", Optional Row0Column1 As Long = 0) As String

'***************************************
  ' Variables
  Dim xdRowStart As Long, xdRowEnd As Long, xdRowCounter As Long
  Dim xdColumnStart As Long, xdColumnEnd As Long, _
      xdColumnCounter As Long
  Dim xdSep As String, xdString As String, xdCheckEmptyString As String
  Dim xdWS As Worksheet

'***************************************
  ' Values
  xdString = ""
  xdSep = Separator
  Set xdWS = Range.Worksheet
  xdRowStart = Range.Row
  xdRowEnd = xdRowStart + Range.Rows.count - 1
  xdColumnStart = Range.Column
  xdColumnEnd = xdColumnStart + Range.Columns.count - 1

'***************************************
  ' Determine concatenated direction: by row or by column
  Select Case Row0Column1
    Case 0
      GoTo ConcatenateByRow
    Case 1
      GoTo ConcatenateByColumn
    Case Else
      MsgBox "Row0Column1:" & vbCr _
        & "Ommit or use 0 for Concatenating by Row." & vbCr _
        & "Use 1 for Concatenating by Column."
      GoTo ConcatenateFinal
  End Select

'***************************************
  ' Concatenate by Row:
ConcatenateByRow:
  For xdRowCounter = xdRowStart To xdRowEnd
    For xdColumnCounter = xdColumnStart To xdColumnEnd
      If xdString = "" Then 'xdString is empty; all cells were empty so far
        xdCheckEmptyString = xdWS.Cells(xdRowCounter, xdColumnCounter)
        If xdCheckEmptyString <> "" Then 'Cell is not empty
          xdString = xdCheckEmptyString
        End If
       Else 'xdString is not empty
        xdCheckEmptyString = xdWS.Cells(xdRowCounter, xdColumnCounter)
        If xdCheckEmptyString <> "" Then 'Cell is not empty
          xdString = xdString & xdSep & xdCheckEmptyString
        End If
      End If
    Next xdColumnCounter
  Next xdRowCounter
  GoTo ConcatenateFinal

'***************************************
  ' Concatenate by Column:
ConcatenateByColumn:
  For xdColumnCounter = xdColumnStart To xdColumnEnd
    For xdRowCounter = xdRowStart To xdRowEnd
      If xdString = "" Then 'xdString is empty; all cells were empty so far
        xdCheckEmptyString = xdWS.Cells(xdRowCounter, xdColumnCounter)
        If xdCheckEmptyString <> "" Then 'Cell is not empty
          xdString = xdCheckEmptyString
        End If
       Else 'xdString is not empty
         xdCheckEmptyString = xdWS.Cells(xdRowCounter, xdColumnCounter)
         If xdCheckEmptyString <> "" Then 'Cell is not empty
           xdString = xdString & xdSep & xdCheckEmptyString
         End If
      End If
    Next xdRowCounter
  Next xdColumnCounter
  GoTo ConcatenateFinal

'***************************************
ConcatenateFinal:
  CONCAT = xdString
End Function
'*******************************************************************************

Usage in Excel

=CONCAT($A1:$G1,",") and copy down:

=CONCAT($A2:$G2,",")

Answer 4

What is the extension of the file? .xls, .txt or .csv?

If it is in .xls then you can simply open the file in Excel and then use the File->save as menu and then selecting the Comma Separated from the file type drop down.

If file has .csv extension and you are trying to open it in Excel then you will see columns even the file has commas in it. To verify that if the file is comma separated then simply open with notepad or other text editors to see the comma separated values.

If there is any other separator like colon : or other and want to replace with comma then simply use the find and replace option in notepad.

Answer 5

This is already separated by commas, so you just have to rename it to a .csv file.

in Windows Explorer, go to the ribbon and go to the 'view' tab and enable 'File Name Extensions'. Navigate to your file, right click and rename it to THEFILENAME.csv instead of THEFILENAME.xlsx

Now when you open this up in excel, it should have the grid.