Делаем всё что угодно с текстом в таблицах: гид по 29 текстовым функциям Google Sheets

В Google Таблицах можно работать не только с цифрами и формулами, но и с текстами. Ячейки таблицы поддерживают основное форматирование текста, поэтому в них можно писать даже небольшие заметки.

Особенно удобно использовать Google Sheets, когда нужно создать много маленьких текстовых блоков и провести с ними определенные действия: убрать пробелы, преобразовать символы или заменить одну часть текста на другую. Для этого необходимо использовать текстовые функции:

Коротко о функциях в Google Таблицах

Функции — это готовые формулы, с помощью которых можно обрабатывать данные. У каждой функции есть свои правила написания, такие правила принято обозначать термином синтаксис.

Вот как строится синтаксис любой функции:

• вначале всегда ставится знак «=»;
• затем пишется название функции;
• следом в круглых скобках идут аргументы — данные, с которыми нужно провести манипуляции, и условия, по которым это делать.

Чтобы получить результат, необходимо в пустой ячейке ввести функцию с аргументами и нажать Enter.

Теперь рассмотрим поближе функции Google Таблиц для работы с текстами. Для каждой функции укажем английское название, объясним правила написания и приведём примеры.

ТЕКСТ (TEXT)

Эта функция преобразует число или дату в текстовую строку. В дальнейшем её можно будет обработать текстовыми функциями, которые не работают с числами.

=ТЕКСТ(число; формат)

число — число или адрес ячейки с числом, которое необходимо преобразовать в текст;

формат — вид, к которому необходимо привести значение. Здесь можно указать, хотим ли мы видеть целые числа или дробные, нужно ли указывать день недели или месяц, в каком виде отображать время. 

У всех вариантов есть свои аббревиатуры, и от того, какую вы будете использовать, зависит формат вывода данных. Полный перечень всех этих вариантов можно найти в Справке по Google Таблицам, а мы дадим основные. 

Вот какие бывают варианты аргументов функции ТЕКСТ для дат:

Примеры использования функции ТЕКСТ

Если в аргументе функции указать, например, 000.00 — число будет приведено к дробному виду, при этом символов станет столько, сколько нулей указано в аргументе.

Если в аргументе функции указать ###,## — число также будет приведено к дробному виду, но недостающие символы не будут заменяться нулями.

Дату в текст можно преобразовать в том же виде, если важно сохранить прежнее написание:

Также вместо полной даты можно подставлять только название месяца или дня недели, например:

ЗНАЧЕН (VALUE) 

Это функция, обратная функции ТЕКСТ. Она преобразует текст или дату в число. Может быть полезна, если ячейки с цифрами случайно отформатировались как текстовые.

=ЗНАЧЕН (текст или дата)

ДЛСТР (LEN) 

Эта функция вычисляет длину строки: считает все знаки, включая пробелы и непечатные символы, такие как табуляция, разрыв строки, конец абзаца. Может быть полезной, например, при подготовке объявлений для контекстной рекламы или SMS-рассылок — там, где есть ограничение на длину сообщения, и необходимо считать символы.

=ДЛСТР(текст)

СЖПРОБЕЛЫ (TRIM)

Эта функция убирает лишние пробелы: в начале и в конце строки. Она часто применяется для очистки данных после выгрузки из CRM-систем и в случае, когда количество пробелов имеет существенное значение.

=СЖПРОБЕЛЫ(текст)

СЦЕПИТЬ (CONCATENATE)

Эта функция объединяет несколько текстовых строк в одну. Например, можно объединить в одной ячейке имя, отчество и фамилию клиента, записанные в разных столбцах.

=СЦЕПИТЬ(строка 1; строка 2)

В качестве аргумента можно указать адреса нескольких строк или диапазона — несколько смежных ячеек.

Обратите внимание, что значения в строках объединяются без пробелов. Если они необходимы, то добавьте их в формулу — вручную проставив пробелы после каждой строки.

JOIN

Объединить несколько строк в одну позволяет и функция JOIN. Чтобы строки не слипались в функции необходимо указать разделитель — любой набор символов. 

=JOIN(разделитель; столбцы или строки)

Обратите внимание, в качестве аргумента функции может быть либо строка, либо столбец. Если необходимо объединение данных из диапазона ячеек, тогда нужно указывать в функции несколько строк или столбцов.

TEXTJOIN

Более удобная функция для объединения строк, она может игнорировать пустые строки и объединять данные без лишних пробелов. 

=TEXTJOIN(разделитель; игнорирование_пустых_строк; текст)

разделитель — символ или набор символов, разделяющий строки текста;

игноирование_пустых_строк — можно написать ИСТИНА (TRUE), тогда функция проигнорирует пустые строки или ЛОЖЬ (FALSE), если пустые строки нужно включить в результат объединения;

текст — текст или диапазон ячеек для объединения, можно указать несколько диапазонов.

SPLIT

Эта функция разделяет текст по разным ячейкам. Её можно использовать, если в одну ячейку выгрузились два email-адреса, или мы хотим город, улицу, номер дома написать в каждой ячейке отдельно.

=SPLIT(текст; разделитель; [тип_разделителя]; [удаление_пустых_ячеек])

текст – текст, который необходимо разделить;

разделитель – символ, разделяющий слова в тексте;

тип_разделителя – указывает, следует ли разделять текст по каждому символу, содержащемуся в разделителе. Необязательный параметр, по умолчанию установлено значение ИСТИНА (TRUE);

удаление_пустых_ячеек – указывает, нужно ли удалить пустые ячейки, оставшиеся после разделения. Необязательный параметр, по умолчанию установлено значение ИСТИНА (TRUE).

UNIQUE

Эта функция убирает дубликаты строк, оставляя уникальные строки в указанном диапазоне. Часто используется, чтобы убрать повторяющиеся данные.

=UNIQUE(диапазон; [по_столбцам]; [точно_один_раз])

диапазон — набор строк, из которого необходимо убрать дубликаты;

по_столбцам — определяет, как фильтровать данные. Если нужно отфильтровать по столбцам, ставьте значение ЛОЖЬ (FALSE), а если по строкам — значение ИСТИНА (TRUE). По умолчанию в функции стоит значение ЛОЖЬ (FALSE), поэтому новые данные без дубликатов появляются в соседнем столбце;

точно_один_раз — определяет, нужно ли показывать только те записи, у которых нет копий. По умолчанию в функции указано значение ЛОЖЬ (FALSE) —поэтому функция демонстрирует только записи, в которых могли встречаться дубликаты. Если поставить значение ИСТИНА (TRUE), будут показаны только уникальные записи, встречающиеся лишь один раз.

ПРОПИСН (UPPER), СТРОЧН (LOWER), ПРОПНАЧ

Эти функции изменяют регистр символов. 

ПРОПИСН (UPPER) — позволяют писать все слова только прописными буквами. 

СТРОЧН (LOWER) — преобразует весь текст в строчные буквы. 

ПРОПНАЧ — делает прописной первую букву каждого слова. 

У этих функций простой синтаксис — необходимо указать правильное название функции и конкретную ячейку. 

Примеры

Если необходимо все слова сделать заглавными буквами, то используйте функцию:

=ПРОПИСН(текст)

Чтобы убрать заглавные буквы в тексте строки, подойдёт функция:

=СТРОЧН(текст)

Если хотите, чтобы каждое новое слово начиналось с большой буквы, то используйте функцию:

=ПРОПНАЧ(текст)

ГИПЕРССЫЛКА (HYPERLINK) 

Эта функция создает гиперссылку в ячейке. Обычно Google Таблицы сами делают активной ссылку в ячейке, но бывают ситуации, когда нужно сделать ссылкой конкретное слово или скрыть полный адрес сайта.

=ГИПЕРССЫЛКА(ссылка; [текст_ссылки])

ссылка — адрес конкретной страницы в интернете;

текст ссылки — слово, которое будет обернуто в ссылку. Если его не указать, то ссылкой будет сам URL.

DETECTLANGUAGE

Эта функция определяет язык текста и показывает его в виде кода из двух букв. Например, ru — русский, en — английский, de — немецкий.  

=DETECTLANGUAGE(текст_или_диапазон)

В качестве аргумента этой функции можно указать текст или ссылку на ячейки с текстом для определения языка.

GOOGLETRANSLATE

Эта функция переводит текст с одного языка на другой с помощью Google переводчика.

=GOOGLETRANSLATE(текст; [язык_оригинала; язык_перевода])

текст — текст, который нужно перевести. Можно указать сам текст в кавычках или адрес ячейки с текстом;

язык оригинала — двухбуквенный код языка. Его можно получить функцией DETECTLANGUAGE, о которой писали чуть выше, либо оставить значение по умолчанию: «auto». В этом случае язык, с которого будет сделан перевод, определится автоматически;

язык перевода — также двухбуквенный код, по умолчанию — язык операционной системы.

НАЙТИ (FIND) 

Функция ищет заданный фрагмент текста и показывает положение, с точностью до символа, где он впервые встречается. Работа этой функции зависит от регистра букв в аргументе. Эта функция обычно используется в комбинации с другими функциями Google Таблиц.

=НАЙТИ(что_ищем; где_ищем; [начиная_с])

что_ищем – фрагмент текста, который требуется найти;

где_ищем – текст, в котором будем искать заданный фрагмент текста;

начиная_с – номер символа в текстовой строке, с которого следует начинать поиск. По умолчанию стоит первый символ в строчке.

ПОИСК (SEARCH) 

Функция также показывает порядковый номер символа для заданного текстового фрагмента, но, в отличие от функции НАЙТИ, не зависит от регистра букв.

=ПОИСК(что_ищем; где_ищем; [начиная_с])

что_ищем – фрагмент текста, который требуется найти;

где_ищем – текст, внутри которого будем искать нужный фрагмент;

начиная_с – номер символа в текстовой строке, с которого следует начинать поиск — в случае, если нужно искать не по всей строке. Это не обязательный параметр, по умолчанию стоит 1 — это означает, что функция будет искать нужный фрагмент с самого начала строки.

ПЕЧСИМВ (CLEAN) 

Эта функция удаляет из строки непечатные символы — например, перенос строки или конец абзаца. Может быть полезной, если нужно найти и удалить лишние переносы строк в адресном блоке.

=ПЕЧСИМВ(текст)

АРАБСКОЕ (ARABIC), РИМСКОЕ (ROMAN)

Функции переводят числа, записанные римскими цифрами в арабские и наоборот.

=АРАБСКОЕ(римское_число)

ЛЕВСИМВ (LEFT)

Эта функция показывает заданное количество символов с начала строки. С помощью этой функции мы можем посмотреть, уместится ли заголовок в нужное количество знаков, или как обрежется тема письма в мобильных приложениях.

=ЛЕВСИМВ(текст; [число_символов])

текст – текстовая строка, из которой извлекается левая часть;

число_символов – количество символов, которое требуется извлечь из левой части строки. Не обязательный параметр, по умолчанию равен 1.

ПРАВСИМВ (RIGHT)

Эта функция вырезает символы с конца строки. Она может пригодиться при обработке телефонных номеров, когда часть записана в городском варианте, часть — в международном.

=ПРАВСИМВ(текст; число_символов; длина_подстроки)

текст – текстовая строка, из которой извлекается правая часть;

число_символов – номер символа с конца строки, начиная с которого требуется извлекать подстроку;

длина_подстроки — количество символов, которое требуется извлечь из строки. Если её не указать, будут извлечены все символы.

ПСТР (MID) 

Эта функция извлекает из строки подстроку заданной длины. Наряду с ЛЕВСИМВ и ПРАВСИМВ эту функцию также удобно применять для обработки адресов и номеров телефонов.

=ПСТР(где_ищем; [начиная_с]; длина_подстроки)

текст – текстовая строка, из которой извлекается левая часть;

начиная_с – количество символов, которое требуется извлечь из левой части строки;

длина_подстроки — количество извлекаемых символов.

ПОДСТАВИТЬ (SUBSTITUTE) 

Эта функция заменяет одну часть текста на другую.

=ПОДСТАВИТЬ(текст, текст_для_замены, заменить_на, [номер_вхождения])

текст – текст, в котором нужно заменить часть;

текст_для_замены – строка, которую нужно найти и заменить в тексте. При этом находятся не только слова, но и части слов;

заменить_на – строка, на которую заменяем «текст_для_замены»;

номер_вхождения – порядковый номер вхождения части текста для замены. По умолчанию заменяются все вхождения текста, но если же требуется замена только определенного фрагмента, то нужно указать порядковый номер вхождения..

ЗАМЕНИТЬ (REPLACE)

Функция заменяет в тексте выбранное количество символов.

=ЗАМЕНИТЬ(текст, позиция, количество, новый_текст)

текст – часть текста, которую нужно заменить;

позиция – порядковый номер символа, с которого начинается замена;

количество – количество заменяемых символов;

новый_текст – текст на замену.

СОВПАД (EXACT)

Функция проверяет, являются ли две строки идентичными. При этом должны совпадать все символы, регистр и непечатные символы.

=СОВПАД(строка1; строка2)

ПОВТОР (REPT)

Функция повторяет заданный текст несколько раз. Обратите внимание, функция не добавляет пробелы между фрагментами текста. При необходимости вручную добавьте пробел к тексту, который будет повторяться.

=ПОВТОР(текст; количество_повторов)

Имейте в виду, что количество повторов в этой функции не должно превышать 100.

КОДСИМВ (CODE), СИМВОЛ (CHAR)

Эти функции показывают номер символа в таблице Unicode и, наоборот, находят символ по номеру.

Чтобы узнать номер символа в таблице Unicode используйте функцию:

=КОДСИМВ(символ)

А если нужно провести обратное действие, то пригодится функция: 

=СИМВОЛ(номер_символа)