Как извлекать числа из строки в Google Таблицах

Содержание:

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

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

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

Использование функций REGEX в Google Таблицах для извлечения числа из строки

Google Таблицы предоставляет набор удобных функций REGEX, которые включают такие функции, как REGEXEXRACT и REGEXREPLACE.

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

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

Функция REGEXEXTRACT

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

Синтаксис функции REGEXTRACT следующий:

REGEXEXTRACT (text; reg_exp)

Здесь,

text — это текст или строка, из которой вы хотите извлечь подстроку
reg_exp — регулярное выражение. Это строка поиска, которая должна соответствовать той части текста, которую вы хотите извлечь.

Если текстовая строка содержит более одной подстроки, соответствующей регулярному выражению, REGEXEXTRACT возвращает первую совпадающую подстроку.

Функция REGEXREPLACE

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

Синтаксис функции REGEXTRACT следующий:

REGEXEXTRACT(text, reg_exp, replacement)

Здесь,

text — это текст или строка, в которой вы хотите найти совпадающие подстроки
reg_exp — регулярное выражение. Это строка поиска, которая должна соответствовать той части текста, которую вы хотите заменить.
replacement — это текстовая строка, которая заменит все совпадающие экземпляры reg_exp в тексте

Что такое регулярное выражение?

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

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

Мета-персонаж	Что он представляет
^	Начало строки
$	Конец строки
.	Одиночный персонаж
+	Одно или несколько вхождений символа или строки
d	Числовая цифра
w	Письмо
	Пробел от специального символа
()	Последовательность символов, заключенная внутри него
[]	Любой из символов внутри него

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

Извлечение чисел из любой точки строки в Google Таблицах

Рассмотрим следующий список строк:

Попробуем извлечь числа из каждой из этих строк.

Выражение «d» представляет собой числовую цифру. Мы используем символ «», чтобы Google Таблицы понимали, что мы имеем в виду метасимвол «d», а не букву «d».

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

Другими словами, если вы хотите извлечь первое вхождение серии числовых цифр из строки, вы можете использовать следующую формулу (учитывая, что извлекаемая строка находится в ячейке A2):

=REGEXEXTRACT(A2,”d+”)

Примечание. Если в строке встречается более одного числа, приведенная выше формула вернет только первое число, как вы можете видеть в строке 4 на скриншоте ниже:

Извлечение чисел из начала строки в Google Таблицах

Могут быть случаи, когда вы хотите извлечь только числа в начале строки. Например, рассмотрим следующий список строк:

Попробуем извлечь только числа в начале каждой из этих строк.

Символ «^» используется, чтобы указать, что нам нужны только символы, начинающиеся со следующего выражения. Таким образом, выражение «^ d» представляет собой только цифру в начале данной строки.

Если вы хотите извлечь более одной числовой цифры из начала строки, вы можете использовать выражение ‘^ d +’.

Что произойдет, если строка вообще не начинается с числа?

В этом случае функция REGEXTRACT возвращает ошибку.

Чтобы обработать эту ошибку, вы можете обернуть функцию IFERROR вокруг функции REGEXEXTRACT. Затем эта функция может гарантировать, что если функция REGEXEXTRACT вернет ошибку, будет отображаться пустая ячейка.

Другими словами, если строка не начинается с числа, результатом будет пустая ячейка.

Поэтому, если вы хотите извлечь числа из начала строки, вы можете использовать следующую формулу (учитывая, что извлекаемая строка находится в ячейке A2):

=IFERROR(REGEXEXTRACT(A2,”^d+”),””)

Извлечение чисел из конца строки в Google Таблицах

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

Рассмотрим следующий список строк:

Попробуем извлечь только числа в конце каждой из этих строк.

Символ «$» используется, чтобы указать, что нам нужны только символы, оканчивающиеся на предыдущее выражение. Таким образом, выражение «d $» представляет собой только цифру в конце данной строки.

Если вы хотите извлечь более одной числовой цифры из конца строки, вы можете использовать выражение ‘d + $’.

Как и раньше, если ваша строка вообще не заканчивается числом, вы можете обработать ее с помощью функции IFERROR.

Поэтому, если вы хотите извлечь числа из конца строки, вы можете использовать следующую формулу (учитывая, что извлекаемая строка находится в ячейке A2):

= IFERROR (REGEXTRACT (A2; ”d + $”), ””)

Извлечение всех чисел в строке путем удаления текстовой части

Теперь давайте рассмотрим несколько частных случаев. Что делать, если у вас есть числа в разных частях текста, и вы хотите извлечь их и объединить в одно число?

Например, рассмотрим следующий список строк:

В каждой строке числа распределены с текстовыми символами между ними. Мы хотим извлечь числа в одном и том же порядке и объединить их в одно большое число.

В таких случаях лучше всего просто удалить все текстовые символы в строке! Для этого мы можем легко применить функцию REGEXREPLACE следующим образом:

= REGEXREPLACE (A2, ”[a-zA-Z]”, ””)

Приведенная выше формула удалит все алфавиты из строки в ячейке A2, оставив только числа.

Извлечение нескольких чисел из разных частей строки в отдельные столбцы

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

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

Рассмотрим тот же список строк:

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

=SPLIT(LOWER(A2);”abcdefghijklmnopqsrtuvwxyz”)

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

Вот что происходит, когда вы применяете формулу к нашему заданному списку строк:

Разделение частей числа на отдельные столбцы на основе шаблона

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

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

Теперь обратите внимание на закономерность в телефонных номерах.

Первые три цифры (кода города) заключены в круглые скобки.
Следующие три цифры (кода обмена) идут после закрывающей круглой скобки и перед дефисом «-».
Последние 4 цифры (номера абонента) идут сразу после дефиса.

Мы можем использовать этот шаблон, чтобы указать регулярное выражение, где каждая последовательность чисел (которая нам нужна в одном столбце) может быть представлена в скругленных скобках.

Чтобы указать, сколько символов должно быть в последовательности, мы можем использовать фигурные скобки. Это означает, что если мы хотим указать последовательность из 3 чисел, мы можем использовать выражение «(. {3})» или «(…)».

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

= REGEXTRACT (A2; "((. {3})) (. {3}) - (. {4})")

Вот что происходит, когда вы применяете формулу к нашему заданному списку строк:

Выводы

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

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

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

Надеемся, наши примеры оказались полезными.