Paste Comma Separated Values into Excel (in Different Orders)

While working in Excel we have to deal with a comma or space-separated data from a different source. Commonly, the file in CSV format has many delimited values like commas or space. When we import data from them into an Excel file, we can easily change the data into a beautiful array. In this article, we will demonstrate several easy and time-saving methods to paste comma-separated values into Excel.


Download Practice Workbook

You can download the practice workbook from the following download button.


2 Simple Methods to Paste Comma Separated Values into Excel

We have a sample CSV file having a record of different hurricane occurrences. Our sample data have three different columns of Month, Average, and Years from 2005-2015.

1. Paste Comma Separated Values into Excel from a CSV File Directly

Suppose, we have some data that are comma-separated in a CSV file. Now we are required to split the data into different columns. In the following method, we are going to demonstrate how to paste comma-separated values into Excel.

πŸ“Œ Steps:

  • Imagine, we have data in a CSV file like the image below:

data in a CSV file

  • Now, we will follow the steps to open a comma-separated CSV file with Excel: Right-mouse click on the CSV file >> Open with >> Excel.

Paste Comma Separated Values into Excel from a CSV File Directly

  • Note that comma-delimited data in CSV format after opening with Excel has been separated into multiple columns automatically.
  • After that just look at the below image where we marked the file in CSV format message in red mark.
  • And a cell C2 in the red box denotes that the data has been separated into multiple columns.

CSV format after opening with Excel

  • As we want data in xlsx format, we will copy the data from a CSV file opened with Excel like the image below:

copy the data from a CSV file opened with Excel

  • At this moment we will paste it into a blank Excel file and the data will look like the below image:

Paste Comma Separated Values into Excel from a CSV File Directly


2. Open CSV File in Text Editor, Copy Data, and Paste It into Excel

In this method, we will demonstrate another process of how a comma-separated dataset can be pasted into Excel.

πŸ“Œ Steps:

  • Firstly, we will copy the data from CSV file format by pressing Ctrl + C.

Open CSV File in Text Editor, Copy Data, and Paste It into Excel

  • Secondly notice that in cell B4, we have pasted the data just like the image below.

pasted the data

  • After pasting the data, all the data will be stored in a single column.

the data will be stored in a single column

  • Now we have to convert the data into a table. To do so, we will go through the steps: select your data in this case B4:B12 >> Data >> Data Tools >> Text to Columns.

convert the data into a table by text to column option

  • Convert Text to Columns Wizard- Step 1 of 3 pop-ups will appear. Now, we will mark the Delimited and click on Next.

mark the Delimited and click on the Next

  • After pressing on Next, Convert Text to Columns Wizard- Step 2 of 3 will appear and in this pop-up, we will mark the following: Tab, Comma, and Space and again click on Next.

mark the following: Tab, Comma, and Space and again click on Next

  • Again, in the pop-up of Convert Text to Columns Wizard- Step 3 of 3 we will select the General and select Finish.

select the General and select Finish

  • Now, the final data in a separate column will look like this.

the final data in a separate column


How to Convert Comma Separated String to List in Excel

Now, we will see how to convert comma-separated strings to a list after getting them pasted into Excel. Follow the steps below.

πŸ“Œ Steps:

  • After pressing Alt + F11 keys, the Microsoft Visual Basic for Applications window will appear.

How to Convert Comma Separated String to List in Excel

  • Now, in the newly opened module, we will insert the following code just like the image below:

In the newly opened module, we will insert the following code

Sub SplitAll()
Dim Yrng As Range
Dim Yrng1 As Range
Dim YCell As Range
Dim A As Long
Dim YAdrs As String
Dim YUp As Boolean
Dim YRet As Variant
On Error Resume Next
YAdrs = Application.ActiveWindow.RangeSelection.address
Set Yrng = Application.InputBox("Select a single column", "Pop-Up", YAdrs, , , , , 8)
Set Yrng = Application.Intersect(Yrng, Yrng.Worksheet.UsedRange)
If Yrng Is Nothing Then Exit Sub
If Yrng.Columns.Count > 1 Then
MsgBox "Don't select more than one column!", , "Pop-Up"
Exit Sub
End If
Set Yrng1 = Application.InputBox("Select list destination:", "Pop-Up", , , , , , 8)
Set Yrng1 = Yrng1.Range("A1")
If Yrng1 Is Nothing Then Exit Sub
YUp = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each YCell In Yrng
YRet = Split(YCell.Value, ",")
Yrng1.Worksheet.Range(Yrng1.Offset(A, 0), Yrng1.Offset(A + UBound(YRet, 1), 0)) = Application.WorksheetFunction.Transpose(YRet)
A = A + UBound(YRet, 1) + 1
Next
Application.ScreenUpdating = YUp
End Sub
  • After pressing F5 or Run a Pop-Up will appear like the image given below. At this moment, in Select a single column we will insert $B$5: $B$10 and then click OK.

How to Convert Comma Separated String to List in Excel

  • After that, a new pop-up will appear and there, we will insert a cell for the destination in this case that is C5.

How to Convert Comma Separated String to List in Excel

  • And the resulting image will be the below image.

the resulting image


Conclusion

Follow these steps and stages on the topic to paste comma-separated values into Excel. Moreover, you are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section of our blog ExcelDemy.

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, i am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo