Содержание
Как получить число из любой позиции в тексте
Если ваша задача подразумевает извлечение числа из любого места строки, вы можете использовать следующую формулу:
=СУММПРОИЗВ(ПСТР(0&A2; НАИБОЛЬШИЙ(ИНДЕКС(ЕЧИСЛО(—ПСТР(A2; СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 1)) * СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 0); СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))))+1; 1) * 10^СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)))/10)
Где A2 — исходная текстовая строка.
Для пояснения, как это работает, потребуется отдельная статья. Поэтому вы можете просто скопировать на свой рабочий лист, чтобы убедиться, что это действительно работает 🙂
Обратите внимание, что в этом случае в тексте могут находиться несколько чисел. Все они будут извлечены и объединены в единое целое.
Однако, изучив результаты, вы можете заметить один незначительный недостаток: если исходный текст в ячейке не содержит числа, формула возвращает ноль, как в строке 7 на скриншоте выше. Чтобы исправить это, вы можете заключить формулу в оператор ЕСЛИ, который проверит, содержит ли исходный текст какое-либо число. Если это так, формула извлекает это число, в противном случае возвращает пустую строку:
=ЕСЛИ(СУММ(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;{«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»)))>0; СУММПРОИЗВ(ПСТР(0&A2; НАИБОЛЬШИЙ(ИНДЕКС(ЕЧИСЛО(—ПСТР(A2; СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 1)) * СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 0); СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))))+1; 1) * 10^СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)))/10);»»)
В отличие от всех предыдущих примеров, результатом этих формул является число. Чтобы убедиться в этом, просто обратите внимание на выровненные по правому краю значения в столбце B и усеченные ведущие нули (например, 88 вместо 088).
Если число, которое вы хотите извлечь, ограничено какими-то знаками-разделителями, то можно использовать функцию ПСТР. Рассмотрим пример, как получить номер счета из текста платежа.
Мы будем искать позицию знака «№» и позицию следующего за ним первого пробела. То, что находится между ними, как раз и будет номером счёта:
=ПСТР(ПОДСТАВИТЬ(A2;» «;»»);НАЙТИ(«№»;ПОДСТАВИТЬ(A2;» «;»»))+1;НАЙТИ(» «;A2;НАЙТИ(«№»;A2;1))-НАЙТИ(«№»;A2;1)-1)
Оптимальный вариант копируем последнюю формулу и вставляем в соседнюю ячейку
=ПСТР(ПОДСТАВИТЬ(A2;» «;»»);НАЙТИ(«№»;ПОДСТАВИТЬ(A2;» «;»»))+1;НАЙТИ(» «;A2;НАЙТИ(«№»;A2;1))-НАЙТИ(«№»;A2;1)-1)
Переносим формулу с изменением адреса ячейки на все ячейки столбца
Копируем все во все ячейки столбца, если есть такая необходимость, растягивая перекрестье:
