Afrina
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.
A Sample of Practice Spreadsheet
You may copy the spreadsheet below and practice by yourself.
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.
Read More: How to Paste Space Separated Values in Google Sheets (2 Ways)
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.
