Excel выбрать часть текста из ячейки

Microsoft Excel

трюки • приёмы • решения

Как извлечь слова из строки таблицы Excel

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

Извлечение первого слова из строки

Чтобы извлечь первое слово из строки, формула должна найти позицию первого символа пробела, а затем использовать эту информацию в качестве аргумента для функции ЛЕВСИМВ. Следующая формула делает это: =ЛЕВСИМВ(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)))

Источник: excelexpert.ru

Как вытащить число или часть текста из текстовой строки в Excel

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

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

Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета. Мы не будем говорить о необходимости сближения бухгалтерского и управленческого учета, или, по крайней мере, получения нужных срезов и аналитик в имеющихся учетных программах в автоматическом режиме. К сожалению, зачастую экономисту приходиться «перелопачивать» огромные объемы информации вручную.

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

Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).

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

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

Вся трудность, в том чтобы извлечь код из текстовой строки.

Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.

В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.

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

Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).

«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.

Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.

Сложнее если нужные нам символы находятся в середине текста.

Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.

=ПСТР(текст; начальная_позиция; количество_знаков)

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

Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.

Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»

Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.

В нашем случае формула будет иметь вид:

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

Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.

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

Читайте также:  Сравнение ячеек в excel

=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])

Искомый_текст – обязательный аргумент. Текст, который требуется найти.

Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.

Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.

Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

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

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

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

Источник: excel-training.ru

Примеры использования функции ЛЕВСИМВ в Excel работа с текстом

Функция ЛЕВСИМВ в Excel используется для получения части текста и возвращает заданное количество символов текстовой строки, при этом отсчет символов начинается слева.

Как вырезать часть текста из строки функцией ЛЕВСИМВ в Excel?

Пример 1. В таблице Excel содержатся имена переменных вместе с текстом, определяющим их тип данных. Необходимо выделить только названия переменных и записать их в отдельный столбец.

Исходная таблица данных:

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

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

Как видно на рисунке с помощью функции ЛЕВСИМВ получилось вытащить левую часть текста из каждой ячейки по одному (первому) символу.

Как вытащить часть текста из ячейки в Excel используя функцию ЛЕВСИМВ?

Пример 2. В таблицу Excel подтягиваются данные из другого программного продукта. Одно из полей данных получило ошибку. Для обработки ошибки необходимо выделить ее код и описание из строки.

Выделим код ошибки с помощью следующей формулы:

  • ЛЕВСИМВ(B5;НАЙТИ(“3”;B5)+1) – функция, выделяющая часть текстовой строки слева, количество символов в которой соответствуют порядковому номеру символа «3» (функция НАЙТИ выполняет поиск порядкового номера этого символа и возвращает соответствующее значение). Единица (+1) добавлена для выделения всего кода ошибки.
  • 2 – количество символов, которые вернет функция ПРАВСИМВ, работающая аналогично рассматриваемой ЛЕВСИМВ (отличие – возвращает символы справа, а не слева строки).

В результате получим:

Выделим часть текста ошибки:

В данном случае удобнее использовать ПРАВСИМВ, которая является, по сути, зеркальным отражением ЛЕВСИМВ. Для определения второго аргумента используется запись ДЛСТР(B5)-НАЙТИ(“:”;B5)-1, определяющая позицию символа «u» в слове «unknown» (единица вычтена для удаления пробела).

В результате получим:

Округление чисел текстовыми функциями в текстовой строке

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

Форма для округления числовых значений имеет следующий вид:

В ячейке B3 введена следующая сложная формула:

=5,ЛЕВСИМВ(A2+1,НАЙТИ(“.”,A2)-1),ЛЕВСИМВ(A2,НАЙТИ(“.”,A2)-1)),”Число не введено”)’ >

  1. Первая функция ЕСЛИ проверяет с помощью формулы ЕПУСТО, заполнена ли ячейка, которая должна содержать значение, которое требуется округлить.
  2. Если число введено, запись ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(“,”;B2)+1)) возвращает числовое значение первого символа после запятой и сравнивает полученное значение с числом 5.
  3. Если полученное значение больше 5, будет возвращен результат выполнения функции ЛЕВСИМВ(B2+1;НАЙТИ(“,”;B2)-1), то есть целой части числа +1 по правилам округления.
  4. Если Примечание: данный алгоритм нельзя назвать гибким и он имеет некоторые недостатки (например, не учтен вариант ввода сразу целого числа). Он приведен в качестве примера использования функции ЛЕВСИМВ. Для расчетов лучше использовать встроенную функцию ОКРГУГЛ и подобные ей.

Функция ЛЕВСИМВ в Excel и особенности ее использования

Функция ЛЕВСИМВ используется наряду с прочими функциями для работы с текстовыми строками в Excel (ПРАВСИМВ, СИМВОЛ, СЦЕПИТЬ, ДЛСТР и прочими) и имеет следующую синтаксическую запись:

  • текст – ссылка на ячейку, содержащую текст, или текстовая строка, в которой требуется выделить требуемое количество символов слева. Аргумент обязателен для заполнения.
  • [число_знаков] – необязательный для заполнения аргумент; числовое значение, характеризующее количество символов, которые требуется выделить из текстовой строки.
  1. Если необязательный аргумента [число_знаков] явно не указан, по умолчанию будет возвращен первый символ строки. Например, функция =ЛЕВСИМВ(«текст») вернет значение «т».
  2. Числовое значение, переданное в качестве необязательного аргумента, должно быть взято из диапазона неотрицательных чисел, то есть должно быть равным нулю или больше нуля. В противном случае будет возвращен код ошибки #ЗНАЧ!.
  3. Если в качестве аргумента [число_знаков] будет переданы текстовые данные, функция ЛЕВСИМВ вернет код ошибки #ЗНАЧ!.
  4. Если число, переданное в качестве необязательного аргумента, превышает количество знаков в текстовой строке, рассматриваемая функция вернет весь текст целиком.
  5. Excel выполняет автоматическое преобразование типов данных там, где это возможно. Например, рассмотрим некоторые возможные варианты записи функции ЛЕВСИМВ и результаты ее выполнения:
  • ЛЕВСИМВ(«текст»;200%) вернет значение «те» (преобразование процентов в числовое значение);
  • при аргументах: («текст»;ИСТИНА) вернет «т» (логическое ИСТИНА эквивалентно числовому значению 1);
  • при аргументах: («текст»;ЛОЖЬ) вернет пустое значение «», поскольку логическое ЛОЖЬ эквивалентно числовому 0 (нуль);
  • при аргументах: («текст»;15/7) вернет значение «те».

Примечание 2: в Excel также существует схожая по принципу работы функция ЛЕВБ, возвращающая определенное количество символов текстовой строки слева на основе определенного количества байтов. Если по умолчанию используется язык с поддержкой двухбайтовой кодировки, для получения первого символа строки требуется в качестве аргумента указать число 2, а для получения последующих второго, третьего и прочих символов необходимо указывать соответствующие числа, кратные двум. Для языков с однобайтовой кодировкой функции ЛЕВСИМВ и ЛЕВБ возвращают идентичные результаты.

Источник: exceltable.com

Как в Эксель (Excel) вытащить часть текста из ячейки в другую ячейку?

Как в Excel извлечь часть текста из ячейки?

Например, в ячейке написана категория товара и информация о товаре.

Как выташить в отдельную ячейку только название категории (“Перчатки хозяйственные”, “Молоток слесарный” и т.п.)?

Если у Вас данные (которые нужно обработать, все эти “молотки” и “перчатки”) всегда отделены от остальной части текста запятой и первая ячейка с данными это B2, то формула такая

напишите ее в любую свободную ячейку (например правее) в той же строке, а потом растяните вниз и все ваши тысячи строк будут обработаны.

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

В Excel их довольно много, и в первую очередь можно выделить такие функции, как:

ЛЕВСИМВ и ПРАВСИМВ – излекают определённое число символов слева и справа соответственно.

ДЛСТР – длина строки.

НАЙТИ – возвращает позицию, с которой подстрока или символ входит в строку.

ПОДСТРОКА – извлекает подстроку из текста, которая отделена определённым символом-разделителе­ м.

ПСТР – извлекает указанное число знаков из строки (начиная с указанной позиции).

КОНЕЦСТРОКИ и НАЧАЛОСТРОКИ – возвращает строку после / до указанной подстроки.

Но здесь всё зависит от того, как именно эти данные расположены в исходной строке – одно дело в самом конце / начале, а другое – в середине.

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

Пример 1

Исходные данные такие:

Предположим, нужно извлечь в отдельную ячейку цену товара (3500 рублей, 4200 рублей).

Можно увидеть, что в этих ячейках цене предшествует текст “размеры, ” – то есть можно воспользоваться функцией КОНЕЦСТРОКИ и вытащить всё, что находится после этого текста.

Итак, ставим курсор в ячейку, куда нужно извлечь цену, и на вкладке “Формулы” выбираем “Текстовые” -> “КОНЕЦСТРОКИ”.

Указываем аргументы функции (обязательные):

ТЕКСТ – указываем ячейку, из которой нужно извлечь подстроку (B2 или B3).

НАЙТИ – указываем подстроку, после которой должно начаться извлечение текста (“размеры, “).

Нажимаем на кнопку “OK” и получаем то, что было нужно:

Формула получилась такая:

А если требуется, чтобы было только число (без рублей), то можно, например, использовать функцию НАЧАЛОСТРОКИ.

В этом случае в качестве 1 аргумента (исходной строки) вводим формулу, созданную выше, а в качестве 2 аргумента – ” “.

Пример 2

Нужно извлечь в отдельную ячейку название цвета (красный, коричневый и т.п.).

Здесь всё проще, так как название цвета находится в самом конце строки – и можно, например, использовать функцию ПРАВСИМВ.

У этой функции 2 аргумента:

Текст – указываем ячейку, из которой нужно извлечь подстроку.

Число_знаков – это разность между длиной исходной строки (функция ДЛСТР) и позицией запятой в этой строке (функция НАЙТИ), также дополнительно нужно отнять единицу, так как после запятой стоит пробел.

Формула и результат:

Но мне всё же больше нравится вариант с упомянутой выше функцией КОНЕЦСТРОКИ.

Она менее громоздкая и не содержит вложенных функций.

Эксель многие любят за то, что можно быстро обрабатывать и менять таблицы, так как надо.

Вот и в этом случаи, для того, чтобы вытащить из ячейки текст, нужно в пустой рядом столбик ввести формулу. Но тут не так всё просто. В зависимости от того, с какой стороны нужен текст, вводим формулу Левсимв и Правсимв. Одна из этих функций выведет нужный текст справа, другая слева. При этом формула будет выглядеть примерно так:=ЛЕВСИМВ(В1;10). В данном случаи 10 число символов. Но если число символов не одинаковое, то метод не совсем подойдёт.

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

Если в таблице одна или 2 строки, тогда можно воспользоваться функцией нажатия клавиш Ctrl+C скопировать и Ctrl+V вставить, а если в таблице нужно поменять цену для большого количества параметров, переходите в шапку инструментов, и действуйте по алгоритму, который находится под кнопой формулы — текстовые и в выпадающем меню находите среди абракадабры из сокращений “конецстроки”

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

Что касается абракадабры в выпадающем меню, на это есть подсказки, например

ЛЕВСИМВ – левые символы

ПРАВСИМВ – правые символы

ДЛСТР – длина строки

НАЙТИ – возвращает позицию, с которой подстрока или символ входит в строку.

КОНЕЦСТРОКИ возврат строки до конца

НАЧАЛОСТРОКИ – возврат строки в начало

Открывайте ячейку из которой надо вытащить часть текста, клацаете по тексту что бы курсор в тексте начал моргать, выделяете эту часть текста которую хотите утащить в другое место, щелкаете по выделенке ПКМ (правой кнопкой мышки) выбираете “копировать”

Переходите в окно куда нужно вставить, щелкаете в нем ЛКМ (левой кнопкой мышки) что бы активировать работу ввода данных в этой ячейки, следом щелкаете ПКМ, выбираете “вставить” и все.

Довольно сложный вопрос, но в Ексель можно сделать и такое, в этом редакторе есть подобные функции работы со строками.

Эти функции мы ищем в верхнем меню во вкладке “Формулы” – “Текстовые”:

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

Текст у нас написан для этого отлично, цена товара стоит в конце строки, после слова “размеры” и запятой, поэтому мы воспользуемся функцией КОНЕЦСТРОКИ из вкладки “Текстовые” (см. выше). Открывается вот такое окошечко, в поле ТЕКСТ указываем столбец, в котором находятся наши ячейки, в поле НАЙТИ – слова, после которых текст надо переносить в отдельную ячейку.

Нажимаем ОК, получаем то, что хотели:

Теперь можно, используя тот же алгоритм, поработать с новыми ячейками с помощью функции НАЧАЛОСТРОКИ и получить число без рублей:

Источник: www.bolshoyvopros.ru

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

Узнаем как можно выделить произвольное слово в ячейке (первое, последнее или n-ое по порядку) в Excel в двух вариациях: в виде сложной формулы и пользовательской функции.

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

Задача выделения конкретных (по порядку) слов из предложения имеет достаточно широкое применение при структурировании и анализе данных, например, выделение части артикула из кода товара, имени или фамилии из полной записи ФИО и т.д.

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


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

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

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

Пойдем по порядку.

Выделение первого слова в ячейке в Excel

C помощью функции НАЙТИ найдем позицию первого пробела, а затем воспользуемся функцией ЛЕВСИМВ, которая возвращает указанное количество символов с начала текста (как раз до первого найденного пробела):


Вместо ЛЕВСИМВ можно воспользоваться функцией ПСТР, которая более универсальна и позволяет вернуть заданное количество символов начиная с указанной позиции, а не только с начала строки (в данном случае с 1, так как мы выделяем именно первое слово):


Недостаток такого способа в том, что если в ячейке содержится ровно 1 слово, то она вернет значение ошибки, так как пробелов в нем нет:


Поэтому дополнительно добавим проверку на ошибку — в случае если во фразе нет пробелов, то возвращаем исходный текст:

Выделение последнего слова в ячейке в Excel

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

Далее вновь воспользуемся функцией ПОДСТАВИТЬ и заменим последний пробел (его порядковый номер мы уже знаем) на специальный символ, который точно не встретится в предложении (обычно используются символы #, ^ и т.п.).

После чего функцией ПРАВСИМВ (возвращает указанное количество символов с конца текста) выделяем все символы от специального символа (в данном случае функцией НАЙТИ ищем позицию символа #) до конца предложения:


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

Теперь перейдем к самому сложному варианту — выделению произвольного по порядку слова.

Выделение n-го слова в ячейке в Excel

Воспользуемся следующим приемом — продублируем все пробелы между словами очень большое количество раз (заведомо больше длины фразы, например, 1 000 раз), после чего функцией ПСТР выделяем 1 000 символов умноженные на n (номер нужного слова), а затем функцией ПРАВСИМВ возвращаем только последнюю 1 000 символов (в котором как раз содержится искомое слово).

Далее удаляем все лишние пробелы и получаем:


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

Если же мы хотим достать другое слово, то вместо *4 в примере выше, нужно просто поставить другой номер (1 для первого слова, 2 для второго и т.д.), а если указанный номер больше количества слов в предложении, то в качестве ответа будет получено последнее слово.

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

Пользовательская функция

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

Перейдем в редактор Visual Basic (быстрый переход комбинацией клавиш Alt + F11), создаём новый модуль (Insert -> Module) и вставляем туда код функции:

Источник: tutorexcel.ru

Проверка ячейки на наличие текста (без учета регистра)

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

Вы также можете использовать фильтр для поиска текста. Дополнительные сведения можно найти в разделе Фильтрация данных.

Поиск ячеек, содержащих текст

Чтобы найти ячейки, содержащие определенный текст, выполните указанные ниже действия.

Выделите диапазон ячеек, которые вы хотите найти.

Чтобы выполнить поиск на всем листе, щелкните любую ячейку.

На вкладке Главная в группе Редактирование нажмите кнопку найти _амп_и выберите пункт найти.

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

Примечание: В критериях поиска можно использовать подстановочные знаки.

Чтобы задать формат поиска, нажмите кнопку Формат и выберите нужные параметры в всплывающем окне Найти формат .

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

В поле внутри вы можете выбрать лист или книгу , чтобы выполнить поиск на листе или во всей книге.

Нажмите кнопку найти все или Найти далее.

Найдите все списки всех вхождений элемента, который нужно найти, и вы можете сделать ячейку активной, выбрав определенное вхождение. Вы можете отсортировать результаты поиска ” найти все “, щелкнув заголовок.

Примечание: Чтобы остановить поиск, нажмите клавишу ESC.

Проверка ячейки на наличие в ней текста

Для выполнения этой задачи используйте функцию текст .

Проверка соответствия ячейки определенному тексту

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

Проверка соответствия части ячейки определенному тексту

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

Примечание: Функция Поиск не учитывает регистр.

Источник: support.office.com