Jawadul Islam Chowdhury
Google Sheets is a powerful tool that allows users to organize, analyze, and visualize data. One common task when working with data in Google Sheets is splitting cells that contain multiple values separated by a delimiter, such as a comma.
The above image is the overview of the article split cell by comma in Google Sheets. In this article, you will learn the step-by-step procedure of different methods to split cell by comma in Google Sheets.
2 Suitable Methods to Split Cell by Comma in Google Sheets
There are several methods to split cell by comma in Google Sheets, including using the SPLIT function, and the Text to Columns feature.
Here is a dataset of the First Name, Last Name, Gender, and Birth Date of different persons. Now you need to split the cells using the comma. Below are the step-by-step procedures for the methods to split cells by comma in Google Sheets.
1. Using SPLIT Function
The SPLIT function is a built-in function in Google Sheets that allows you to split a cell’s contents into multiple cells based on a delimiter. Follow the below steps to split the cell by comma using the SPLIT function.
📌 Steps:
- In the beginning, select cell D5 and enter the following formula.
=SPLIT(B5,",")
- Press Enter and the output will look like the following image.
- Lastly, the output will be similar to the following image if you use the fill-handle tool to copy the formula to the following cells.
Read More: How to Use QUERY with SPLIT Function in Google Sheets
2. Utilizing Text to Columns Feature
Another way to split cells by comma in Google Sheets is by using the Text to Columns feature. This feature allows you to quickly and easily split cells into multiple columns based on a delimiter. Follow the steps below.
📌 Steps:
- First of all, select the whole dataset and select options Data >> Split text to columns.
- After that, a Separator menu will appear. Select Comma from the menu.
- Finally, the output will look like the following image.
Read More: How to Split Text to Columns Using Formula in Google Sheets
Similar Readings
- How to Split View in Google Sheets (2 Easy Ways)
- [Solved!] Split Text to Columns Is Not Working in Google Sheets
- How to Split Address in Google Sheets (3 Easy Methods)
How to Split First and Last String of a Cell by Comma in Google Sheets
The LEFT and FIND functions can be used to split the first content of a string that appears before the separator comma. Likewise, you can split the content after the separator comma using the RIGHT, LEN, and FIND functions. Now follow the below steps.
📌 Steps:
- First, select cell D5 and enter the following formula.
=LEFT(B5,FIND(",",B5)-1)
- Then, press Enter and the first content from cell B5 will be split into cell D5. Use the fill-handle tool to copy the formula to the following cells.
- Similarly, to split the content that appears after the comma, select cell E5 and insert the following formula.
=RIGHT(B5,LEN(B5)-FIND(",",B5))
- After that, press Enter, and the last content from cell B5 will be split into cell E5. Use the fill-handle tool to copy the formula to the following cells.
Read More: How to Split Text to Columns Based on Line Break in Google Sheets
How to Split Cells Vertically in Google Sheets
You can split the cells vertically using the ARRAYFORMULA, TRANSPOSE, and SPLIT functions. Follow the below steps to split cells vertically in Google Sheets.
📌 Steps:
- First, select cell E5 and input the following formula.
=ARRAYFORMULA(TRANSPOSE(SPLIT(B5:B7,",")))
- Now, hit Enter and the final output will look like the following image.
Read More: How to Split String into Array in Google Sheets (3 Easy Methods)
Things to Remember
Input the proper separator to split the cell.
Conclusion
Hopefully, now you can split cell by comma in Google sheets following the above methods. Please comment below with your doubts or suggestions regarding this article. Visit our website, OfficeWheel, for more articles related to Google Sheets.
