L_TEXT2ARRAY

=L_TEXT2ARRAY(text_string, [dimension])

Download the Template

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

Description

The L_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)

=L_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 L_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 L_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.

/**
* Converts a single text string to a row (dim=1, default) or column (dim=2) of characters
*/
L_TEXT2ARRAY = LAMBDA(text,[dim],
LET(doc,"https://www.vertex42.com/lambda/text2array.html",
    dim,IF(ISOMITTED(dim),1,dim),
    array,MID(text,SEQUENCE(1,LEN(text)),1),
    IF(dim=2,TRANSPOSE(array),array)
));

Name: L_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)
)

L_TEXT2ARRAY Examples

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

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

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

Result: 2

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

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

See Also

COUNTCHAR