In this chapter we will explore Text Functions. Functions for working with text allow you to manipulate strings of data: measuring the length of text, replacing parts of a string, combining multiple strings, and splitting text by a given delimiter. In this section, we will look at four key functions: LEN, SUBSTITUTE, CONCAT, and SPLIT.

Calculating Length

The LEN function returns the number of characters in a string, including spaces. This function is useful for analyzing the length of textual data, such as when checking whether entered text meets a character limit.

Syntax and Examples

LEN(text)

text - The string whose length will be returned.

Formula	Result	Comment
LEN("Hello, World!")	13	The function returns 13 because the string "Hello, World!" contains 13 characters, including the space and the comma.
LEN(":@ #&")	5	The string ":@ #&" consists of 5 characters, including spaces and special characters.
LEN(TRIM(" Extra spaces "))	12	After using the TRIM function to remove spaces from the start and end, the string length becomes 12 instead of 14.
LEN(UPPER("google sheets"))	13	The UPPER function converts the string to uppercase, but the length remains 13 because the number of characters hasn't changed.
LEN(CONCAT("Hello", "World"))	10	The CONCAT function combines the strings, and the length of the resulting string "HelloWorld" is 10 characters.

Substituting Text

The SUBSTITUTE function replaces all or some occurrences of a part of a string with another text. It is useful for correcting typos or replacing data, such as replacing outdated values with current ones.

Syntax and Examples

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

text_to_search - The text in which the search and replacement will occur;
search_for - The string to search for in text_to_search;
- search_for matches both parts of words and whole words; for example, searching for "vent" will also replace text in the word "eventual";
replace_with - The string that will replace search_for;
occurrence_number - [OPTIONAL] The instance of search_for within text_to_search to replace with replace_with. By default, all occurrences of search_for are replaced; however, if an occurrence number is specified, only the specified instance of search_for will be replaced.

Formula	Result	Comment
SUBSTITUTE("Hello World", "World", "Everyone")	Hello Everyone	Replaces the word "World" with "Everyone" in the string "Hello World".
SUBSTITUTE("banana", "a", "o")	bonono	Replaces all occurrences of "a" with "o" in the word "banana".
SUBSTITUTE("2020-01-01", "-", "/")	2020/01/01	Replaces hyphens with slashes in the date.
SUBSTITUTE("I love apples, apples are my favorite", "apples", "oranges", 1)	I love oranges, apples are my favorite	Replaces only the first occurrence of "apples" with "oranges", leaving the second one unchanged.
SUBSTITUTE("The cat and the cat sat on the mat", "cat", "dog", 2)	The cat and the dog sat on the mat	Replaces only the second occurrence of "cat" with "dog", leaving the first occurrence unchanged.

Concatenating Text

The CONCATENATE function combines two texts or strings into one. This is useful for joining data from different cells, such as last names and first names, or other pieces of information.

Syntax and Examples

CONCATENATE(string1, [string2, ...])

string1 - The initial strin;
string2 ... - [OPTIONAL] Additional strings to be appended.

Value (G)	Value (H)	Formula	Result	Comment
Alex	Jones	CONCATENATE("Hello", " World")	Hello World	Combines the two words with a space between them.
Everly	Hartman	CONCATENATE("Price: $", 50)	Price: $50	Combines text and a number to display a price.
250	61	CONCATENATE(G2, " ", G3)	Alex Jones	Combines the first and last names found in cells G2 and H2.
310	711	CONCATENATE(G3, " ", H3)	Everly Hartman	Combines the first and last names found in cells G3 and H3.
		CONCATENATE(G4, "-", H4, "-", 100)	250-61-100	Combines strings with a hyphen for formatting a code or number in cells G4, H4.
		CONCATENATE(G5, "-", H5, "-", 100)	310-711-100	Combines strings with a hyphen for formatting a code or number in cells G5, H5.

Splitting Text

The SPLIT function divides a string into multiple parts using a specified delimiter. This is useful for breaking down data, such as a list of words, dates, or complete sentences.

Syntax and Examples

SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

text - The text to be split;
delimiter - The character or characters used to split the text;
- By default, each character in the delimiter is treated separately, for example, if the delimiter is "the", the text will be split around the characters "t", "h", and "e". Set split_by_each to FALSE to disable this behavior;
split_by_each - [OPTIONAL - TRUE by default] Whether to split the text around each character in the delimiter;
remove_empty_text - [OPTIONAL - TRUE by default] Whether or not to remove empty text entries from the split result. By default, consecutive delimiters are treated as one (if TRUE). If FALSE, empty values are added between consecutive delimiters.

Formula	Result				Comment
SPLIT("Everly Hartman Anoe", " ")	Everly	Hartman	Anoe		The string is split into three words by space.
SPLIT("Apple,Orange,Banana", ",")	Apple	Orange	Banana		The text is split into three parts by comma.
SPLIT("2023/08/--/05", "/")	2023	8	--	5	The string is split by the "/" character, empty values are retained.
SPLIT("thegreatfox", "the", FALSE)	greatfox				The split occurs by the word "the", not by characters.
SPLIT("apple; banana;;; ;cherry;; ; date", "; ", TRUE, TRUE)	apple	banana	cherry	date	The string is split by the delimiter ";" and " ", ignoring empty values. Empty spaces between delimiters are not displayed in the result.

Everything was clear?

Thanks for your feedback!

Section 6. Chapter 8