STARTSWITH function

Returns whether a text string starts with another text string. STARTSWITH("David", "D")STARTSWITH("David"; "D") returns TRUE, as "David" starts with "D".

The comparison is case-sensitive. To perform a case-insensitive comparison, first apply LOWER or UPPER to both parameters:

STARTSWITH(LOWER(TextField1), LOWER(TextField2))STARTSWITH(LOWER(TextField1); LOWER(TextField2))

This formula returns whether the value of TextField1 starts with the value of TextField2. As both parameters are made lower-case using LOWER before being passed to STARTSWITH, the comparison is case-insensitive.

Use CONTAINS to determine if a text string contains another text string and ENDSWITH to determine if a text string ends with another text string. Use REGEXMATCH to perform more complex text comparisons.

This function is specific to Calcapp.

STARTSWITH and arrays

This function also works with arrays:

STARTSWITH({ "Deborah", "David" }, "D")STARTSWITH({ "Deborah"; "David" }; "D")

This formula returns the array { TRUE, TRUE }{ TRUE; TRUE }, because both text strings start with "D".

To determine if all text strings start with another text string, use the AND function:

AND(STARTSWITH({ "Deborah", "David" }, "D"))AND(STARTSWITH({ "Deborah"; "David" }; "D"))

This formula returns TRUE. When AND is applied to an array, it returns TRUE only if all array elements are TRUE. As that is the case for { TRUE, TRUE }{ TRUE; TRUE }, AND returns TRUE.

Use OR to determine if at least one text string of an array starts with another text string:

OR(STARTSWITH({ "Ivan", "Deborah", "David" }, "D"))OR(STARTSWITH({ "Ivan"; "Deborah"; "David" }; "D"))

This formula returns TRUE, despite the fact that "Ivan" does not start with "D". OR is given the logical array { FALSE, TRUE, TRUE }{ FALSE; TRUE; TRUE }, returned by STARTSWITH, and returns TRUE because at least one element is TRUE.

Use SIZE and FILTER to determine the number of text strings starting with another text string:

SIZE(FILTER({ "Ivan", "Deborah", "David" }, STARTSWITH(Element, "D")))SIZE(FILTER({ "Ivan"; "Deborah"; "David" }; STARTSWITH(Element; "D")))

Above, FILTER is given the array { "Ivan", "Deborah", "David" }{ "Ivan"; "Deborah"; "David" } and returns an array where all elements start with "D", that is, { "Deborah", "David" }{ "Deborah"; "David" }. Finally, SIZE counts the number of elements of that array, yielding the result, 2.

Examples

STARTSWITH("David", "D")STARTSWITH("David"; "D")

Returns TRUE.

STARTSWITH(LOWER(TextField1), LOWER(TextField2))STARTSWITH(LOWER(TextField1); LOWER(TextField2))

Returns whether the value of TextField1 starts with the value of TextField2. The comparison is case-insensitive, as both parameters are made lower-case before being passed to STARTSWITH, using the LOWER function.

STARTSWITH({ "Deborah", "David" }, "D")STARTSWITH({ "Deborah"; "David" }; "D")

Returns the array { TRUE, TRUE }{ TRUE; TRUE }, because both text strings start with "D".

AND(STARTSWITH({ "Deborah", "David" }, "D"))AND(STARTSWITH({ "Deborah"; "David" }; "D"))

Returns TRUE, because all text strings start with "D". AND is given the array { TRUE, TRUE }{ TRUE; TRUE } from STARTSWITH, and returns TRUE, as all array elements are TRUE.

OR(STARTSWITH({ "Ivan", "Deborah" }, "D"))OR(STARTSWITH({ "Ivan"; "Deborah" }; "D"))

Returns TRUE, because at least one text string starts with "D". OR is given the array { FALSE, TRUE }{ FALSE; TRUE } from STARTSWITH, and returns TRUE, as at least one array element is TRUE.

SIZE(FILTER({ "ab", "ac", "e" }, STARTSWITH(Element, "a")))SIZE(FILTER({ "ab"; "ac"; "e" }; STARTSWITH(Element; "a")))

Returns 2, as two array elements ("ab" and "ac") start with "a". FILTER is asked to filter the array { "ab", "ac", "e" }{ "ab"; "ac"; "e" } using its second parameter. FILTER processes every array element in turn, passing it to its second parameter under the Element name, expecting to receive a logical value back which determines if the element should be included in the result array. The second parameter, STARTSWITH(Element, "a")STARTSWITH(Element; "a") invokes STARTSWITH, which only returns TRUE if the array element given to it starts with "a". As a result, FILTER returns the array { "ab", "ac" }{ "ab"; "ac" }, containing two elements. Finally, SIZE counts the number of elements, returning the number 2.