How to Paste Comma Separated Values in Google Sheets

While working on a dataset sometimes we get different types of data, and we get to modify that data in suitable ways. If you paste any value from somewhere else and the value is combined with a comma, semi-colon or any other sign then separating that value manually is very time-consuming and tiring. Separating all the combined values using different methods and functions is relatively easy in google sheets. Here, we will learn how to paste comma separated values in google sheets. So, let’s start.

Here is the overview of this article. You will learn more after you go through the total article.

Table of Contents

A Sample of Practice Spreadsheet

You may copy the spreadsheet below and practice by yourself.

Paste Comma Separated Values

2 Simple Ways to Paste Comma Separated Values in Google Sheets

The dataset below contains Student Name, Marks, Student ID, and Class which is combined with a comma. At first, the data is combined in a CSV file as below.

Now, we will copy the data from the CSV file and paste this into our dataset so that, we can execute the process.

1. Using Split Text to Column Method

here we will learn how to use Split Text to Column method to paste comma separated values using the following dataset. Now, follow step by step to execute this method.

📌 Steps:

First, select range B4:B11 along with the header row. So, the header row also separates.

Then, select Data from the ribbon >> Split text to columns tool to separate the below values.

Now, separate the values with the separator Comma.

After that, the final result will be as below. The values will be separated with every comma as below.

2. Applying SPLIT Function

Now, we will use the SPLIT function to separate these values using the same dataset. Follow the steps below to execute the formula.

📌 Steps:

Initially, Select cell C5 and enter the function as below.

Moreover, select cell B5 as the lookup value so that function can get the actual value from this cell.

Consequently, write (,) manually and enclose with a double quote. to separate the value every time if there is a comma.

In the end, write down true Manually to return the value if the value is right and executes this formula.

=SPLIT(B5,",",true)

Now, drag down the fill handle and copy the same formula into every dataset cell.

Things to Remember

Always select the dataset range first while implementing the Split text to column.
Split text to column tool will split the actual dataset and only show the separated value after executing the method.

Conclusion

In this article, we explained how to paste comma separated values in Google Sheets with simple and practical examples. Hopefully, the examples will help you to apply this method to your own dataset. Please let us know in the comment section if you have any further queries or suggestions. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.