Maruf Niaz
Google Sheets has a couple of effective spreadsheet tools that allow you to split a string of text into an array of individual elements. Knowing how to perform this action can be beneficial in the organization and analysis of data in Google Sheets. In this article, we will guide you on how to split a string into an array in Google Sheets.
The above screenshot is an overview of the article, representing how we can split string into array in Google Sheets.
A Sample of Practice Spreadsheet
You can download the spreadsheet and practice the techniques by working on it.
3 Easy Methods to Split String into Array in Google Sheets
Method 1: Using Split Text to Columns Feature
Splitting text or string data into an array or separate parts can be done quickly and easily by using the Split text to columns feature of Google Sheets.
To accomplish this task, consider using the following dataset that contains the Employee Names of a company. We will split these names into First Name, and Last Name using the built-in Split text to columns feature in Google Sheets.
Steps:
- First, select the Employee Name cell range which you want to split. We select the range B5:B14 in our example and, go to Menu bar > Data > Split text to columns.
- Afterward, select the appropriate separator in the Separator dialog box. In our example, the names are structured as “First Name (Space) Last Name” so we select Space as the separator to split the names.
- Finally, you will see that the names are split into two columns. The results are always presented in the adjacent cells.
Read More: How to Split Text to Columns Based on Line Break in Google Sheets
Similar Readings
- How to Split Cell by Comma in Google Sheets (2 Easy Methods)
- [Solved!] Split Text to Columns Is Not Working in Google Sheets
- How to Split Address in Google Sheets (3 Easy Methods)
- Split a Cell in Google Sheets (9 Quick Methods)
Method 2: Applying SPLIT Function with ARRAYFORMULA
Another way to split a string into arrays is by using the SPLIT function with the ARRAYFORMULA function.
The SPLIT function allows you to split a string into an array using a specific delimiter. The ARRAYFORMULA function allows you to apply a function to a range of cells.
We use the same dataset we used for the previous method. We will use the SPLIT function together with the ARRAYFORMULA function to split these Employee Names into First Name, and Last Name.
Steps:
- First, go to the first cell of the table where you want to split the names. We go to cell C5 in our example.
- Then, insert the ARRAYFORMULA function.
- Next, insert the SPLIT function.
- After that, type the cell reference of the range of Names that you want to split. We type B5:B14 as the Names are in range B5:B14 in our example.
- Now, type the delimiter by which you want to split the names. In our example, we structure the Employee Names as “First Name (Space) Last Name” so we insert Space (” “) inside a quotation as the delimiter.
- Then, close the parenthesis to complete the formula. This is what the final formula looks like:
=ARRAYFORMULA(SPLIT(B5:B14," "))
- Finally, press ENTER to split the names.
Read More: How to Use QUERY with SPLIT Function in Google Sheets
Method 3: Applying SPLIT with REGEXREPLACE and CHAR Functions
We can use the SPLIT, REGEXREPLACE, and the CHAR functions together to break a word into its individual letters and characters. Breaking a word down into its individual characters may seem straightforward, however, it can be a challenging task when trying to automate this process using an application such as Google Sheets.
We use the following dataset for this method.
Steps:
- First, select cell D5.
- Then, type the following formula:
=SPLIT(REGEXREPLACE(REGEXREPLACE(B4&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))
Formula Explanation:
- CHAR(127)
The CHAR(127) works as a delimiter. In ASCII it is known as the Delete Character. It is virtually invisible and highly likely not to show up in any regular texts or strings.
- REGEXREPLACE(B5&””,”(?s)(.{1})”,”$1″&CHAR(127))
The inner REGEXREPLACE function replaces every character of the word with itself and combines it with a special character CHAR(127). A regular expression flag (?s) is also included that allows the dot (.) to match characters and new lines if there are multiple lines in the reference cell.
- REGEXREPLACE(REGEXREPLACE(B5&””,”(?s)(.{1})”,”$1″&CHAR(127)),”‘”,”””)
The outer REGEXREPLACE function handles any single quotes that might appear in the reference text. It replaces single quotes (‘) with double single quotes (‘’).
- SPLIT(REGEXREPLACE(REGEXREPLACE(B5&””,”(?s)(.{1})”,”$1″&CHAR(127)),”‘”,”””),CHAR(127))
The SPLIT function here uses CHAR(127) as a delimiter to separate the characters of the word here.
- Finally, press ENTER to get the required result.
Read More: How to Split Text to Columns Using Formula in Google Sheets
Conclusion
In this article, we have demonstrated methods to split a string into array in Google Sheets. Practice the techniques shown in order to fully grasp the concept. The goal of the article is to provide helpful information and guide you in your task.
Additionally, consider looking into other articles available on OfficeWheel to expand your understanding and skill in using Google Sheets.
