L_COUNTCHAR

=L_COUNTCHAR(char, within_text)

Download the Template

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

Description

L_COUNTCHAR will return the count of the instances of the exact character or string defined by char within the text string defined by within_text. The result is case sensitive.

This function allows you to input arrays for char and/or within_text. For example, you can return the count of char within each of the values of the array within_text.

=LET(
    char, "a",
    within_text, {"Allen Allred";"Abracadabra";"aaa"},
    L_COUNTCHAR(char,within_text)
)

Result: {0; 4; 3}

You can also return the count of each of the separate values in the char array within a single text string.

=LET(
    char, {"a";"e";"l"},
    within_text, "Allen Allred",
    L_COUNTCHAR(char,within_text)
)

Result: {0; 2; 4}

You can even return a 2D array by comparing a column array of char values with a row array of within_text values.

=LET(
    char, {"A";"2"},
    within_text, {"A1A2a3","2B|!2B","A2222"},
    L_COUNTCHAR(char,within_text)
)

Result: {2,0,1; 1,2,4}

Lambda Formula

This code for using L_COUNTCHAR 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.

/**
* Count the number of instances of a character or string within text
*/
L_COUNTCHAR = LAMBDA(char,within_text,
LET(doc,"https://www.vertex42.com/lambda/countchar.html",
    (LEN(within_text)-LEN(SUBSTITUTE(within_text,char,""))) / LEN(char)
));

Name: L_COUNTCHAR
Description: Count the number of instances of a character or string within text
Arguments: char, within_text
Function:

=LET(doc,"https://www.vertex42.com/lambda/countchar.html",
    ARRAYFORMULA((LEN(within_text)-LEN(SUBSTITUTE(within_text,char,""))) / LEN(char))
)

L_COUNTCHAR Examples

Example

Make a list of the UNIQUE characters in a text string and then count how many times each of those characters appears in the string. Return the result labeled {"Characters","Count"}

Test: Copy and Paste this LET function into a cell
=LET(
    within_text, "This is a sentence with seven words",
    unique_chars, UNIQUE(L_TEXT2ARRAY(within_text,2)),
    counts, L_COUNTCHAR(unique_chars,within_text),
    VSTACK({"Characters","Count"},HSTACK(unique_chars,counts))
)

See Also

TEXT2ARRAY