TEXT2ARRAY Function for Excel

=TEXT2ARRAY(text_string, [dimension])

Argument	Description	Example
text	A single text string such as "Hello" or "Vertex42"	"Hello World"
dimension	dimension=1 (default) returns a row, dimension=2 returns a column	1

Download the Template

In the template file, navigate to the General worksheet to see the TEXT2ARRAY function in action.

Description

The TEXT2ARRAY function is a convenience function for performing the common task of converting a text string to an array of characters. This is relatively simple to do, if you can remember how:

=MID(text,SEQUENCE(1,LEN(text)),1)

=TEXT2ARRAY(text,1)

Here are a couple of scenarios where this function may be useful:

Prior to Counting Characters: If you want to count the number of specific characters within text, converting the text first to an array can make this easier.
Excel Esports: Multiple cases in the past Excel Esports seasons have involved text manipulation where it is convenient to split text into an array.
Find Unique Characters: Wrap TEXT2ARRAY(text) within the UNIQUE function to return a list of all the unique characters in a text string.

Lambda Formula

This code for using TEXT2ARRAY in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Code to Create TEXT2ARRAY via the Name Manager

Name: TEXT2ARRAY
Comment: Converts a text string to a row (dim=1, default) or column (dim=2) of characters
Refers To:

=LAMBDA(text,[dim],
LET(doc,"https://www.vertex42.com/lambda/text2array.html",
    dim,IF(ISOMITTED(dim),1,dim),
    text_array,IF(COLUMNS(text)>1,TRANSPOSE(text),text),
    result,REDUCE("",SEQUENCE(ROWS(text_array)),LAMBDA(acc,i,
        LET(cell,INDEX(text_array,i,1),
            splitArray,IF(cell="","",REGEXEXTRACT(cell,".",1)),
            IF(i=1,splitArray,VSTACK(acc,splitArray))
        )
    )),
    cleaned,MAP(result,LAMBDA(_v,IF(ISNA(_v),"",_v))),
    IF(dim=2,TRANSPOSE(cleaned),cleaned)
))

The older version based on MID and LEN used the following:

...
splitArray,IF(cell="","",MID(cell,SEQUENCE(1,LEN(cell)),1)),
...

Named Function for Google Sheets

Not yet updated to work with arrays of text. TEXT2ARRAY fails with certain unicode characters (due to the LEN("😃")=2 bug). TEXT2ARRAYX was designed to work with characters that would otherwise cause problems for LEN, MID, etc.

The Google Sheets version works very differently from Excel when a cell contains a numeric value. The GS version actually uses the displayed value rather than the stored value. For example, if you have PI() formatted to display as 3.14, the result of TEXT2ARRAY will be {"3",".","1","4"} and if you have a date value displayed as "12/1/24" the result will be {"1","2","/","1","/","2","4"}.

Name: TEXT2ARRAY
Description: Convert a text string to a row or column of characters
Arguments: text, dimension
Function:

=LET(doc,"https://www.vertex42.com/lambda/text2array.html",
    dimension,IF(ISBLANK(dimension),1,dimension),
    array,ARRAYFORMULA( MID(text,SEQUENCE(1,LEN(text)),1) ),
    IF(dimension=2,TRANSPOSE(array),array)
)

Name: TEXT2ARRAYX
Description: Convert a text string to a row or column of characters
Requires: L_DROP (available via the template)
Arguments: string, dimension
Function:

=LET(doc,"https://www.vertex42.com/lambda/text2array.html",
  string,SUBSTITUTE(string,"","",1),
  dimension,IF(ISBLANK(dimension),1,dimension),
  new,SCAN(string,SEQUENCE(LEN(string)),LAMBDA(a,i,
    SUBSTITUTE(a,REGEXEXTRACT(a,"."),"",1)
  )),
  orig,VSTACK(string,new),
  next,L_DROP(orig,1,),
  array,ARRAYFORMULA(SUBSTITUTE(orig,next,"",1)),
  cleaned,FILTER(array,NOT(ISNA(array))),
  IF(dimension=2,cleaned,TRANSPOSE(cleaned))
)

TEXT2ARRAY Examples

Example 1

Convert "Hello World" to a 1x11 (row) array of characters.

Test: Copy and Paste this LET function into a cell
=LET(
    string, "Hello World",
    TEXT2ARRAY(string,1)
)

Result: {"H","e","l","l","o"," ","W","o","r","l","d"}

Example 2

Count the number of characters in a text string. Count the number of occurrences of the letter "o" within "Hello World" and use EXACT to make the comparison case sensitive. See the COUNTCHAR function for an easier method.

=LET(
    string, "Hello World",
    char, "o",
    SUM(--EXACT(char,TEXT2ARRAY(string)))
)

Result: 2

Example 3

Return a list of the UNIQUE characters in a text string. You should use dimension=2 for TEXT2ARRAY in this case.

=LET(
    string, "A1A4B4",
    UNIQUE(TEXT2ARRAY(string,2))
)

Result: {"A";"1";"4";"B"}

Example 4: Numeric Values

When text is a numeric value (regardless of the number formatting) the function returns the value stored in Excel as an array of separate digits. In this example I'm using ARRAYTOTEXT to quickly convert the array back to a text string (so I can copy/paste the result to this web page).

=ARRAYTOTEXT(TEXT2ARRAY( 42.3 ),1)
Result: {"4","2",".","3"}

=ARRAYTOTEXT(TEXT2ARRAY( PI() ),1)
Result: {"3",".","1","4","1","5","9","2","6","5","3","5","8","9","7","9"}

=ARRAYTOTEXT(TEXT2ARRAY( DATE(2024,1,1) ),1)
Result: {"4","5","2","9","2"}

Example 5: An Array of Strings

When text is a row or column vector of values, the function returns an array of rows (dim=1) or columns (dim=2) containing the strings in the vector split by character.

=ARRAYTOTEXT(TEXT2ARRAY( {"abc", 1234, "HI"} ),1)

Result:
{"a", "b", "c",  "";
 "1", "2", "3", "4";
 "H", "I",  "",  ""}

Note that NA() values are removed from the final array as the final step in lambda function, to replace NA() values with empty strings.

Revision History

12/10/2024: Added ability for the text parameter to be a row or column vector of text values.
12/16/2024: Updated the formula (on this page, but not yet in the template file) to use REGEXEXTRACT instead of LEN because LEN can return the wrong answer for some unicode characters such as 😃 (a bug in the LEN function).

References & Resources

Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.

TEXT2ARRAY