Извлекаем в EXCEL число из конца текстовой строки

Извлечем число из конца текстовой строки, например, из строки «Филатова123» получим «123».

Пусть текстовая строка Филатова123 находится в ячейке A1 . Чтобы извлечь число 123, расположенное справа, запишем формулу массива : =1*ПСТР(A1; ПОИСКПОЗ(ЛОЖЬ;ЕОШИБКА(1*ПСТР(A1;СТРОКА(ДВССЫЛ("A1:A"&ДЛСТР(A1)));1));0); 255)

Если число расположено в начале или середине значения, то формула работать не будет (см. файл примера ).

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

=1*ПСТР(A1;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(1*ПСТР(A1;СТРОКА(ДВССЫЛ("A1:A"&ДЛСТР(A1)));1));0);СЧЁТ(1*ПСТР(A1;СТРОКА(ДВССЫЛ("A1:A"&ДЛСТР(A1)));1)))

О построении этой формулы смотрите ]]> здесь ]]> . Это также формула массива - не забудьте нажать CRTL+SHIFT+ENTER .

Несколько чисел в текстовой строке (через WORD)

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

• скопируйте столбец с текстовыми строками, содержащие числа, в MS WORD;
• нажмите CTRL+H , т.е. вызовите инструмент Найти и Заменить ;
• В поле Найти введите ^$ (любая буква) или его выберите из меню (см. рисунок ниже);

• в поле Заменить на: оставьте пустым (если все числа в строке нужно вывести одним числом, т.е. текстовая строка 123Филато11в6а будет преобразована в 123116 ) или введите пробел (если в дальнейшем потребуется вывести числа в отдельные ячейки);
• нажмите ОК, буквы будут заменены пробелами или просто убраны;
• скопируйте столбец обратно в MS EXCEL.

Если требуется вывести полученные числа в отдельные ячейки, то используйте инструмент Текст-по-столбцам (мастер текстов) или материал статьи Разнесение текстовых строк по столбцам.

Несколько чисел в текстовой строке (через формулы, все числа склеиваются в одно)

Если в текстовой строке расположено несколько чисел, то для извлечения чисел можно вывести каждый символ текстовой строки в отдельную ячейку (см. файл примера, лист Общий случай ) для этого:

• подсчитайте количество букв в текстовой строке (ячейка А3 ) с помощью функции ДЛСТР() в ячейке B3 ;
• создайте табличку, состоящую из количества столбцов = количеству букв в текстовой строке;
• заголовкам столбцов присвойте порядковые номера ;
• в ячейку С3 введите формулу =ПСТР($A3;C$2;1) и протяните ее вправо, заполнив все столбцы.

Заменив формулу =ПСТР($A3;C$2;1) на =ЕСЛИ(ЕОШ(--ПСТР($A3;C$2;1));"";--ПСТР($A3;C$2;1)) можно вывести только числовые значения. Собрать все числовые значения в одну ячейку можно с помощью формулы =--Т(C7&D7&E7&F7&G7&H7&I7&J7&K7&L7&M7&N7&O7&P7&Q7) или =СЦЕПИТЬ(C7;D7;E7;F7;G7;H7;I7;J7;K7;L7;M7;N7;O7;P7;Q7)+0

Несколько чисел в текстовой строке (через формулы, все числа размещаются в разные ячейки)

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

• как в предыдущем примере каждый символ текстовой строки выводим в отдельную ячейку;
• с помощью функций СЖПРОБЕЛЫ() и ( СЦЕПИТЬ() или аперсанда &) выводим текстовую строку без букв (т.е. только числа), между числами - 1 пробел;
• с помощью функции ПОИСК() находим начальные позиции каждого числа;
• с помощью функции ПСТР(), ЛЕВСИМВ(), ПРАВСИМВ() выводим числа в отдельные ячейки.

Решение из файла примера позволяет извлекать от 2-х до 4-х чисел из текстовых строк длиной до 15 символов. При желании решение можно легко расширить на большее количество символов и чисел.

Совет: В статье Извлекаем число из начала текстовой строки приведено решение соответствующей задачи. В статье Извлекаем число из середины текстовой строки приведено решение соответствующей задачи.