Формула для поиска и получения последнего слова из строки
Научитесь находить и получать последнее слово из текстовой строки с помощью функций MID, FIND, CHAR, LEN и SUBSTITUTE .
Работа с текстовыми строками в Excel иногда является легкой задачей, иногда нет. В этой статье мы объясним, как использовать лучшие практики. Как обычно, сначала мы выполним задачу, используя встроенные в Excel функции манипулирования строками. Кроме того, мы представим новую определяемую пользователем функцию 9.0007 GetLastWord тоже!
Шаги для извлечения последнего слова из текста:
- Заменить пробелы нулевыми строками: ПОДСТАВИТЬ
- Вставить символ-разделитель для последнего пробела CHAR
- Найти положение разделителя: НАЙТИ
- Удалить первый символ из последнего слова:
- Извлечь последнее слово между разделителями: MID
В этом примере ячейка B3 содержит текстовую строку, с которой мы хотим работать.
Используйте следующие формула , чтобы найти последнее слово:
=MID(B3,1+НАЙТИ(СИМВОЛ(1),ПОДСТАВИТЬ(B3," ",СИМВОЛ(1),ДЛСТР(В3)-ДЛСТР(ПОДСТАВИТЬ(В3," ") ,"")))),LEN(B3)) Объяснение Вычислить формулу изнутри наружу .
Сначала мы заменим символы пробела пустыми строками:
=LEN(B3)-LEN(SUBSTITUTE(B3," ","")) После этого вставьте разделитель CHAR(1) для последнего пробела:
=ПОДСТАВИТЬ(B3," ",CHAR(1),LEN(B3)-LEN(SUBSTITUTE(B3," ",""))) НАЙТИ позицию разделителя в выбранной строке:
=НАЙТИ(СИМВОЛ(1),ПОДСТАВИТЬ(B3," ",СИМВОЛ(1),ДЛСТР(B3)-ДЛСТР(ЗАМЕНИТЬ(B3," " ,"")))) Поскольку мы используем разделитель, мы должны удалить первый символ последнего слова:
НАЙТИ(СИМВОЛ(1),ЗАМЕНИТЬ(B3," ",СИМВОЛ(1) ,LEN(B3)-LEN(SUBSTITUTE(B3," ","")))) + 1 Наконец, расширите формулу с помощью функции MID , чтобы извлечь последнее слово между разделителем:
=MID(B3,1+НАЙТИ(СИМВОЛ(1),ЗАМЕНИТЬ(B3," ",СИМВОЛ(1),ДЛСТР(B3)-ДЛСТР(ЗАМЕНИТЬ(B3," ","")))),ДЛСТР (B3)) В следующем примере у нас та же цель.
Если вы хотите извлечь последнее слово из текстовой строки, вы можете использовать другой альтернативный способ выполнения вычислений. Объедините REPT , ЗАМЕНА , ПРАВО и ОТДЕЛКА . Этот метод использует хитрости и упрощает формулу, упомянутую выше.
Проанализируем следующую формулу:
=ОБРЕЗАТЬ(ПРАВО(ПОДСТАВИТЬ(A3," ",ПОВТОР(" ",50)),50)) Функция ОБРЕЗАТЬ удалит любое количество начальных пробелов. Мы используем в качестве аргумента 50, но в некоторых случаях (например, у вас длинные слова) вам нужно увеличить это число.
Функция ПОДСТАВИТЬ находит все пробелы в текстовой строке и заменяет каждый пробел на N пробелов. N — переменная целочисленного типа, в данном случае 50.
=ПОДСТАВИТЬ(A3," ",REPT(" ",50)) Функция ПРАВИЛЬНО извлекает N символов (50) справа.
=ВПРАВО(ПОДСТАВИТЬ(A3," ",ПОВТОР(" ",50)),50) Остался только один шаг. Функция TRIM удаляет лишние пробелы между словами, а также начальные или конечные пробелы.
Наконец, формула возвращает последнее слово. =ОТРЕЗАТЬ(ПРАВО(ЗАМЕНИТЬ(A3," ",REPT(" ",50)),50)) И последнее, но не менее важное: Скорость имеет значение . Конечно, у нас есть определяемая пользователем функция для повышения вашей производительности. Синтаксис понятен каждому.
=GetLastWord(cell) Функция использует один аргумент: « ссылка на ячейку ».
Настоятельно рекомендуется использовать библиотеку пользовательских функций, например DataFX. Надстройка значительно повышает вашу производительность . Кроме того, попробуйте DataXL бесплатную надстройку Excel для проектов по очистке данных. Никаких навыков кодирования не требуется; вам нужно только следовать руководству по установке надстройки .
- Извлечь энное слово из текста
- Получить первое слово из текста в Excel
- Расширение файла полосы
Иштван Возар
Иштван является соучредителем компании Visual Analytics. Он помогает людям достичь вершин в Excel.
Извлечь последнее слово из ячейки
Автор: Оскар Кронквист Последнее обновление статьи: 27 января 2022 г.
Содержание
- Извлечь последнее слово0026
- Извлечь последнюю букву
- Извлечь последнее число
- Получить файл Excel *.xlsx
1. Извлечь последнее слово
Формула, показанная выше для диапазона ячеек C3:C9, извлекает последнее слово из соседней ячейки в столбце B. » «, 200)), 200))
Меня вдохновил комментарий Рика Ротштейна от 2012 года, когда я составил эту формулу.
Если ваши слова длиннее 200 символов, измените каждый экземпляр 200 в приведенной выше формуле на более высокое значение.
Символ-разделитель — пробел (пробел). Убедитесь, что вы изменили это, если значение ячейки содержит другой символ-разделитель строк.
Объяснение формулы в ячейке C3
Шаг 1. Повторить пробел 200 раз
Функция ПОВТОР повторяет определенный текст заданное количество раз.
ПОВТОР( текст , число_время )
ПОВТОР(» «, 200)
возвращает 200 соединенных пробелов.
Шаг 2. Замените пробел на 200 пробелов
Функция ПОДСТАВИТЬ заменяет каждый пробел в значении ячейки на 200 пробелов.
SUBSTITUTE(B3,» «,REPT(» «,200))
становится
SUBSTITUTE(«Martin Williams»,» «,» «)
и возвращает «Martin Williams».
(Я сократил строку по понятным причинам.)
Шаг 3. Извлечение 200 символов справа
Функция ПРАВО извлекает 200 символов, начиная справа.
ПРАВО( текст ,[ num_chars ])
ПРАВО(ЗАМЕНИТЬ(B3,» «,REPT(» «,200)), 200)
становится
ПРАВО(«Mar олово 200)
и возвращает « Williams».
Все символы пробела не отображаются по понятным причинам.
Шаг 4. Удаление начальных пробелов
Функция TRIM удаляет все начальные и конечные пробелы в строке или значении ячейки.
ОТДЕЛКА (ПРАВАЯ (ЗАМЕНА (B3, » «, ПОВТОР (» «, 200)), 200))
становится
TRIM(» Williams»)
и возвращает «Williams» в ячейке C3.
В начало
2. Извлечь последнюю букву
Формула в ячейке D3 извлекает последнюю букву из символов в ячейке B3, значение содержит буквы, цифры и другие случайные символы.
Формула массива в ячейке D3:
=MID(B3, MAX(ЕСЛИОШИБКА(ПОИСК({«a»; «b»; «c»; «d»; «e»; «f»; «g»; «ч», «и», «к», «к», «л», «м», «н», «о», «р», «к», «р», «с», «т». «; «и»; «в»; «ш»; «х»; «у»; «з»}, В3), «»)), 1)
В начало
2.1 Как ввести формулу массива
На изображении выше показана ведущая фигурная скобка, формула слишком велика для отображения замыкающей фигурной скобки, однако она есть.
Они появляются автоматически, когда вы выполняете следующие шаги.
- Скопируйте приведенную выше формулу массива.
- Дважды нажмите левой кнопкой мыши на ячейку D3, появится подсказка.
- Вставьте его в ячейку C3, сочетание клавиш CTRL + v.
- Одновременно нажмите и удерживайте клавиши CTRL + SHIFT.
- Нажмите Enter один раз.
- Отпустить все клавиши.
Строка формул показывает начальную и конечную фигурные скобки, не вводите эти символы самостоятельно.
В начало
2.2 Объяснение формулы
Шаг 1. Поиск всех букв
Функция ПОИСК возвращает число, представляющее позицию символа, в которой находится определенная текстовая строка, читаемая слева направо. Это не чувствительный к регистру поиск.
ПОИСК( найти_текст , внутри_текста , [ start_num ])
ПОИСК({«a»; «b»; «c»; «d»; «e»; «f»; «g»; «h»; » и», «к», «к», «л», «м», «н», «о», «р», «ц», «р», «с», «т», «у» ; «v»; «w»; «x»; «y»; «z»}, B3)
становится
ПОИСК({«a»; «b»; «c»; «d»; «e «; «ж»; «г»; «ч»; «и»; «к»; «к»; «л»; «м»; «н»; «о»; «р»; «ц»; «r»; «s»; «t»; «u»; «v»; «w»; «x»; «y»; «z»}, «12 Wi llia3 3m s2»)
и возвращает
{10; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 5; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 7; 14; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 16; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; 4; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЗНАЧ!}
Шаг 2.
Замените значения ошибок пробеламиФункция MAX не может обрабатывать значения ошибок, мы должны позаботиться о них. Функция ЕСЛИОШИБКА может заменить значения ошибки заданным значением.
ЕСЛИОШИБКА( значение , значение_если_ошибка )
ЕСЛИОШИБКА(ПОИСК({«a»; «b»; «c»; «d»; «e»; «f»; «g»; «h» ; «и»; «к»; «к»; «л»; «м»; «н»; «о»; «р»; «ц»; «р»; «с»; «т»; » u»; «v»; «w»; «x»; «y»; «z»}, B3), «»)
становится
ЕСЛИОШИБКА({10; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ !; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; 5; #ЗНАЧ!; #ЗНАЧ!; 7; 14; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ! !; 16; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; 4; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!}, «»)
и возвращает
{10; «»; «»; «»; «»; «»; «»; «»; 5; «»; «»; 7; 14; «»; «»; «»; «»; «»; 16; «»; «»; «»; 4; «»; «»; «»}.
Шаг 3. Вычислите наибольшее число в массиве
Функция MAX возвращает наибольшее число в диапазоне ячеек или массиве.
МАКС(ЕСЛИОШИБКА(ПОИСК({«a»; «b»; «c»; «d»; «e»; «f»; «g»; «h»; «i»; «j»; » к»; «л»; «м»; «н»; «о»; «р»; «ц»; «р»; «с»; «т»; «у»; «в»; «ш» ; «x»; «y»; «z»}, B3), «»))
становится
МАКС({10; «»; «»; «»; «»; «»; «»; » «; 5; «»; «»; 7; 14; «»; «»; «»; «»; «»; 16; «»; «»; «»; 4; «»; «»; «» })
и возвращает 16.
Шаг 4. Извлечение последней буквы
Функция MID возвращает подстроку из строки на основе начальной позиции и количества символов, которые вы хотите извлечь.
MID( text , start_num , num_chars )
MID(B3, MAX(IFERROR(ПОИСК({«a»; «b»; «c»; «d»; «e»; » ф», «г», «ч», «и», «к», «к», «л», «м», «н», «о», «р», «ц», «р» ; «s»; «t»; «u»; «v»; «w»; «x»; «y»; «z»}, B3), «»)), 1)
становится
MID(«12 Willia3 3m s2 «, 16, 1)
и возвращает «s» в ячейке D3.
В начало
3. Извлечение последней цифры из ячейки
На изображении выше показана формула массива в ячейке D3, которая извлекает последнюю цифру из ячейки B3.
Формула массива в ячейке D3:
=СРЕДН(B3, МАКС(ЕСЛИОШИБКА(ПОИСК({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, B3), «»)) , 1)
Как ввести формулу массива
Наверх
3.1 Объяснение формулы
Шаг 1. Поиск всех цифр
Функция ПОИСК возвращает число, представляющее позицию символа, в которой найдена определенная текстовая строка, читаемая слева направо.
Это не чувствительный к регистру поиск.
ПОИСК( найти_текст , внутри_текста , [ начальный_номер ])
ПОИСК({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, B3) 90 005
становится
ПОИСК({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, «12?Wi:llia3 3m_s»)
и возвращает
{#ЗНАЧ!; 1; 2; 11; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!; #ЦЕНИТЬ!}.
Шаг 2. Замените значения ошибок пробелами
Функция ЕСЛИОШИБКА может заменить значения ошибок заданным значением.
ЕСЛИОШИБКА( значение , значение_если_ошибка )
ЕСЛИОШИБКА(ПОИСК({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, B3), «»))
становится
ЕСЛИОШИБКА({#ЗНАЧ!; 1; 2; 11; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!})
и возвращает
{«»; 1; 2; 11; «»; «»; «»; «»; «»; «»}.
Шаг 3. Вычислите наибольшее число в массиве
Функция MAX возвращает наибольшее число в диапазоне ячеек или массиве.
МАКС(ЕСЛИОШИБКА(ПОИСК({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, B3), «»))
становится
МАКС({«»; 1; 2; 11; «»; «»; «»; «»; «»; «»})
и возвращает 11.
Шаг 4. Извлечение последней буквы
Функция MID возвращает подстроку из строки на основе начальная позиция и количество символов, которые вы хотите извлечь.
MID( text , start_num , num_chars )
MID(B3, MAX(IFERROR(ПОИСК({0; 1; 2; 3; 4; 5; 6; 7; 8;9}, B3), «»)), 1)
становится
MID(«12?Wi:llia3 3m_s», 11, 1)
и возвращает «s» в ячейке D3.
В начало
Получить файл Excel *.xlsx
Извлечь последнее слово в ячейке.xlsx
Извлечь категорию
Как создать инициалы имени
Формула массива в ячейке C3 извлекает первый символ из имени, отчества и фамилии. Формула отлично работает […]
Сортировка и возврат уникальных отдельных цифр из диапазона ячеек
В этой статье демонстрируется формула, которая отфильтровывает уникальные отдельные цифры из диапазона ячеек, содержащего числа. Диапазон ячеек B3:B6 […]
Извлечение заголовков таблицы на основе условия
В этой статье демонстрируется формула массива, которая возвращает заголовок таблицы на основе условия.
Просмотреть все статьи в категории «извлечение»
Функции в этой статье
TRIMRIGHTSUBSTITUTEREPTMIDMAXIFERRORSEARCH
Более 1300 формул ExcelКатегории Excel
Домашняя страница
Последние обновленные статьи.
Функции Excel
Более 300 функций Excel с подробной информацией, включая синтаксис, аргументы, возвращаемые значения и примеры для большинства функций, используемых в формулах Excel.
Формулы Excel
Более 1300 формул, организованных в подкатегории.
Таблицы Excel
Таблицы Excel упрощают работу с данными, добавляют или удаляют данные, фильтруют, суммируют, сортируют, улучшают читаемость с помощью форматирования ячеек, ссылок на ячейки, формул и т. д.
Расширенный фильтр
Позволяет фильтровать данные на основе выбранного значения, заданного текста или других критериев. Он также позволяет фильтровать существующие данные или перемещать отфильтрованные значения в новое место.
Проверка данных
Позволяет контролировать, что пользователь может вводить в ячейку. Это позволяет вам указать условия и показать собственное сообщение, если введенные данные недействительны.
Раскрывающийся список
Позволяет пользователю работать более эффективно, отображая список, из которого пользователь может выбрать значение. Это позволяет вам контролировать то, что отображается в списке, и это быстрее, чем ввод в ячейку.
Именованные диапазоны
Позволяет назвать одну или несколько ячеек, это упрощает поиск ячеек с помощью поля Имя, чтение и понимание формул, содержащих имена вместо ссылок на ячейки.
Excel Solver
Excel Solver — это бесплатная надстройка, которая использует целевые ячейки, ограничения, основанные на формулах на листе, для выполнения анализа «что, если» и других задач принятия решений, таких как перестановки и комбинации.
Функция Excel, позволяющая визуализировать данные в виде графика.
Условное форматирование
Форматирование ячеек или значений ячеек на основе условия или критерия. Существует множество встроенных инструментов условного форматирования, которые можно использовать, или использовать пользовательскую формулу условного форматирования.
Сводные таблицы
Позволяет быстро суммировать большие объемы данных в удобной для пользователя форме. Эта мощная функция Excel позволяет эффективно анализировать, упорядочивать и классифицировать важные данные.
VBA
VBA означает Visual Basic для приложений и представляет собой язык программирования, разработанный Microsoft. Он позволяет автоматизировать трудоемкие задачи и создавать пользовательские функции.
Макросы
Программа или подпрограмма, встроенная в VBA, которую может создать любой. Используйте средство записи макросов, чтобы быстро создавать собственные макросы VBA.
UDF
UDF расшифровывается как User Defined Functions и представляет собой настраиваемые функции, которые может создать каждый.