как извлечь первое и последнее слово из ячейки
В учебнике демонстрируется быстрый, точный и безошибочный метод извлечения слов в Excel без длинных и сложных формул благодаря специальной функции.
Вы когда-нибудь сталкивались с проблемой извлечения определенных слов из текста в ячейке Excel? Получить первое или последнее слово можно относительно легко, используя встроенные функции Excel. Однако извлечь произвольное слово из середины текста может быть гораздо сложнее. В этой статье мы объясним, как эффективно и точно выполнить задачу с помощью пользовательской функции.
Пользовательская функция для извлечения слова из строки в Excel
В одной из предыдущих статей мы обсуждали, как извлечь первое, последнее или N-е слово из ячейки с помощью формулы MID. Теперь мы покажем вам, как заменить все эти длинные формулы одной определяемой пользователем функцией. Это сэкономит вам массу времени и предотвратит непреднамеренные ошибки при составлении формул.
Пользовательская функция ExtractWord, представленная в этой статье, позволит вам:
- Извлеките первое или последнее слово из ячейки.
- Вытяните второе, третье или любое конкретное слово из строки.
- Используйте любые разделители слов, такие как пробел, запятая или точка с запятой.
- Получить из текста любое слово, содержащее указанный символ.
Для начала добавьте следующий код в свою книгу, как описано в разделе «Как вставить код VBA в Excel».
Пользовательская функция для извлечения слова из ячейки Excel
Функция ExtractWord(rRange As Range, Необязательный nWord As Long, Необязательный разделитель As String, Необязательный spCharacter As String) Как строка Dim rCell As Range Dim text Как строка, sWord Как строка Dim arr() Как строка, arr2(10) Как строка, i As Long, a As Long ‘ объединить диапазон в текстовую строку Для каждого rCell In rRange text = text & ” ” & rCell Next rCell a = 1 ‘ разделителем по умолчанию является пробел If Delimiter = “” или Delimiter = ” ” then Delimiter = ” ” End If ‘ удалить лишние пробелы text = WorksheetFunction.Trim(text) ‘ создать массив текстовых значений arr() = Split(text, Delimiter) ‘ извлечь первое слово, содержащее определенный символ If spCharacter <> “” Тогда Do While i < UBound(arr()) + 1 If InStr(1, arr(i), spCharacter, 1) > 0 Тогда arr2(a) = arr(i) a = a + 1 End If i = i + 1 Цикл ‘ извлекает слово, содержащее определенный символ по номеру. Если nWord > a – 1 Или nWord = -1 Тогда ExtractWord = arr2(a – 1) Иначе Если nWord > 0 Тогда nWord = nWord – 1 End If ExtractWord = arr2(nWord + 1) End If Else ‘извлекаем слово If nWord > UBound(Split(text, Delimiter)) + 1 Or UBound(Split(text, Delimiter)) = 0 then ExtractWord = “” Else ‘ извлекаем первое слово If nWord = 0 then nWord = 1 End If ‘ извлечь последнее слово If nWord < 0 then On Error Resume Next nWord = UBound(Split(text, Delimiter)) + (nWord + 2) End If ' извлечь слово по номеру ExtractWord = arr(nWord - 1) End Если Конец Если Конец Функция
Кончик. Если вы планируете часто использовать эту функцию, мы рекомендуем сохранить ее в файле надстройки, а не в текущей книге, для большей доступности. Подробные инструкции о том, как это сделать, можно найти здесь: Использование надстроек для хранения пользовательских функций.
Как только код функции будет добавлен в Excel, вы сможете использовать его так же, как любую другую встроенную функцию. Если вы новичок в UDF и хотите узнать больше, вы можете найти полную информацию в разделе «Как использовать пользовательские функции в Excel».
Синтаксис функции
Синтаксис использования пользовательской функции следующий:
ExtractWord(диапазон_данных, [word_num], [delimiter], [char])
Обязательный аргумент:
- Диапазон_данных — это ячейка или диапазон ячеек, из которых вы хотите извлечь слово.
Необязательные аргументы:
- Word_num – этот аргумент указывает порядковый номер слова, которое вы хотите извлечь:
- Положительное число – извлекает слово из начала строки. Например, чтобы извлечь первое слово, введите 1 или оставьте пустым.
- Отрицательное число – извлекает слово с конца строки. Например, чтобы извлечь последнее слово, введите -1.
- По умолчанию 1 — если аргумент опущен, функция извлечет первое слово.
- Разделитель — используйте этот аргумент, чтобы указать разделитель слов. Если не указано, предполагается, что слова разделены пробелами. Разделитель чувствителен к регистру. Последовательные разделители рассматриваются как один разделитель.
- Char — используйте этот аргумент, если хотите извлечь слово, содержащее определенный символ. Если вы также установите аргумент word_num, функция извлечет первое, второе, третье,… или последнее слово, содержащее указанный символ.
Определив синтаксис, давайте перейдем к практическим примерам и более подробно рассмотрим, как использовать эту мощную функцию в электронных таблицах Excel.
Как извлечь первое слово в Excel
Начнем с самого распространенного сценария — извлечения первого слова из ячейки Excel. Предполагая, что целевой текст находится в ячейке A3, пользовательская функция предельно проста:
=ИзвлечьСлово(A3, 1)
Фактически, вы можете сделать его еще короче, опустив второй аргумент, поскольку word_num по умолчанию равен 1:
=ИзвлечьСлово(A3)
Теперь давайте сравним это с традиционной формулой MID для извлечения первого слова из строки:
=MID(A3, 1, ПОИСК(” “, A3) – 1)
Как видите, результаты те же, но пользовательская функция более лаконична и удобна для пользователя.
Как извлечь последнее слово в Excel
Чтобы извлечь последнее слово из текстовой строки в Excel с помощью пользовательской функции, установите для аргумента word_num значение -1. Вот как выглядит формула:
=ИзвлечьСлово(A3, -1)
Теперь, если бы вы попытались выполнить ту же задачу, используя встроенные функции Excel, вам пришлось бы создать гораздо более длинную и сложную формулу, объединяющую шесть различных функций. Вот как это будет выглядеть:
=ОТРЕЗАТЬ(ПРАВО(ЗАСТАВИТЬ(A3, ” “, ПОВТОРИТЬ(“” “, LEN(A3))), LEN(A3)))
Несмотря на сложность нативной формулы, результаты абсолютно идентичны тем, что вы получаете с помощью нашей пользовательской функции:
Выбрав пользовательскую функцию, вы не только упростите формулы Excel, но и повысите ясность и читаемость вашей электронной таблицы.
Как получить N-е слово из ячейки Excel
Чтобы извлечь какое-либо конкретное слово из текстовой строки, вам просто нужно указать функции ExtractWord, какое слово вам нужно.
Допустим, вы хотите извлечь второе слово из ячейки A3. Вот формула:
=ИзвлечьСлово(A3, 2)
Если вашей целью является второе слово с конца строки (предпоследнее слово), формула изменится на:
=ИзвлечьСлово(A3, -2)
Кроме того, вы можете использовать более гибкий подход, поместив номер слова в другую ячейку. Например, если номер целевого слова находится в ячейке B3, формула принимает следующий вид:
=ИзвлечьСлово(A3, B3)
Эта формула намного короче и проще, чем формула с использованием обычных функций Excel, но она дает тот же результат:
=TRIM(MID(SUBSTITUTE(TRIM($A$3), ” “, REPT(” “, LEN($A$3))), (B3-1)*LEN($A$3)+1, LEN($A 3 доллара)))
Подробное объяснение этой формулы см. в разделе «Функция MID для извлечения N-го слова из текста».
Используя этот метод, вы экономите много времени. Кроме того, при использовании компактной пользовательской функции сложно допустить ошибку, а более длинные формулы могут привести к случайным ошибкам.
Извлечение слов с пользовательским разделителем
Во всех предыдущих примерах мы сосредоточились на извлечении слов, разделенных пробелами. Но это не всегда так. Когда вы импортируете данные в Excel из других программ, в качестве разделителей могут использоваться различные символы, такие как точки с запятой, косая черта, вертикальные полосы и т. д. В таких ситуациях на помощь приходит третий аргумент функции ExtractWord.
Проиллюстрируем это примером. Предположим, вы хотите получить второе слово из ячейки А3, где слова разделяются знаком «(пробел-косая черта-пробел). Формула выглядит следующим образом:
=ExtractWord(A3, 2, “)
Важно отметить, что мы включаем пробел с обеих сторон косой черты, чтобы избежать захвата начальных и конечных пробелов в результатах. Если в ваших исходных записях используются косые черты без пробелов, вам просто нужно использовать «в качестве аргумента-разделителя.
Как показано на скриншоте ниже, в этом случае в результате можно получить даже несколько слов. Это происходит потому, что здесь пробел рассматривается как обычный символ, а не как разделитель. Другими словами, любые символы между разделителями, указанными в формуле, считаются одним словом.
Примечание. Аргумент-разделитель чувствителен к регистру, что может иметь решающее значение при использовании буквы в качестве разделителя, поскольку буквы верхнего и нижнего регистра рассматриваются как разные символы.
Как получить слово, содержащее определенный символ
Чтобы извлечь слово, содержащее определенный символ, вы можете использовать четвертый аргумент функции ExtractWord, метко названный char. Этот аргумент определяет, какой конкретный символ вы хотите найти в тексте, и указывает функции извлечь слово, содержащее этот символ.
Например, финансовые документы, созданные в Excel, часто содержат в текстовых строках различные суммы в валюте. Вместо использования сложных регулярных выражений для извлечения этих цифр вы можете использовать простую пользовательскую функцию.
=ExtractWord(A3, 1, , “$”)
В этом сценарии формула возвращает первое слово, содержащее символ «$» из текста в формате A3.
В приведенном ниже примере показано, как можно использовать ссылку на ячейку, содержащую целевой символ (B3). Вы также можете указать, какое вхождение слова с нужным символом нужно вернуть (ячейки A6:A9). Как показано, в исходной строке есть четыре слова с символом «$». Вы можете получить любой из них, введя следующую формулу в ячейку B6 и скопировав ее через B9:
=ExtractWord($A$3, A6, , $B$3)
Как извлечь слово, содержащее определенный текст
Помимо символов, функция ExtractWord отлично справляется с извлечением слов, содержащих определенный текст. Для этого просто укажите целевой текст (подстроку) в качестве аргумента char.
Например, чтобы извлечь адреса электронной почты с доменом «gmail», формула выглядит следующим образом:
=ExtractWord(A3, , , “gmail”)
Результат отображен на скриншоте ниже:
Как извлечь текст за словом в Excel
Чтобы извлечь текст, следующий за определенным словом, вам нужно сделать две вещи:
- Укажите целевое слово в качестве аргумента-разделителя.
- Укажите 2 для аргумента word_number, чтобы указать, что вы хотите получить слово после разделителя.
Да, это так просто!
Например, чтобы получить текст после фразы «Код ошибки», формула выглядит следующим образом:
=ExtractWord(A3, 2, «Код ошибки»)
Примечание. Помните, что разделитель чувствителен к регистру, поэтому использование «кода ошибки» и «кода ошибки» приведет к разным результатам. При использовании этого метода убедитесь, что регистр целевого слова точно соответствует регистру.
Как разбить текст на слова по любому разделителю
Это конкретное использование функции ExtractWord может быть не сразу очевидным, но это мощный метод. Включив функцию СТРОКА или СТОЛБЕЦ в формулу ExtractWord, вы можете эффективно разделить текстовую строку на отдельные ячейки по вертикали или по горизонтали.
Разбить строку на строки
Чтобы разделить текст в формате A3 на строки, используя «в качестве разделителя, формула следующая:
=ExtractWord($A$3, ROW(A1), “)
Поместите эту формулу в ячейку B3, а затем скопируйте ее в столбец. Вы добьетесь следующего результата:
При копировании формулы вниз по столбцу относительная ссылка на ячейку A1 меняется на A2, A3 и т. д. В результате функция ROW(A1) возвращает 1, ROW(A2) возвращает 2 и т. д. Это позволяет последовательно извлекать первое, второе и последующие слова в отдельные ячейки заданного столбца.
Разделить строку по столбцам
Чтобы поместить слова в отдельные столбцы, используйте функцию СТОЛБЕЦ вместо СТРОКИ, чтобы сгенерировать числа для аргумента word_num:
=ExtractWord($A$3, СТОЛБЕЦ(A1), “)
Введите формулу в первую ячейку (A8), а затем скопируйте ее по строке. Исходное содержимое ячейки будет соответственно разделено на отдельные столбцы.
В Excel 365 аналогичного результата можно добиться с помощью функции TEXTSPLIT. Однако в более ранних версиях, где TEXTSPLIT недоступен, этот метод может служить надежной альтернативой инструментам разделения ячеек Excel или формулам разделения строк.
Как получить слово из диапазона ячеек
Функция ExtractWord не ограничивается извлечением слов из отдельных ячеек; он также может извлекать слова из диапазона. Для этого укажите диапазон ячеек в качестве первого аргумента. Вот пример:
=ExtractWord($A$3:$A$13, C3)
На снимке экрана ниже показано, как можно получить любое слово из диапазона, используя номера слов, указанные в предопределенных ячейках (в нашем случае C3:C12).
Кончик. При извлечении слов из диапазона вы можете определить свой собственный разделитель. Дополнительно вы можете указать символ, который должно содержать слово. Другими словами, во всех примерах, которые мы рассмотрели ранее, вы можете указать диапазон вместо отдельной ячейки.
Вот как извлечь слова из ячеек Excel с помощью специальной функции. Хотите ли вы уловить конкретные слова или найти контент, основанный на определенных персонажах, этот удобный инструмент поможет вам найти свежие идеи. Итак, в следующий раз, когда вы будете иметь дело с текстом в Excel, помните о функции ExtractWord — она может стать секретом улучшения ваших задач с данными и достижения лучших результатов.
Рабочая тетрадь для скачивания
Excel: извлечь слово из ячейки — примеры (файл .xlsm)
