How to Split String into Array in Google Sheets (3 Easy Methods)

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.

overview of split string into array

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.

dataset used to split string into array in google sheets using split text to columns

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.

select 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.

select appropriate separator

  • Finally, you will see that the names are split into two columns. The results are always presented in the adjacent cells.

result after using text to columns to split string into array in google sheets

Read More: How to Split Text to Columns Based on Line Break in Google Sheets


Similar Readings


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.

datset used to split string into array using split function in google sheets

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.

select desired cell to input formula

  • Then, insert the ARRAYFORMULA function.

insert arrayformula function

  • Next, insert the SPLIT function.

insert 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.

insert range of the text to split

  • 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.

insert delimiter

  • Then, close the parenthesis to complete the formula. This is what the final formula looks like:
=ARRAYFORMULA(SPLIT(B5:B14," "))

final formula after using split function with arrayformula to split string into array

  • Finally, press ENTER to split the names.

final result after using split function to split string into array

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.

dataset used to split character into letters

Steps:

  • First, select cell D5.

select desired cell

  • Then, type the following formula:
=SPLIT(REGEXREPLACE(REGEXREPLACE(B4&"","(?s)(.{1})","$1"&CHAR(127)),"'","''"),CHAR(127))

formula used to split a string into character arrays in google sheets

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.

final result after using split, regexreplace and char functions to split a string into characters in google sheets

Note: The Split text to columns feature will split the Employee Names starting from the column where the names exist. The SPLIT function will split the names starting from the column next to the Employee Names.

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.


Related Articles

Maruf Niaz

Maruf Niaz

My Name is Niaz. As a regular Google Sheets user, I have a good understanding of the users' needs and bad experiences. In my articles, I will share my knowledge which I hope, will be helpful to you. My latest educational degree was BSc in Textile Engineering from Bangladesh University of Textiles.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo