определенный текст из строк, URL-адреса из ссылок и т. д.

Следующая часть наших операций с текстом в электронных таблицах посвящена извлечению. Узнайте, как извлекать различные данные — текст, символы, числа, URL-адреса, адреса электронной почты, дату и время и т. д. — из разных мест в нескольких ячейках Google Таблиц одновременно.

Формулы Google Sheets для извлечения текста и чисел из строк
Способы извлечения данных из ячеек Google Sheets без формул

Формулы Google Sheets для извлечения текста и чисел из строк

Формулы в Google Sheets — это все. В то время как некоторые комбинации добавляют текст и числа и удаляют различные символы, некоторые из них также извлекают текст, числа, отдельные символы и т. д.

Извлечь данные по положению: первый/последний/средний N символов

Когда вы собираетесь извлекать данные из ячеек Google Sheets, самые простые функции — это LEFT, RIGHT и MID. Они получают любые данные по положению.

Извлечение данных из начала ячеек в Google Sheets

Вы можете легко вытащить первые N символов, используя функцию LEFT:

ВЛЕВО(строка,[number_of_characters])

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

Вот самый простой пример: вынесем коды стран из телефонных номеров:

Как видите, коды стран занимают 6 символов в начале ячеек, поэтому вам нужна формула:

=ЛЕВО(A2,6)

Кончик. ArrayFormula позволит получить сразу 6 символов из всего диапазона:

=Формуламассива(СЛЕВА(A2:A7,6))

Извлечение данных из конца ячеек в Google Sheets

Чтобы вытащить последние N символов из ячеек, используйте вместо этого функцию ПРАВО:

ВПРАВО(строка,[number_of_characters])

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

Давайте получим названия этих стран по тем же телефонным номерам:

Они занимают всего 2 символа, и это именно то, что я упоминаю в формуле:

=ВПРАВО(A2,2)

Кончик. ArrayFormula также поможет вам извлечь данные из конца всех ячеек Google Sheets сразу:

=Формуламассива(ПРАВО(A2:A7,2))

Извлечение данных из середины ячеек в Google Sheets

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

Это называется MID:

MID(строка, начало_в, длина_извлечения)

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

На примере тех же телефонных номеров найдем сами телефонные номера без кода страны и сокращения страны:

Поскольку коды стран заканчиваются 6-м символом, а 7-й — тире, я буду брать номера, начиная с 8-го знака. Всего я получу 8 цифр:

=СРЕДНЯЯ(A2,8,8)

Кончик. Заменив одну ячейку на весь диапазон и обернув ее в ArrayFormula, вы получите результат сразу для каждой ячейки:

=Формуламассива(MID(A2:A7,8,8))

Извлечь текст/числа из строк

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

Но Google Sheets не был бы Google Sheets, если бы не имел других функций, помогающих извлекать текст из строк.

Давайте рассмотрим несколько возможных способов, которые предлагают электронные таблицы.

Извлечь данные перед определенным текстом — ЛЕВАЯ + ПОИСК

Всякий раз, когда вы хотите извлечь данные, которые предшествуют определенному тексту, используйте ЛЕВАЯ + ПОИСК:

ОСТАВИЛ используется для возврата определенного количества символов с начала ячеек (слева от них)
ПОИСК ищет определенные символы/строки и получает их позицию.

Объедините их — и ЛЕВЫЙ вернет количество символов, предложенное ПОИСКОМ.

Вот пример: как вы извлекаете текстовые коды перед каждым «ea»?

Вот формула, которая поможет вам в подобных случаях:

=ВЛЕВО(A2,ПОИСК(“ea”,A2)-1)

Вот что получается в формуле:

ПОИСК(“воздух”,A2) ищет «ea» в A2 и возвращает позицию, в которой начинается «ea» для каждой ячейки — 10.
Итак, 10-я позиция – это место, где находится буква «е». Но так как я хочу, чтобы все было прямо перед «ea», мне нужно вычесть 1 из этой позиции. В противном случае также будет возвращено «е». Так что в итоге я получаю 9.
ОСТАВИЛ смотрит на A2 и получает первые 9 символов.

Извлечь данные после текста

Есть также средства, чтобы получить все после определенной текстовой строки. Но на этот раз ПРАВО не поможет. Вместо этого наступает очередь REGEXREPLACE.

Кончик. REGEXREPLACE использует регулярные выражения. Если вы не готовы иметь с ними дело, есть гораздо более простое решение, описанное ниже.

REGEXREPLACE(текст, регулярное_выражение, замена)

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

Итак, как вы используете его для извлечения данных после определенного текста — «ea» в моем примере?

Легко — по этой формуле:

=REGEXREPLACE(A2,”(.*)ea(.*)”,”$2″)

Позвольте мне объяснить, как именно работает эта формула:

А2 это ячейка, из которой я извлекаю данные.
“(.*)еа(.*)” мое регулярное выражение (или вы можете назвать его маской). Я ищу «ea» и помещаю все остальные символы в скобки. Есть 2 группы символов — все, что до «ea», — это первая группа (.*), а все, что после «ea», — вторая (.*). Вся маска заключена в двойные кавычки.
“2 доллара” то, что я хочу получить — вторую группу (отсюда ее номер 2) из предыдущего аргумента.

Кончик. Все символы, используемые в регулярных выражениях, собираются на этом специальная страница.

Извлечение чисел из ячеек Google Sheets

Что, если вы хотите извлечь только числа, когда их положение и все, что идет до и после, не имеет значения?

Маски (также известные как регулярные выражения) также помогут. На самом деле я возьму та же функция REGEXREPLACE и измените регулярное выражение:

=REGEXREPLACE(A2,”[^[:digit:]]”, “”)

А2 это ячейка, из которой я хочу получить эти числа.
“[^[:digit:]]” регулярное выражение, которое принимает все, кроме цифр. Этот символ ^caret делает исключение для цифр.
“” заменяет все, кроме числовых символов, на «ничего». Или, другими словами, убирает его целиком, оставляя в ячейках только цифры. Или извлекает числа 🙂

Извлечь текст, игнорируя цифры и другие символы

Аналогичным образом вы можете вынимать только буквенные данные из ячеек Google Sheets. Сокращение для регулярного выражения, обозначающего текст, называется соответственно — альфа:

=REGEXREPLACE(A2,”[^[:alpha:]]”, “”)

Эта формула берет все, кроме букв (AZ, az), и буквально заменяет их на «ничего». Или, говоря по-другому, выводит только буквы.

Способы извлечения данных из ячеек Google Sheets без формул

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

Извлечение различных типов данных с помощью надстроек Power Tools

Первый инструмент, который я хочу, чтобы вы знали, называется Extract. Он делает именно то, что вы искали в этой статье — извлекает различные типы данных из ячеек Google Sheets.

Удобные настройки

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

Запомнить второй пункт этой статьи с REGEXREPLACE и регулярными выражениями? Вот как это просто для надстройки:

Дополнительные опции

Как видите, есть несколько дополнительных опций (просто флажки), которые вы можете быстро включить/выключить, чтобы получить наиболее точный результат:

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

Извлечение различных типов данных

Не только Power Tools извлекает данные до/после/между определенными текстовыми строками и первыми/последними N символами; но он также выводит следующее:

Числа вместе с их десятичными знаками, сохраняющие разделители десятичных и тысячных знаков без изменений:
N символов, начиная с определенной позиции в ячейке.
Гиперссылки (текст + ссылка), URL-адреса (ссылка), адреса электронной почты.

Извлеките любую строку данных отовсюду

Также есть возможность настроить свой собственный точный шаблон и использовать его для извлечения. Извлечь по маске и его подстановочные знаки — * а также ? – Покажи фокус:

Например, вы можете вывести все, что находится между скобками, используя следующую маску:
Или получить те SKU, у которых в идентификаторах всего 5 цифр:
Артикул????? Или, как я показываю на скриншоте ниже, потяните все после каждого ‘ea’ в каждой ячейке: