Как извлечь первые или последние два (или n) слова из текстовой строки?
При работе с большими объёмами текстовых данных в Excel часто требуется извлекать определённые слова из каждой ячейки — например, первые несколько слов, последние или те, что находятся на заданной позиции. Типичные сценарии включают выделение имён, адресов, ключевых слов или уникальных идентификаторов из ячеек с многословными записями, особенно когда элементы разделены пробелами. Допустим, у вас есть ячейка с предложением или кодом продукта, а вам нужно получить только первые три слова или последние два — для последующего анализа или обработки. Хотя в Excel нет встроенной функции для такой задачи, существует несколько гибких подходов к извлечению слов, каждый из которых подходит для разных объёмов данных и целей. В этой статье представлены эффективные формулы, удобные инструменты и дополнительные методы автоматизации, которые помогут вам легко извлекать первые, последние или n-е слово(а) из текстовой строки в Excel.
Извлечение первых двух или n слов из текстовой строки с помощью формул
Извлечение последних двух или n слов из текстовой строки с помощью формулы
Извлечение первого или n-го слова из текстовой строки с помощью удобной функции
Извлечение слов с помощью макроса VBA (первое, последнее или n-е слово)
Извлечение слов с помощью Power Query (разделение и объединение)
Извлечение первых двух или n слов из текстовой строки с помощью формул
Чтобы извлечь первые несколько слов из значения ячейки, вы можете использовать приведённые ниже формулы. Это особенно полезно, если у вас есть согласованный разделитель слов (например, пробел), и вы хотите, например, взять первые три слова из ячейки. Эти формулы легко применять, они хорошо работают с наборами данных умеренного размера и могут быть адаптированы для извлечения любого количества начальных слов.
Введите одну из приведённых ниже формул в пустую ячейку (например, C2), а затем перетащите маркер заполнения вниз, чтобы применить её ко всем нужным строкам. В результате вы увидите желаемое количество начальных слов, извлечённых из каждой текстовой строки, как показано на скриншоте ниже.
Пояснение и советы:
1. В приведённой выше примерной формуле A2 ссылается на ячейку, содержащую ваш оригинальный текст, а 3 — это количество слов, которые вы хотите извлечь. При необходимости измените эти ссылки в соответствии с вашими данными.
2. Если количество слов, которые вы хотите извлечь, варьируется от строки к строке и указано в столбце B, воспользуйтесь следующей гибкой формулой:
Эта формула позволяет динамически задавать количество извлекаемых слов, ссылаясь на ячейку (например, B2). Убедитесь, что в ячейке B2 указано корректное целое число.
3. Если ваш текст разделён другими разделителями (например, запятой или точкой с запятой), просто адаптируйте формулу, заменив символ пробела ()« ») на нужный вам разделитель.
4. Следите за начальными или конечными двойными пробелами, а также за несколькими последовательными пробелами — они могут повлиять на результат. Функция СЖПРОБЕЛЫ поможет вам удалить лишние пробелы.
5. Если общее количество слов в ячейке меньше указанного вами числа, формула вернёт весь текст без ошибки.
Извлечение последних двух или n слов из текстовой строки с помощью формулы
Чтобы извлечь последние слова из текстовой строки — например, последние два или любые последние n слов, — используйте следующую формулу. Этот метод идеально подходит для наборов данных, где регулярно требуется получать конечные слова, такие как фамилии, расширения файлов или идентификаторы в конце текстовой ячейки.
Скопируйте приведённую ниже формулу и вставьте её в пустую ячейку, куда хотите поместить результат. После ввода формулы перетащите маркер заполнения вниз, чтобы применить её к остальным строкам.
- A2: исходная ячейка с текстом.
- 3 — это количество последних слов, которые вы хотите извлечь. Чтобы получить последние 5 слов, просто замените 3 на 5 в формуле.
- Если в ячейке меньше слов, чем запрошено, формула вернёт все имеющиеся слова из неё.
- Если ваши данные разделены другим разделителем — например, запятой или табуляцией, — скорректируйте формулу соответствующим образом.
- Наличие сложных или несогласованных пробелов в данных может привести к неточным результатам — по возможности очистите текст перед применением формулы.
Извлечение первого или n-го слова из текстовой строки с помощью удобной функции
Если вам нужно извлечь только первое или любое конкретное (n-е) слово из текстовой строки, и вы предпочитаете решение без запоминания или редактирования сложных формул, воспользуйтесь Kutools для Excel. Функция Извлечь N-ое слово из ячейки позволяет легко выбрать слово на нужной позиции — идеально как для разового, так и для регулярного использования. Этот подход рекомендуется, если вы хотите быстрое, надёжное решение с управлением через меню, минимальным риском ошибок и без ручного ввода формул.
После установки Kutools для Excelвыполните следующие действия:
1. Выберите ячейку, в которой нужно отобразить результат извлечения, затем перейдите в меню Kutools > Помощник формул > Помощник формул, как показано на скриншоте:
2. В диалоговом окне Помощник формул выполните следующие шаги:
- Выберите Текст из раскрывающегося списка Тип формулы.
- Выберите Извлечь N-ое слово из ячейки из списка формул.
- Укажите исходную ячейку в поле Ячейка, а желаемую позицию слова (например, 2 для второго слова) — в поле N-е.
3. Нажмите OK, чтобы получить результат, затем перетащите маркер заполнения вниз, чтобы применить ту же логику ко всему списку. См. иллюстрацию:
Этот метод идеально подходит, когда требования к извлечению часто меняются или когда вместо создания пользовательских формул каждый раз нужен интерактивный интерфейс.
Извлечение слов с помощью макроса VBA (первое, последнее или n-е слово)
Для решения более сложных задач — например, динамического извлечения первого, последнего или любого указанного по счёту слова из текста, а также при работе с большими или часто обновляемыми наборами данных — макрос VBA предлагает значительно больше гибкости и автоматизации по сравнению с формулами. С его помощью вы можете создать многократно используемый инструмент, который быстро обрабатывает множество ячеек и легко настраивается под ваши требования для извлечения нужного слова или группы слов. Это особенно ценно, если вы регулярно выполняете однотипные текстовые операции или стремитесь избежать громоздких вложенных формул.
Типичные сценарии: Массовое извлечение данных из импортированного текста, пакетная очистка или подготовка стандартизированных отчётов.
1. Откройте редактор VBA, выбрав Разработчик > Visual Basic. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль и вставьте следующий код в новый модуль:
Sub ExtractWord()
Dim Rng As Range
Dim Cell As Range
Dim WordPos As Integer
Dim Words() As String
Dim ExtractedWord As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set Rng = Application.Selection
Set Rng = Application.InputBox("Select range to extract word from", xTitleId, Rng.Address, Type:=8)
WordPos = Application.InputBox("Enter which word number to extract (1=first,2=second, etc., or -1 for last)", xTitleId, 1, Type:=1)
Application.ScreenUpdating = False
For Each Cell In Rng
Words = Split(Application.Trim(Cell.Value), " ")
If WordPos = -1 Then
If UBound(Words) >= 0 Then
ExtractedWord = Words(UBound(Words))
Else
ExtractedWord = ""
End If
ElseIf WordPos >= 1 And WordPos <= UBound(Words) + 1 Then
ExtractedWord = Words(WordPos - 1)
Else
ExtractedWord = ""
End If
Cell.Offset(0, 1).Value = ExtractedWord
Next Cell
Application.ScreenUpdating = True
MsgBox "Extraction complete! Results placed in the column to the right.", vbInformation, xTitleId
End Sub2. Запустите макрос, нажав кнопку 
«Выполнить» или клавишу F5. Макрос предложит выбрать диапазон данных, запросит позицию извлекаемого слова (например, 1 — для первого слова, –1 — для последнего) и поместит результат в соседний столбец справа. Убедитесь, что в качестве разделителя используется пробел, либо при необходимости адаптируйте функцию Splitпод другие разделители.
Такой подход на основе VBA значительно ускоряет повторяющиеся массовые операции по извлечению данных и идеально подходит в тех случаях, когда решения на основе формул становятся слишком громоздкими. Примечание: всегда сохраняйте книгу перед запуском макросов и убедитесь, что макросы разрешены в настройках безопасности.
Извлечение слов с помощью Power Query (разделение и объединение)
При работе с большими таблицами или регулярными задачами импорта и экспорта Power Query предлагает надёжное решение для извлечения и преобразования слов из текстовых строк. Power Query, расположенный на вкладке Данные в группе Получение и преобразование данных, позволяет визуально разделять текст по разделителям (пробелам, запятым и т.д.), извлекать нужные слова, а затем объединять их или загружать обратно в Excel. Этот процесс легко воспроизводится и идеально подходит для массовой очистки данных — особенно при обработке сотен или тысяч строк.
Основные преимущества: Последовательность при повторном использовании, интуитивно понятный визуальный интерфейс и простота настройки при изменении требований к позиции слов.
Шаги выполнения:
- Выделите ваш диапазон данных и выберите Данные > Из таблицы/диапазона, чтобы загрузить данные в Power Query. При появлении запроса подтвердите создание таблицы или создайте её.
- В редакторе Power Query выберите столбец, содержащий ваш текст.
- Нажмите Разделить столбец>По разделителю
- Выберите Пробел или введите собственный разделитель, затем выберите Разделить по – Каждому вхождению разделителя. После этого нажмите «ОК».
- Каждое слово из текста теперь будет находиться в отдельном столбце (например, Столбец1, Столбец2 и т.д.). Если вы хотите извлечь первые два слова, объедините разделённые слова: выделите нужные столбцы, щёлкните правой кнопкой мыши по их заголовкам и выберите в контекстном меню Объединить столбцы.
- Выберите предпочитаемый разделитель (например, пробел или запятую) и нажмите ОК.
- Наконец, нажмите Закрыть и загрузить, чтобы загрузить результаты обратно в новый лист текущей книги.
Power Query также позволяет с лёгкостью повторно использовать процесс: достаточно обновить запрос при изменении исходной таблицы, чтобы гарантировать стабильные результаты без необходимости вручную заново применять формулы или макросы. Не забудьте проверить количество столбцов, создаваемых Power Query, и учитывать отсутствие слов в более коротких текстовых строках.
Другие связанные статьи:
- Извлечение нескольких строк из ячейки
- У вас есть список текстовых строк, разделённых разрывами строк (введёнными с помощью Alt + Enter), и вы хотите извлечь каждую такую строку в отдельную ячейку, как показано на скриншоте ниже. Как решить эту задачу с помощью формулы в Excel?
- Извлечение подстроки из текстовой строки в Excel
- Вам нередко приходится извлекать подстроки из текстовых строк. Хотя в Excel нет специальной функции для этой задачи, с помощью комбинации функций ЛЕВСИМВ, ПРАВСИМВ, ПСТР и ПОИСК вы сможете легко получать нужные фрагменты текста в соответствии с вашими требованиями.
- Извлечь текст между скобками из текстовой строки
- Если часть текста в ячейке заключена в скобки, как быстро и легко извлечь весь текст между ними в Excel, как показано на скриншоте ниже?
- Извлечение слова, начинающегося с определённого символа, в Excel
- Например, вам может понадобиться извлечь слово, начинающееся с символа «=», из каждой ячейки, как показано на скриншоте ниже. Как быстро и легко выполнить эту задачу в Excel?
- Извлечь текст после последнего вхождения определённого символа
- Если у вас есть список сложных текстовых строк, содержащих несколько разделителей (например, как на скриншоте ниже: дефисы, запятые и пробелы внутри ячейки), и вы хотите определить позицию последнего дефиса, а затем извлечь подстроку, расположенную после него.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек