Если вы работаете с данными в Google Таблицах, наверняка сталкивались с ситуацией, когда числа «спрятаны» внутри текстовых строк. Например, у вас есть ячейка со значениями вроде “обед (20$)”, “метро (5,5$)”, и нужно быстро получить сумму всех чисел. Ручной расчёт здесь неэффективен, особенно если данных много. К счастью, есть способ автоматизировать процесс, даже если вы не знакомы с программированием. Давайте разберёмся, как это сделать.
Как извлечь числа из текста и сложить их
Для решения задачи потребуется комбинация трёх функций:
1. REGEXREPLACE – заменяет части текста по шаблону (регулярному выражению).
2. SPLIT – разбивает текст на части по указанному разделителю.
3. SUM – суммирует числа.
Предположим, исходный текст находится в ячейке A2. Вот формула, которая извлечёт и сложит все числа:
=SUM(SPLIT(REGEXREPLACE(A2; "[^d,]"; " "); " "))Разберём каждый шаг:
1. REGEXREPLACE(A2; “[^d,]”; ” “)
– Регулярное выражение [^d,] означает: «всё, кроме цифр (d) и запятых».
– Эти символы заменяются на пробелы. Например, из строки "обед (20$)" получится 20.
– Важно: Если в тексте есть точки (например, 5.5$), замените запятую в выражении на точку: [^d.].
2. SPLIT(…; ” “)
– Разбивает полученный текст по пробелам. Для примера выше это создаст массив ["20"; "5,5"].
3. SUM(…)
– Суммирует числа из массива. В нашем случае результат будет 25,5.
Учёт локализации: запятые против точек
Если ваша таблица использует точку как десятичный разделитель (например, в США), формула не сработает корректно для чисел с запятой. Чтобы это исправить, добавьте функцию SUBSTITUTE:
=SUM(SPLIT(SUBSTITUTE(REGEXREPLACE(A2; "[^d,]"; " "); ","; "."); " "))Здесь запятые заменяются на точки перед расчётом суммы. Учитывайте, что это не меняет исходные данные – преобразование происходит только внутри формулы.
Возможные ошибки и нюансы
Лишние пробелы в тексте. Если в исходной строке есть пробелы между цифрами (например, 1 000$), формула воспримет их как разделители. Чтобы этого избежать модифицируйте регулярное выражение:
=SUM(SPLIT(REGEXREPLACE(A2; "[^d,]|(d)s+(d)"; "$1$2"); " "))Это удалит все нецифровые символы, кроме запятых, и объединит числа, разделённые пробелами.
Текст с числами. Если в названиях категорий есть цифры (например, "продукты2 (10$)"), они тоже будут извлечены. Чтобы исключить такие случаи, уточните шаблон:
=SUM(SPLIT(REGEXREPLACE(A2; ".*?((d+,?d*)).*?"; "$1 "); " "))Это выражение ищет числа только внутри скобок.
Альтернатива: вывод формулы для проверки
Если хотите увидеть промежуточный результат (например, 20+5,5), используйте JOIN вместо SUM:
=JOIN("+"; SPLIT(REGEXREPLACE(A2; "[^d,]"; " "); " "))Это полезно, если нужно проверить, какие именно числа участвуют в расчёте.
Примеры для разных сценариев
Чтобы лучше понять, как работает формула, рассмотрите эти кейсы:
| Исходный текст | Формула | Результат |
| “кофе (3,5$)”, “транспорт (4)” | =SUM(…) | 7,5 |
| “аренда (100.50$)”, “интернет (30)” | =SUM(SUBSTITUTE(…)) | 130.5 |
Теперь вы знаете, как быстро извлекать и суммировать числа из текстовых строк в Google Таблицах. Главное – правильно настроить регулярное выражение под ваш формат данных и не забывать о локальных настройках. Если столкнётесь с неочевидными ошибками (например, числа не суммируются), проверьте:
1. Нет ли лишних символов в исходном тексте.
2. Совпадает ли десятичный разделитель в формуле с настройками таблицы.
Попробуйте применить эти формулы к своим данным – и вы сэкономите десятки минут ручной работы. Если остались вопросы или нужна помощь с нестандартным случаем, напишите в комментариях!