Как вытащить последнее слово из ячейки эксель
Чтобы извлечь первое слово из строки, формула должна найти позицию первого символа пробела, а затем использовать эту информацию в качестве аргумента для функции ЛЕВСИМВ. Следующая формула делает это: =ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1) .
Эта формула возвращает весь текст до первого пробела в ячейке A1. Однако у нее есть небольшой недостаток: она возвращает ошибку, если текст в ячейке А1 не содержит пробелов, потому что состоит из одного слова. Несколько более сложная формула решает проблему с помощью новой функции ЕСЛИОШИБКА, отображая все содержимое ячейки, если произошла ошибка:
=ЕСЛИОШИБКА(ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1);A1) .
Если вам нужно, чтобы формула была совместима с более ранними версиями Excel, вы не можете использовать ЕСЛИОШИБКА. В таком случае придется обойтись функцией ЕСЛИ и функцией ЕОШ для проверки на ошибку:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));A1;ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1))
Извлечение последнего слова строки
Извлечение последнего слова строки — более сложная задача, поскольку функция НАЙТИ работает только слева направо. Таким образом, проблема состоит в поиске последнего символа пробела. Следующая формула, однако, решает эту проблему. Она возвращает последнее слово строки (весь текст, следующий за последним символом пробела):
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;" ";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"";"")))))
Но у этой формулы есть такой же недостаток, как и у первой формулы из предыдущего раздела: она вернет ошибку, если строка не содержит по крайней мере один пробел. Решение заключается в использовании функции ЕСЛИОШИБКА и возврате всего содержимого ячейки А1, если возникает ошибка:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;" ";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";"")))));A1)
Следующая формула совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));A1;ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;"";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))))
Извлечение всего, кроме первого слова строки
Следующая формула возвращает содержимое ячейки А1, за исключением первого слова:
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ":A1;1)) .
Если ячейка А1 содержит текст 2008 Operating Budget, то формула вернет Operating Budget.
Формула возвращает ошибку, если ячейка содержит только одно слово. Следующая версия формулы использует функцию ЕСЛИОШИБКА, чтобы можно было избежать ошибки; формула возвращает пустую строку, если ячейка не содержит более одного слова:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;1));"")
А эта версия совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));"";ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;1)))
Простая, на первый взгляд, задача с не очевидным решением: извлечь из строки текста последнее слово. Ну или, в общем случае, последний фрагмент, отделенный заданным символом-разделителем (пробелом, запятой и т.д.) Другими словами, необходимо реализовать реверсивный поиск (от конца к началу) в строке заданного символа и извлечь потом все символы справа от него.
Давайте рассмотрим традиционно несколько способов решения на выбор: формулами, макросами и через Power Query.
Способ 1. Формулы
Чтобы проще было понять суть и механику формулы, начнем немного издалека. Сначала увеличим количество пробелов между словами в нашем исходном тексте до, например 20 штук. Сделать это можно при помощи функции замены ПОДСТАВИТЬ (SUBSTITUTE) и функции повтора заданного символа N-раз - ПОВТОР (REPT) :
Теперь отрежем от конца получившегося текста 20 символов с помощью функции ПРАВСИМВ (RIGHT) :
Уже теплее, да? Осталось убрать лишние пробелы с помощью функции СЖПРОБЕЛЫ (TRIM) и задача будет решена:
В английской версии наша формула будет выглядеть, соответственно:
=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";20));20))
Надеюсь, понятно, что в принципе не обязательно вставлять именно 20 пробелов - подойдет любое количество, лишь бы оно было больше, чем длина самого длинного слова в исходном тексте.
Способ 2. Макрофункция
Задачу извлечения последнего слова или фрагмента из текста также можно решить с помощью макросов, а именно - написать функцию реверсивного поиска в Visual Basic, которая будет делать то, что нам нужно - искать заданную подстроку в строке в обратном направлении - от конца к началу.
Нажмите сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор макросов. Затем добавьте новый модуль через меню Insert - Module и скопируйте туда следующий код:
Теперь можно сохранить книгу (в формате с поддержкой макросов!) и воспользоваться созданной функцией в следующем синтаксисе:
=LastWord(txt ; delim ; n)
- txt - ячейка с исходным текстом
- delim - символ-разделитель (по умолчанию - пробел)
- n - какое по счету слово с конца необходимо извлечь (по умолчанию - первое с конца)
При любых изменениях в исходном тексте в будущем наша макрофункция будет "на лету" пересчитываться, как и любая стандартная функция листа Excel.
Способ 3. Power Query
Power Query - это бесплатная надстройка от Microsoft для импорта данных в Excel из практически любых источников и последующей трансформации загруженных данных в любой вид. Мощь и крутизна этой надстройки настолько велики, что Microsoft встроила все ее возможности в Excel 2016 по умолчанию. Для Excel 2010-2013 Power Query можно бесплатно скачать отсюда.
Наша задача по отделению последнего слова или фрагмента через заданный разделитель с помощью Power Query решается очень легко.
Сначала превратим нашу таблицу с данными в умную с помощью сочтания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) :
Затем загрузим созданную "умную таблицу" в Power Query с помощью команды Из таблицы / диапазона (From table/range) на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013):
В открывшемся окне редактора запросов на вкладке Преобразование (Transform) выберем команду Разделить столбец - По разделителю (Split Column - By delimiter) и затем останется задать символ-разделитель и выбрать опцию Самый правый разделитель, чтобы разрубить не все слова, а только последнее:
После нажатия на ОК последнее слово будет отделено в новый столбец. Ненужный первый столбец можно удалить, щелкнув по его заголовку правой кнопкой мыши и выбрав Удалить (Delete ) . Также можно переименовать оставшийся столбец в шапке таблицы.
Результаты можно выгрузить обратно на лист, используя команду Главная - Закрыть и загрузить - Закрыть и загрузить в . (Home - Close & Load - Close & Load to. ) :
И в итоге получаем:
Вот так - дешево и сердито, без формул и макросов, почти не касаясь клавиатуры :)
Если в будущем исходный список изменится, то достаточно будет правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 обновить наш запрос.
Вот бывает так: есть у Вас в ячейке некий текст. Допустим "Было доставлено кусков мыла 763шт.". Вам нужно из этого только 763 - чтобы можно было провести с этим некие математические действия. Если это только одна ячейка - проблем тут нет, а если таких ячеек пару тысяч? И к тому же все разные?
- Было доставлено кусков мыла 763шт.
- Всего пришло 34
- Тюбики - 54 доставлено
- и т.д.
Никакой зацепки для извлечения данных. Пару тысяч таких строк удалять вручную весьма утомительное занятие, надо сказать. Да еще и не быстрое.
Есть несколько вариантов решения подобной задачи.
СПОСОБ 1: не используем макросы
можно применить формулу массива, вроде такой:
=ПСТР( A1 ;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 ))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )))+1)
Три важных момента:
- Формула вводится в ячейку сочетанием клавиш Ctrl+Shift+Enter, т.к. является формулой массива. Подробнее про эти формулы читайте в статье: Что такое формула массива
- в таком виде формула работает с текстом, количество символов в котором не превышает 99. Чтобы расширить необходимо в формуле во всех местах заменить СТРОКА( $1:$99 ) на СТРОКА( $1:$200 ) . Т.е. вместо 99 указать количество символов с запасом. Только не увлекайтесь, иначе может получиться, что формула будет работать слишком долго
- формула не обработает корректно текст " Было доставлено кусков мыла 763шт., а заказывали 780 " и ему подобный, где числа раскиданы по тексту.
Теперь коротко разберем формулу на примере фразы: Было доставлено кусков мыла 763шт.
- в A1 сам текст, из которого необходимо извлечь числа: Было доставлено кусков мыла 763шт., а заказывали 780
- блок: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )))
вычисляет позицию первой цифры в ячейке - 29 - блок: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 ))
вычисляет позицию последней цифры в ячейке - 31 - в результате получается: =ПСТР( A1 ;29;31-29+1)
функция ПСТР извлекает из текста, указанного первым аргументом( A1 ) текст, начиная с указанной позиции(29) с количеством символов, указанным третьим аргументом(31-29+1) - И в итоге:
=ПСТР( A1 ;29;31-29+1)
=> =ПСТР( A1 ;29;2+1)
=> =ПСТР( A1 ;29;3)
=> 763
СПОСОБ 2: используем макросы
Самый главный недостаток метода при помощи формулы, приведенной выше - из текста " Было доставлено кусков мыла 763шт., а заказывали 780 " формула вернет не только числа, а и текст между первой и последней цифрой: 763шт., а заказывали 780 .
Решить же проблему извлечения цифр даже из такого текста при помощи VBA куда проще и гибче. Плюс можно не только цифры извлекать, но и наоборот - цифры удалить, а извлечь только текст. Ниже приведен код пользовательской функции, которая поможет извлечь из строки только числа либо только текст. Иными словами, результатом функции будет либо только текст, либо только числа.
Function Extract_Number_from_Text(sWord As String, Optional Metod As Integer) 'sWord = ссылка на ячейку или непосредственно текст 'Metod = 0 – числа 'Metod = 1 – текст Dim sSymbol As String, sInsertWord As String Dim i As Integer If sWord = "" Then Extract_Number_from_Text = "Нет данных!": Exit Function sInsertWord = "" sSymbol = "" For i = 1 To Len(sWord) sSymbol = Mid(sWord, i, 1) If Metod = 1 Then If Not LCase(sSymbol) Like "*7*" Then If (sSymbol = "," Or sSymbol = "." Or sSymbol = " ") And i > 1 Then If Mid(sWord, i - 1, 1) Like "*6*" And Mid(sWord, i + 1, 1) Like "*6*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If Else If LCase(sSymbol) Like "*[0-9.,;:-]*" Then If LCase(sSymbol) Like "*[.,]*" And i > 1 Then If Not Mid(sWord, i - 1, 1) Like "*4*" Or Not Mid(sWord, i + 1, 1) Like "*9*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If End If Next i Extract_Number_from_Text = sInsertWord End Function
Данный код необходимо поместить в стандартный модуль книги. После этого в мастере функций в категории Определенные пользователем (User Defined) будет доступна функция Extract_Number_from_Text , которую можно будет применять как обычную функцию на листе.
Для извлечения только чисел
=Extract_Number_from_Text( A1 ; 0)
или
=Extract_Number_from_Text( A1 )
Для извлечения только текста
=Extract_Number_from_Text( A1 ; 1)
Подробнее про создание пользовательских функции и их применении можно почитать в статье Что такое функция пользователя(UDF)?
Помимо функции пользователя решил выложить и вариант с использованием диалогового окна:
Выбрать ячейку или диапазон с текстом(Лист1! $A$2:$A$10 ) - здесь указывается диапазон с исходными значениями, из которого необходимо оставить только числа или только текст.
Выберите ячейку для вывода данных(Лист1! $A$2 ) - указывается одна ячейка, с которой начать вывод преобразованных значений. В качестве этой ячейки можно выбрать первую ячейку диапазона с текстом(исходного) если необходимо произвести изменения сразу в этих же ячейках(как на рисунке). Осторожнее с таким указанием, т.к. результат работы кода может быть не совсем таким, какой вы ожидали, а вернуть прежние данные уже не получится - если только не закрыть файл без сохранения изменений.
Оставить только цифры, Оставить только текст- думаю не надо пояснять. Здесь выбираем, что оставить в качестве результата.
Небольшое дополнение к использованию кода
В коде есть строка:
If LCase(sSymbol) Like "*[0-9.,;:-]*" Then
Данная строка отвечает за текстовые символы, которые могут встречаться внутри чисел и которые надо оставить(не удалять наравне с другими не числовыми символами). Следовательно, если какие-то из данных символов не нужны в конечном тексте - их надо просто удалить. Например, чтобы оставались исключительно числа(без запятых и пр.):
If LCase(sSymbol) Like "*8*" Then
если надо исключить из удаления помимо цифр точку(т.е. будут извлечены цифры и точка):
If LCase(sSymbol) Like "*[0-9.]*" Then
Число из текста и наоборот.xls (99,0 KiB, 17 007 скачиваний)
Простая, на первый взгляд, задача с не очевидным решением: извлечь из строки текста последнее слово. Ну или, в общем случае, последний фрагмент, отделенный заданным символом-разделителем (пробелом, запятой и т.д.) Другими словами, необходимо реализовать реверсивный поиск (от конца к началу) в строке заданного символа и извлечь потом все символы справа от него.
Давайте рассмотрим традиционно несколько способов решения на выбор: формулами, макросами и через Power Query.
Способ 1. Формулы
Чтобы проще было понять суть и механику формулы, начнем немного издалека. Сначала увеличим количество пробелов между словами в нашем исходном тексте до, например 20 штук. Сделать это можно при помощи функции замены ПОДСТАВИТЬ (SUBSTITUTE) и функции повтора заданного символа N-раз - ПОВТОР (REPT) :
Теперь отрежем от конца получившегося текста 20 символов с помощью функции ПРАВСИМВ (RIGHT) :
Уже теплее, да? Осталось убрать лишние пробелы с помощью функции СЖПРОБЕЛЫ (TRIM) и задача будет решена:
В английской версии наша формула будет выглядеть, соответственно:
=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";20));20))
Надеюсь, понятно, что в принципе не обязательно вставлять именно 20 пробелов - подойдет любое количество, лишь бы оно было больше, чем длина самого длинного слова в исходном тексте.
Способ 2. Макрофункция
Задачу извлечения последнего слова или фрагмента из текста также можно решить с помощью макросов, а именно - написать функцию реверсивного поиска в Visual Basic, которая будет делать то, что нам нужно - искать заданную подстроку в строке в обратном направлении - от конца к началу.
Нажмите сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор макросов. Затем добавьте новый модуль через меню Insert - Module и скопируйте туда следующий код:
Теперь можно сохранить книгу (в формате с поддержкой макросов!) и воспользоваться созданной функцией в следующем синтаксисе:
=LastWord(txt ; delim ; n)
- txt - ячейка с исходным текстом
- delim - символ-разделитель (по умолчанию - пробел)
- n - какое по счету слово с конца необходимо извлечь (по умолчанию - первое с конца)
При любых изменениях в исходном тексте в будущем наша макрофункция будет "на лету" пересчитываться, как и любая стандартная функция листа Excel.
Способ 3. Power Query
Power Query - это бесплатная надстройка от Microsoft для импорта данных в Excel из практически любых источников и последующей трансформации загруженных данных в любой вид. Мощь и крутизна этой надстройки настолько велики, что Microsoft встроила все ее возможности в Excel 2016 по умолчанию. Для Excel 2010-2013 Power Query можно бесплатно скачать отсюда.
Наша задача по отделению последнего слова или фрагмента через заданный разделитель с помощью Power Query решается очень легко.
Сначала превратим нашу таблицу с данными в умную с помощью сочтания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) :
Затем загрузим созданную "умную таблицу" в Power Query с помощью команды Из таблицы / диапазона (From table/range) на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013):
В открывшемся окне редактора запросов на вкладке Преобразование (Transform) выберем команду Разделить столбец - По разделителю (Split Column - By delimiter) и затем останется задать символ-разделитель и выбрать опцию Самый правый разделитель, чтобы разрубить не все слова, а только последнее:
После нажатия на ОК последнее слово будет отделено в новый столбец. Ненужный первый столбец можно удалить, щелкнув по его заголовку правой кнопкой мыши и выбрав Удалить (Delete ) . Также можно переименовать оставшийся столбец в шапке таблицы.
Результаты можно выгрузить обратно на лист, используя команду Главная - Закрыть и загрузить - Закрыть и загрузить в . (Home - Close & Load - Close & Load to. ) :
И в итоге получаем:
Вот так - дешево и сердито, без формул и макросов, почти не касаясь клавиатуры :)
Если в будущем исходный список изменится, то достаточно будет правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 обновить наш запрос.
Пусть текстовая строка Филатова123 находится в ячейке A1 . Чтобы извлечь число 123, расположенное справа, запишем формулу массива : =1*ПСТР(A1; ПОИСКПОЗ(ЛОЖЬ;ЕОШИБКА(1*ПСТР(A1;СТРОКА(ДВССЫЛ("A1:A"&ДЛСТР(A1)));1));0); 255)
Если число расположено в начале или середине значения, то формула работать не будет (см. файл примера ).
Общая формула, позволяющая извлечь число из начала, середины и конца текстовой строки посложнее и выглядит так:
О построении этой формулы смотрите ]]> здесь ]]> . Это также формула массива - не забудьте нажать 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 символов. При желании решение можно легко расширить на большее количество символов и чисел.
Совет: В статье Извлекаем число из начала текстовой строки приведено решение соответствующей задачи. В статье Извлекаем число из середины текстовой строки приведено решение соответствующей задачи.
Читайте также: