Как извлечь последнее слово из ячейки в Excel

Обновлено: 21.11.2024

Часто мы сталкиваемся с ситуацией, когда нам нужно разделить имена (получить первое и последнее слово из ячейки/текстовой строки) в Excel.

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

Нам нужна функция, которая может извлечь это.

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

Сегодня в этом посте я хотел бы поделиться с вами тем, как получить отдельные имена с помощью формулы

Извлечь имя

Извлечение первого слова из текстовой строки намного проще, чем извлечение последнего слова. Для этого мы можем создать формулу, объединив две разные текстовые функции: ПОИСК и ВЛЕВО.

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

И формула для получения первого имени из приведенного выше столбца:

Это просто возвращает имя, которое является первым словом в тексте.

Как это работает

В приведенном выше примере мы использовали комбинацию ПОИСК (он может искать текстовую строку в другой текстовой строке) и ЛЕВЫЙ (он может извлекать текст с левой стороны). Эта формула работает в двух разных частях.

В первой части функция ПОИСК находит позицию пробела в тексте и возвращает число, из которого вы вычли единицу, чтобы получить позицию последнего символа первого слова.

Во второй части, используя число, возвращенное функцией ПОИСК, функция ВЛЕВО извлекает первое слово из ячейки.

Извлечь фамилию

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

Итак, чтобы извлечь последнее слово из ячейки, вам нужно объединить ПРАВИЛЬНО, ПОДСТАВИТЬ и ПОВТОРИТЬ. И формула будет такой:

=ОТРЕЗАТЬ(ПРАВО(ПОДСТАВИТЬ(A2", ",ПОВТОР(" ",ДЛСТР(A2))),ДЛСТР(A2)))

Эта формула возвращает фамилию из ячейки, которая является последним словом, и работает одинаково, даже если в ячейке более двух слов.

Как это работает

Опять же, вам нужно разобрать эту формулу, чтобы понять, как она работает.

В ПЕРВОЙ части функция ПОДСТАВИТЬ заменяет каждый пробел пробелами, равными длине текста.

Текст становится вдвое длиннее с дополнительными пробелами и выглядит примерно так.После этого функция ПРАВИЛЬНО будет извлекать символы с правой стороны, равные исходной длине текста.

В конце функция TRIM удаляет из текста все начальные пробелы.

Заключение

Используя приведенные выше формулы, вы можете легко получить первое и последнее слово из текстовой строки.

Формула для первого слова очень проста, а для последнего слова немного сложна, но вам нужно понять ее один раз в начале.

Надеюсь, этот совет был вам полезен.

Теперь скажи мне одну вещь. У вас есть другой способ получить первое и последнее из строки? Пожалуйста, поделитесь со мной своим мнением в разделе комментариев, я хотел бы услышать от вас. И, пожалуйста, не забудьте поделиться этим советом с друзьями.

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

Извлечь последнее слово

Если бы мы хотели извлечь первое слово ячейки, мы могли бы просто использовать функцию НАЙТИ, чтобы найти первый пробел, и функцию ВЛЕВО, чтобы вывести слово перед пробелом.

К сожалению, в Excel нет обратной функции НАЙТИ, поэтому вместо этого нам потребуется разработать другое решение:

Мы будем использовать функцию ПОДСТАВИТЬ, чтобы заменить пробелы между словами большим количеством (n) пробелов.
Мы будем использовать ПРАВИЛЬНУЮ функцию, чтобы вычислить нужное количество пробелов. (сюда будет входить наше слово, а также ряд дополнительных пробелов)
Мы воспользуемся функцией TRIM, чтобы обрезать лишние пробелы, оставив только последнее слово.

Вот формула, которую мы будем использовать:

Обратите внимание, что для этого примера мы выбрали n=10. Это число слишком маленькое: ячейка C6 не содержит полного последнего слова, так как оно состоит из 11 символов.

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

Теперь пройдемся по формуле:

Функция ПОВТОР

Функция REPT повторяет символ (n) раз. Эта формула создаст 10 пробелов:

Здесь, чтобы показать, для кого будет работать эта функция, мы заменили пробелы дефисами (-):

Функция ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ находит все пробелы в текстовой строке и заменяет их 10 пробелами.

Формула, показанная выше в диапазоне ячеек C3:C9, извлекает последнее слово из соседней ячейки в столбце B.

Меня вдохновил комментарий Рика Ротштейна от 2012 года, когда я создал эту формулу.

Если ваши слова длиннее 200 символов, замените каждый экземпляр 200 в приведенной выше формуле на более высокое значение.

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

Объяснение формулы в ячейке C3

Шаг 1. Повторите пробел 200 раз

Функция ПОВТОР повторяет определенный текст заданное количество раз.

возвращает 200 объединенных пробелов.

Шаг 2. Замените пробел на 200 пробелов

Функция ПОДСТАВИТЬ заменяет каждый пробел в значении ячейки на 200 пробелов.

ЗАМЕНИТЬ(B3," ",ПОВТОР(" ",200))

и возвращает "Мартин Уильямс".

(Я сократил строку по понятным причинам.)

Шаг 3. Извлеките 200 символов справа

Функция RIGHT извлекает 200 символов, начиная справа.

ВПРАВО(ЗАМЕНИТЬ(B3," ",ПОВТОР(" ",200)), 200)

ВПРАВО("Мартин Уильямс", 200)

и возвращает " Williams".Все символы пробела не отображаются по понятным причинам.

Шаг 4. Удалите начальные пробелы

Функция TRIM удаляет все начальные и конечные пробелы в строке или значении ячейки.

ОТРЕЗАТЬ(ПРАВО(ПОДСТАВИТЬ(B3, " ", ПОВТОР(" ", 200)), 200))

и возвращает "Вильямс" в ячейке C3.

2. Извлечь последнюю букву

Формула в ячейке D3 извлекает последнюю букву из символов в ячейке B3, значение содержит буквы, цифры и другие случайные символы.

Формула массива в ячейке D3:

2.1 Как ввести формулу массива

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

Скопируйте приведенную выше формулу массива.
Дважды нажмите левой кнопкой мыши на ячейку D3, появится подсказка.
Вставьте его в ячейку C3, нажав сочетание клавиш CTRL + V.
Одновременно нажмите и удерживайте клавиши CTRL + SHIFT.
Нажмите Enter один раз.
Отпустить все клавиши.

В строке формул отображаются начальная и конечная фигурные скобки, не вводите эти символы самостоятельно.

2.2 Объяснение формулы

Шаг 1. Поиск всех букв

Функция ПОИСК возвращает число, представляющее позицию символа, в которой найдена определенная текстовая строка, читаемая слева направо. Это не чувствительный к регистру поиск.

Шаг 2. Замените значения ошибок пробелами

Функция MAX не может обрабатывать значения ошибок, мы должны позаботиться о них. Функция ЕСЛИОШИБКА может заменить значения ошибки заданным значением.

Шаг 3. Вычисление максимального числа в массиве

Функция MAX возвращает наибольшее число в диапазоне ячеек или массиве.

Шаг 4. Извлеките последнюю букву

Функция MID возвращает подстроку из строки на основе начальной позиции и количества символов, которые вы хотите извлечь.

MID("12 Willia3 3m s2 ", 16, 1)

и возвращает "s" в ячейке D3.

3. Извлечь последнюю цифру в ячейке

На изображении выше показана формула массива в ячейке D3, которая извлекает последнюю цифру из ячейки B3.

Формула массива в ячейке D3:

3.1 Объяснение формулы

Шаг 1. Поиск всех цифр

Шаг 2. Замените значения ошибок пробелами

Функция ЕСЛИОШИБКА может заменить значение ошибки заданным значением.

Шаг 3. Вычисление максимального числа в массиве

Функция MAX возвращает наибольшее число в диапазоне ячеек или массиве.

Шаг 4. Извлечение последней буквы

MID("12?Wi:llia3 3m_s ", 11, 1)

и возвращает "s" в ячейке D3.

Получить файл Excel *.xlsx

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.

Статьи по теме

Формула, показанная выше в диапазоне ячеек D3:D9, извлекает слово на основе его позиции в значении ячейки. Например, […]

В этой статье блога описывается, как разделить строки в ячейке с помощью пробела в качестве символа-разделителя, например, текст на […]

Формула массива в ячейке C3 извлекает первый символ из имени, отчества и фамилии. Формула работает нормально […]

В этой статье демонстрируется формула, которая отфильтровывает уникальные отдельные цифры из диапазона ячеек, содержащих числа. Диапазон ячеек B3:B6 […]

В этой статье демонстрируется формула массива, которая возвращает заголовок таблицы на основе условия. Например, в ячейке C8 […]

Функция ПОВТОР повторяет определенный текст заданное количество раз. Формула в ячейке D3: =REPT(B3,C3) Содержание […]

Функция ПРАВИЛЬНО извлекает определенное количество символов, всегда начиная справа. Синтаксис функции Excel RIGHT(text,[num_chars]) Текст аргументов […]

Вопрос. Как подсчитать, сколько раз слово встречается в диапазоне ячеек? В нем нет […]

Содержание Подсчет определенной текстовой строки в ячейке Подсчет текстовой строки в диапазоне (с учетом регистра) Подсчет […]

Вопрос. Как подсчитать, сколько раз текстовая строка встречается в столбце? Текстовая строка может […]

На рисунке выше показаны значения, которые имеют несколько начальных и конечных пробелов, а иногда также несколько пробелов между словами. […]

Оставить ответ

Как комментировать

Как добавить формулу в комментарий
Вставьте сюда формулу.

Преобразование знаков меньше и больше
Использование символов HTML вместо знаков меньше и больше.
становится >

Как добавить код VBA в комментарий
[vb 1="vbnet" language=","]
Поместите здесь код VBA.
[/vb]

Формула Excel для удаления первого или последнего слова из ячейки.

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

Разделы:

Удалить первое слово из ячейки

Предполагается, что текст находится в ячейке A1, при необходимости измените таблицу.

Символы без пробелов

Если в ваших словах используются тире или какой-либо другой разделитель, просто замените " " любым разделителем. Если используется дефис, измените его на "-" в формуле.

Пояснение

Функция ПРАВИЛЬНО получает текст справа от ячейки. Это необходимо, потому что мы извлекаем весь текст, кроме первого слова; это означает, что нам нужно получить все, что находится справа от первого слова в ячейке.

Функции ДЛСТР и НАЙТИ используются, чтобы сообщить функции ПРАВИЛЬНО, сколько символов справа от ячейки мы хотим получить. Функция ПРАВИЛЬНО извлекает только определенное количество символов справа от ячейки.

Чтобы получить все, кроме первого слова, нам нужно найти пробел, который отделяет первое слово от остального текста. Используйте функцию НАЙТИ, ища пробел " ", чтобы получить количество символов слева от ячейки до первого пробела. В приведенном выше примере это возвращает 5. Это количество символов, которые мы хотим удалить из ячейки.

Чтобы получить число, которое мы хотим сохранить, нам нужно использовать функцию ДЛСТР для подсчета всех символов в ячейке. Результат функции ДЛСТР равен 12, поскольку в ячейке 12 символов.

В приведенной выше формуле мы вычитаем результат НАЙТИ из результата ДЛСТР, который равен 12-5 или 7.

Это указывает функции ПРАВИЛЬНО получить 7 символов справа от ячейки, что эффективно удаляет первое слово из ячейки.

Удалить последнее слово из ячейки

Предполагается, что текст находится в ячейке A1, при необходимости измените таблицу.

Примечание: если в ваших ячейках может быть символ ~, измените эту часть формулы на символ, которого не будет в ячейках.

Символы без пробелов

Пояснение

В этой формуле используются четыре функции: НАЛЕВО(), ДЛСТР(), НАЙТИ(), ПОДСТАВИТЬ().

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

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

Для этого нам нужно сообщить функции ВЛЕВО, сколько символов слева от ячейки мы хотим в новой ячейке, и это число может варьироваться. Сложная часть формулы — это то, что дает нам это число.

Эта часть формулы ПОДСТАВИТЬ(A1," ","") удаляет все пробелы в ячейке, а затем подсчитывает количество оставшихся символов с помощью функции ДЛСТР ДЛСТР(ПОДСТАВИТЬ(A1," ","")). Из приведенного выше примера результат равен 10.

Затем подсчитывается количество символов в ячейке, когда в ней остаются пробелы: LEN(A1). Результат равен 12.

Затем формула вычитает два вышеуказанных числа, чтобы получить 2 в этом примере. Это число представляет собой общее количество пробелов в ячейке, и оно сообщает другой функции ПОДСТАВИТЬ, какой пробел следует заменить; мы хотим заменить последний пробел, чтобы его было легче найти.

Используя только что полученное число, снова используется функция ПОДСТАВИТЬ, чтобы заменить последний пробел в ячейке на ~, а затем используется функция НАЙТИ, чтобы найти это ~, и затем формула знает, сколько символов слева от ячейка последний пробел.

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

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

Если вы все еще немного запутались, ознакомьтесь с нашим руководством по функции ПОДСТАВИТЬ в Excel

Более универсальные формулы — предотвращение ошибок

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

Чтобы избежать ошибок, мы можем использовать простую функцию ЕСЛИОШИБКА().

Формула 1:

Формула 2:

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

Прочитайте наш учебник, чтобы узнать больше о функции ЕСЛИОШИБКА в Excel и подавлении ошибок в целом.

Примечания

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

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

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

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

Извлечение последнего слова из ячейки в Excel — Извлечение текста с разделителем, определяемым пользователем — UDF
Макрос: эта UDF (определяемая пользователем функция) извлекает последнее слово или символы из ячейки в Excel.

Получить первое слово из ячейки в Excel
Учебник: Как использовать формулу для получения первого слова из ячейки в Excel. Это работает для одного c.

Извлечение первого слова из ячейки в Excel — Извлечение текста с разделителем, определяемым пользователем — UDF
Макрос: эта бесплатная функция Excel UDF (определяемая пользователем функция) возвращает первое слово из ячейки в Exce.

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

Удалить все пустые строки или пустые ячейки из диапазона в Excel
Учебник: Как быстро удалить все пустые ячейки или строки из диапазона в Excel. Это позволяет вам .

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
200+ руководств по Excel

Читайте также: