Выбрать часть текста в ячейке excel

30.11.2012 Полезные советы, Управленческий учет

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

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

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

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

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

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

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

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

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

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

=ЛЕВСИМВ(текст; [количество_знаков])

=ПРАВСИМВ(текст; [количество_знаков])

Где:

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

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

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

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

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

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

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

Где:

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

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

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

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

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

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

=ПСТР(С7;29;2)

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

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

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

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

Где:

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

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

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

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

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

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

=ПСТР(C7;ПОИСК(«?Ф00000»;C7);2)

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

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

Как извлечь часть текста в Эксель

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

Для получения части текста, в Эксель есть 3 функции:

1. ЛЕВСИМВ(Строка; Количество_символов) – выводит заданное количество символов с левого края. Например, =ЛЕВСИМВ(А1;10) выведет 10 первых символов строки в ячейке А1 . Функция имеет 2 обязательных аргумента – Строка-источник и количество выводимых символов;

Функция ЛЕВСИМВ в Эксель

1. ПРАВСИМВ(Строка; Количество_символов) – функция схожа с предыдущей, она выводит заданное количество символов справа. То есть, =ПРАВСИМВ(А1;10) в результате выдаст 10 последних символов из строки А1 .

Функция ПРАВСИМВ в Excel

1. ПСТР(Строка; Начальный_символ; Количество символов) – выбирает из текста нужное количество знаков, начиная с заданного. Например, =ПСТР(А1;5;3) выведет 3 символа начиная с 5-го (5-7 символы строки).

Функция ПСТР в Эксель

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

Функции ЛЕВИСМВ, ПРАВСИМВ, ПСТР – это простой и мощный инструмент, если используется в комбинации с другими текстовыми функциями. Вы увидите это в уроке-практикуме по строчным функциям.

А следующий пост мы посвятим поиску нужного текста в строке. Заходите и читайте. Только хорошее владение функциями позволит вам эффективно выполнять задачи в Microsoft Excel!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример 1

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

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

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

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

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

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

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

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

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

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

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

Пример 2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры использования функции ЛЕВСИМВ в 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)),»Число не введено»)’ class=’formula’>

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

Примечание: данный алгоритм нельзя назвать гибким и он имеет некоторые недостатки (например, не учтен вариант ввода сразу целого числа). Он приведен в качестве примера использования функции ЛЕВСИМВ. Для расчетов лучше использовать встроенную функцию ОКРГУГЛ и подобные ей.

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

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

• текст – ссылка на ячейку, содержащую текст, или текстовая строка, в которой требуется выделить требуемое количество символов слева. Аргумент обязателен для заполнения.
• [число_знаков] – необязательный для заполнения аргумент; числовое значение, характеризующее количество символов, которые требуется выделить из текстовой строки.

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

• ЛЕВСИМВ(«текст»;200%) вернет значение «те» (преобразование процентов в числовое значение);
• при аргументах: («текст»;ИСТИНА) вернет «т» (логическое ИСТИНА эквивалентно числовому значению 1);
• при аргументах: («текст»;ЛОЖЬ) вернет пустое значение «», поскольку логическое ЛОЖЬ эквивалентно числовому 0 (нуль);
• при аргументах: («текст»;15/7) вернет значение «те».

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

Извлечение слов из текста в Excel

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

Важное замечание!

Задача 1. Подсчет количества слов в тексте

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

Итак, слова в тексте отделены друг от друга пробелами. Пробелов всегда на 1 меньше, чем слов (действительно, между двумя словами — один пробел, между тремя — два и т.д.). Значит, достаточно подсчитать пробелы и прибавить к ним единицу, чтобы узнать число слов в ячейке.

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

Функция ПОДСТАВИТЬ() имеет 4 аргумента:

1) Ячейка с текстом

2) То, что нужно заменить (указывается в кавычках)

3) То, на что нужно заменить (указывается в кавычка)

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

Первой функцией мы можем узнать длину текста в ячейке. Второй функцией мы можем заменить все пробелы в тексте на «» (пусто), то есть удалить. Если после удаления мы подсчитаем длину текста еще раз, то она сократится ровно на число удаленных пробелов. Таким образом, чтобы подсчитать пробелы, нужна формула:

Прибавим к ней единицу — и получим число слов в ячейке.

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

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

Раз слова отделены пробелами, то первое слово от второго отделено первым в тексте пробелом. Значит для извлечения первого слова, нам нужно найти первый пробел и извлечь из текста всё, что идет до него.

Найти символ в тексте поможет функция НАЙТИ() . У нее есть 3 аргумента:

1) Искомый текст;

2) Текст, в котором ищем;

3) С какого символа начать поиск (необязательный, если опущен — функция ищет с самого начала).

Результатом работы функции является позиция первого символа найденного текста в строке. Например, формула =НАЙТИ(«плюс»;»Один плюс один») вернет в результате число 6 (именно с этой позиции начинается искомое слово «плюс»). Если в строке несколько раз содержится искомое слово, то вернется результат для первого вхождения.

Функция НАЙТИ работает с учетом регистра и без подстановочных знаков. Если нужно их использовать, или не учитывать регистр — есть аналогичная функция ПОИСК() .

Теперь мы можем найти позицию первого пробела

Извлечь символы до пробела поможет функция ЛЕВСИМВ . Ей нужно указать текст, а также число символов, которое нужно извлечь начиная с первого. Если найденный пробел, например, 6-ой символ, то нам нужно извлечь 6-1=5.

Формула для извлечения первого слова:

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

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

Задача 3. Извлечение последнего слова из текста

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

• подсчитаем, сколько в ячейке пробелов, воспользовавшись приемом из Задачи 1.

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))

• с помощью функции ПОДСТАВИТЬ заменим последний пробел в тексте на символ, которого там точно нет (например, «*» или «$»). Чтобы заменить именно последний пробел, добавим в качестве необязательного аргумента формулу из предыдущего пункта (то есть число пробелов, так как оно равно номеру последнего пробела)

=ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))

• теперь, когда последний пробел заменен на «*», мы можем узнать его позицию с помощью функции НАЙТИ() . В качестве текста, в котором ищем, укажем предыдущую формулу

=НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

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

=ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

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

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

=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))));A1)

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

Function РЕВЕРС(str As String)

Это макрофункция. Теперь, используя ее, можно отразить текст зеркально, извлечь первое слово, как в Задаче 2 и отразить его обратно. Формула:

Берите на вооружение, если любите макросы.

Задача 4. Извлечение n-ного слова из текста

Самый интересный пример. Для решения подобной задачи нужно применить фантазию. Разберем решение поэтапно.

Помимо уже использовавшихся в статье функций, нам понадобятся еще 2:

1) ПОВТОР() . Умеет повторять указанный текст указанное число раз.

2) ПСТР() . Имеет 3 аргумента:

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

Теперь мы вооружены всем нужным инструментарием. Приступаем:

• Для начала воспользуемся функцией ПОВТОР() , чтобы вместо 1 пробела в тексте вставить число пробелов, равное длине исходного текста. Количество повторений узнаем через уже известную функцию ДЛСТР() . Итак, формула:

=ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)))

• Теперь каждое слово отделено друг от друга числом символов, равным общей длине строки. Чтобы извлечь слово под номером n, нам нужно узнать позицию какого-нибудь пробела между словом n и n-1 в обработанном удлиненном тексте. Это может сделать формула ниже (+1 в ней нужно для правильного поиска первого слова):

=ДЛСТР(A1)*(n-1)+1

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

• Теперь мы может извлечь из найденной позиции число символов ДЛСТР(A1) (на рисунке — m ) и извлеченный текст всегда будет захватывать нужное нам слово целиком (с некоторым количеством предшествующих и последующих пробелов)

=ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1))

• Осталось избавиться от лишних пробелов функцией СЖПРОБЕЛЫ()

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1)))

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

Задача 5. Извлечение имя файла из полного пути к нему

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

Как мы знаем, директории в ссылках отделены друг от друга символом «». После последнего такого символа расположено нужное нам имя файла. Похоже на задачу с извлечением последнего слова? Только вместо пробелов теперь символ обратной косой черты.

Берем формулу из задачи 3 и заменяем в ней пробелы на «».

Получим следующую формулу:

Вставляем в ячейку и вуаля! Имя файла извлечено.

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

Поддержать наш проект и его дальнейшее развитие можно вот здесь .

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

Отбор части символов из текста

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

Функция LEFT ( ЛЕВСИМВ )

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

LEFT ( текст ;количество_символов)

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

Функция RIGHT ( ПРАВСИМВ )

Аналогичное действие мы можем сделать и с правой части текста.

RIGHT ( текст ;количество_символов)

Функция MID ( ПСТР )

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

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

Синтаксис функции следующий:

MID(текст;начальный_номер;количество_символов)

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

Для получения части текста, в Эксель есть 3 функции:

1. ЛЕВСИМВ(Строка; Количество_символов) – выводит заданное количество символов с левого края. Например, =ЛЕВСИМВ(А1;10) выведет 10 первых символов строки в ячейке А1 . Функция имеет 2 обязательных аргумента – Строка-источник и количество выводимых символов;

Функция ЛЕВСИМВ в Эксель

1. ПРАВСИМВ(Строка; Количество_символов) – функция схожа с предыдущей, она выводит заданное количество символов справа. То есть, =ПРАВСИМВ(А1;10) в результате выдаст 10 последних символов из строки А1 .

Функция ПРАВСИМВ в Excel

1. ПСТР(Строка; Начальный_символ; Количество символов) – выбирает из текста нужное количество знаков, начиная с заданного. Например, =ПСТР(А1;5;3) выведет 3 символа начиная с 5-го (5-7 символы строки).

Функция ПСТР в Эксель

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

Функции ЛЕВИСМВ, ПРАВСИМВ, ПСТР – это простой и мощный инструмент, если используется в комбинации с другими текстовыми функциями. Вы увидите это в уроке-практикуме по строчным функциям.

А следующий пост мы посвятим поиску нужного текста в строке. Заходите и читайте. Только хорошее владение функциями позволит вам эффективно выполнять задачи в Microsoft Excel!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

У нас имеется данные для анализа производимой продукции. Нам нужно автоматически выделить все изделия, которые были выпущены в 2006 году. К сожалению, в таблице недостает еще одного столбца содержащего значения года производства каждого изделия. Но видно, что в фрагменте идентификатора (ID) изделия указывается год производства. В таком случае нам нужно выполнить поиск по части текста в Excel. А потом мы будем использовать условное форматирование с текстовыми функциями в формуле. Благодаря этому молниеносно реализуем решение задачи.

Пример таблицы производимой продукции:

Чтобы на основе идентификатора выделить изделия 2006-го года выпуска выполним шаги следующих действий:

1. Выделите диапазон ячеек A2:A10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Выберите: «Использовать формулу для определения форматируемых ячеек».
3. Чтобы выполнить поиск части текста в ячейке Excel, введите формулу: =ПСТР(A2;5;4)=»2006″
4. Нажмите на кнопку «Формат», чтобы задать красный цвет заливки для ячейки. И нажмите ОК на всех открытых окнах.

Экспонированные цветом изделия 2006-го года выпуска:

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

Функция ПСТР и ее пример в использования условном форматировании

Чтобы легко понять, как удалось экспонировать цветом определенные значения с помощью условного форматирования разберем этапы действий в двух словах. Сначала мы извлекаем часть текста, а потом сравниваем его с требуемым значением. Но как из ячейки извлечь часть текста в Excel? Обратим внимание на функцию =ПСТР() в формуле правила. Данная функция возвращает часть строки, взятой с каждой ячейки столбца A, что указано в первом аргументе. Ссылка в первом аргументе должна быть относительной, так как формула применяется к каждой ячейке столбца A. Во втором аргументе функции указывается номер символа исходного текста, с которого должен начаться отрезок строки. В третьем аргументе указывается количество символов, которые нужно взять после определенного (во втором аргументе) символа исходного текста. В результате функция =ПСТР() возвращает только часть текста длинной 4 символа взятого начиная с 5-ой буквы в каждой ячейки из столбца А. Это «2005» или «2006». После функции стоит оператор сравнения к значению строки «2006». Если ячейка содержит такую часть текста значит ей будет присвоен новый формат.

Аналогичным способом можно использовать и другие текстовые функции в условном форматировании. Например, с помощью функции =ПРАВСИМВ() мы можем экспонировать цветом определенную группу товаров из партии C. Для этого нужно использовать формулу:

Здесь все просто функция позволяет выбрать часть текста из ячейки Excel, начиная с правой стороны исходного текста. Количество необходимых символов указано во втором аргументе функции ПРАВСИМВ. После чего все что возвращает функция сравнивается с значением строки «C».

Во всех выше описанных примерах очень легко применять текстовые функции в условном форматировании так как длина строк в исходных данных одинаковая. Но что, если у нас в исходных данных разного типа индикаторы с разной длинной символов, а нам все еще нужно выделять 2006-й год или группу «C».

В решении данной задачи нам поможет дополнительная текстовая функция в формуле =НАЙТИ(). В первом случаи формула будет выглядеть так:

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

Функция =НАЙТИ() выполняет поиск фрагмента текста в ячейке Excel. Потом возвращает номер символа, в котором был найдет текст, который задан в первом аргументе. Во втором аргументе указываем где искать текст. А третий аргумент – это номер позиции с какого символа вести поиск в исходном тексте. Третий аргумент позволяет нам смещаться по строке. Например, если в идентификаторе 2 раза используется символ «C». В таком случае третий аргумент пользователь задает в зависимости от ситуации.

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

Выделение содержимого ячеек в Excel

​Смотрите также​ открытой форме выбирать​ в формулах..лых..ЛОХ!​

​т.е для ПИТЬЕВОЙ​​ универсальный, но вариант.​ с учетом этого​ следуют друг за​Владимир​

Выделение ячеек

1. ​ верный результат. Так​ за помощь.​ отобразиться интервал, затем​ Например, рассмотрим некоторые​ для округления дробных​

2. ​ (слово)).​ текст, но не​ перечня и, в​ удовлетворяющих 1 критерию​В Excel можно выделять​ выбирать диапозоны и​KuklP​

3. ​ «ДАНОН» Персик-Маракуйя 290​ В ячейку пишем​ замечания))​ другом без разрывов,​

Выделение строк и столбцов

1. ​: Вот на примитивном​ что может быть​elleb​ снова прибавляем 11​ возможные варианты записи​ чисел до ближайших​Слово Разряд в списке​

2. ​ совпадающих с ним.​ случае успеха, возвращает​ (без учета РЕгиСТра).​ содержимое ячеек, строк​ применять макрос ?​: The_Prist дал полностью​

3. ​выдаст 2900​ =ТОЛЬКОЦИФРЫ(А1)​Guest​ то извлечь текст​

Выделение таблицы, списка или листа

1. ​ уровне​ имеет смысл вписать​: Решение задачи поста​ и т.д…​ функции ЛЕВСИМВ и​

2. ​ целых значений с​ не встречается (склонения​ Например, слово в​​ начальную позицию найденного​​ Критерий введем в​ или столбцов.​

​Юрий М​​ исчерпывающий ответ. И​У меня это​Function ТОЛЬКОЦИФРЫ(ячейка As​: и если не​ числа из строки​mazayZR​ слово целиком.​ 14 с помощью​Заранее спасибо)​ результаты ее выполнения:​ использованием формул для​

support.office.com

Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL

​ не считаются). А​ списке Мотодрель содержит​ значения. В противном​ ячейку​Примечание:​: Надеюсь, sva меня​ то, что я​ тоже не сразу​ Range) As Long​ всегда будет «=»​=ПСТР(A4;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПСТР(A4;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A4)));1)*1);0);СУММ(ЕЧИСЛО(ПСТР(A4;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A4)));1)*1)*1))​: а вот сюда​vikttur​ макросов в теме:​Перед 6195 нолик​

​ЛЕВСИМВ(«текст»;200%) вернет значение «те»​ работы с текстом.​ вот слово Техник​текст​ случае возвращается ошибка​B6​ Если лист защищен, возможность​ простит.​ хотел написать -​ было отработано -​

Исходная таблица с текстовыми значениями

​Dim i As​​ перед счётом​​/извлечь из строки​ еще загляните… взято​:​ Извлечение части данных​ пропуститила.​ (преобразование процентов в​Форма для округления числовых​ встречается как в​

Задача 1 (поиск значений в списке, совпадающих с единственным критерием)

​Дрель, но​ #ЗНАЧ!​. Кроме того, зададим​ выделения ячеек и​Rama​​ и то что​​ см. выше :-)​ Integer, Результат As​

• ​Serge​ символы(начиная от первой​ с форума, автора​Yberpleks​
• ​ из одной ячейки​Serge 007​ числовое значение);​ значений имеет следующий​ начале фраз, так​
• ​слово​Формула ПОИСК($A$6;A9) возвращает номер позиции​ 4 Типа поиска:​
• ​ их содержимого может​: Все отлично. Для​ умничка kim предложил.​

​Юрий М​ String​: С перепугу наваять​ цифры, всего столько​​ к сожалению не​​, не цитируйте все​ макросом​: См вложение​

Решение

​при аргументах: («текст»;ИСТИНА) вернет​ вид:​ и в середине​Дрель не содержится​ текстовой строки из​Точно совпадает (выделение ячеек,​​ быть недоступна.​​ себя я добавил​ Так что я​: Если «ДАНОН» на​Dim Символ As​ можно такое(см. вложение)​ символов сколько цифр)/​ записал…​ подряд!​chumich​Анастасия999​ «т» (логическое ИСТИНА​

​В ячейке B3 введена​ и конце.​ в этой ячейке.​

• ​ ячейки​ содержащих значения в​Чтобы выделить ячейку, щелкните​​ черный еще.​​ поступил правильно.​
• ​ порядок больше -​ String​ :-)​
• ​5. задача Лузера​mazayZR​
• ​Guest​: А как извелечь​: Спасибо за быстрый​ эквивалентно числовому значению​
• ​ следующая сложная формула:​Не смотря на то,​Чтобы выделить ячейки в​

​А9​

​ точности совпадающих с​ ее. Для перехода​Всем спасибо.​Юрий М​ беда. А вот​For i =​kim, там когда​​ — извлечь текст​​: а вот и​​: Всем привет!​​ цифры из ячейки?​

​ ответ!​ 1);​=5,ЛЕВСИМВ(A2+1,НАЙТИ(«.»,A2)-1),ЛЕВСИМВ(A2,НАЙТИ(«.»,A2)-1)),»Число не введено»)’​

​ что формулы похожи​ списке, которые содержат​, с которой начинается​ искомым текстом);​

• ​ к ячейке и​
• ​не ЛЕВСИМВ,ПРАВСИМВ…​
• ​: Поискал по Форуму.​
• ​ если такое с​

​ 1 To Len(ячейка)​ нулёвку сгоняют перед​​ от первой до​​ автор — Лузер™​Есть столбец, в​Нужно получить «3358»»​

​Andrei_D​при аргументах: («текст»;ЛОЖЬ) вернет​ class=’formula’>​ на те, что​слово​ текст, указанный в​Содержит (выделение ячеек, которые​ ее выделения также​код товара находится​

​ Действительно — оказывал​ водкой — это​Символ = Mid(ячейка,​​ счётом «=» нет,​​ последней цифры (если​вот и та​ каждой ячейки которого​ из «3358 -​

​: Добрый день, подниму​​ пустое значение «»,​​Логика работы:​ использовались в Задаче​​Дрель, нужно воспользоваться​​ критерии (если он​ содержат искомый текст​ можно использовать клавиатуру.​ в каждой строке​ помощь с формулами.​ за ошибку не​ i, 1)​ твоя формула знач#​

​ между цифрами есть​​ тема​ названия городов и​ Специальные процедуры/ Дайвинг».​ тему вопрос из​ поскольку логическое ЛОЖЬ​Первая функция ЕСЛИ проверяет​ 2, они от​​ формулами в файле​​ найден).​ в начале, в​Чтобы выделить диапазон, выделите​ в разных меятах..его​ Выглядело это так:​ считается.​

​If IsNumeric(Символ) Then​ даёт :(​ буквы или знаки​Sh_Alex​ улиц:​AleksSid​ этой же темы,​ эквивалентно числовому 0​ с помощью формулы​ них принципиально отличаются,​ примера на Листе​Например, для ячейки​ конце или середине​​ ячейку, а затем​​ нужно вытянуть в​ = A1​

​KuklP​​Результат = Результат​Было-бы «=», тогда​ они сохраняются)​: Пользовательская функция из​a1 «Саратов, ул…»,​: Какая, в данном​ но не много​​ (нуль);​​ ЕПУСТО, заполнена ли​ т.к. являются формулами​ «1 критерий (слово)».​А14​ строки);​ перетащите ее правый​

​ другую строку..​Rama​: Типун Вам…​ & Символ​ вообще огород городить​=ПСТР(ЛЕВСИМВ(A4;МАКС(ЕЧИСЛО(ПСТР(A4;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A4)));1)*1)*СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A4)))));ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПСТР(A4;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A4)));1)*1);0);ДЛСТР(A4))​ ячейки «выцепляет» цифры​a2 «Москва, ул…»​

​ случае, разница: цифры​
​ сложнее. необходимо выделять​при аргументах: («текст»;15/7) вернет​ ячейка, которая должна​ массива.​Понятно, что нужно выделить​формула ПОИСК($A$6;A14) вернет​Начинается (выделение ячеек, которые​ нижний край. Для​

Задача 2 (поиск СЛОВ в списке, совпадающих с единственным критерием)

​Serge​​: Уважаемые форумчане !​​DON​End If​ не надо, формулу​mazayZR​ и полученный String​Т.е. сначала идет​ или буквы? У​ значение из середины​ значение «те».​ содержать значение, которое​Функция ЛЕВСИМВ в Excel​ ячейки, в которых:​​ 1, а для​​ содержат искомый текст​​ этого также можно​​: Одной функцией не​Столкнулся с проблемой​

​: Ребята! А вопрос​Next i​​ я постом выше​​: да, пятый вариант​ преобразует в число.​ Город, потом заяпятая…​ вас цифры -​

​ текста в другую​Примечание 2: в Excel​

• ​ требуется округлить.​ используется для получения​
• ​точно содержится слово Дрель​А15​
• ​ в начале строки);​ использовать SHIFT+клавиши со​
• ​ обойдёшься​ выделения нужного текста​ в догонку можно?​

​ТОЛЬКОЦИФРЫ = Val(Результат)​ приводил :-)​ несколько изящнее, чем​Public Function число(смесь​

​Помогите пожалуйста записать​
​ часть текста.​
​ ячейку​
​ также существует схожая​
​Если число введено, запись​

Задача 3 (поиск нескольких слов в списке)

​ части текста и​ ИЛИ;​формула ПОИСК($A$6;A15) вернет​Заканчивается (выделение ячеек, которые​ стрелками.​Надо писать формулу​ в ячейках с​Изменяется значение в​End Function​

​22, 5 Кб​ поиск цифр исходя​

​ As String)​ код, который бы​​Код =ЛЕВСИМВ(B3;НАЙТИ(«-«;B3)-2)​Пример текста​ по принципу работы​ ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(«,»;B2)+1)) возвращает числовое​ возвращает заданное количество​текстовая строка начинается со​

​ 8 и обе​ содержат искомый текст​Чтобы выделить несмежные ячейки​ из нескольких функций.​ текстом. Стандарным методом​

​ ячейке и в​kim​​Serge​ из номера символа…​Dim i As​ брал только название​Можно так для​1122100098 надо выделить​ функция ЛЕВБ, возвращающая​ значение первого символа​ символов текстовой строки,​ слова Дрель ИЛИ;​

​ ячейки будут выделены.​ в конце строки).​​ и диапазоны ячеек,​Собственно «вырезает» (возвращает​ (Найти и Заменить)​ формуле представленной ввиде​: Или формулой массива:​: Может Вы сразу​

​интересно, а «прополоть»​​ Long​ Города…​ чисел. Код =ПСТР(B3;1;ПОИСК(«-«;B3)-2)​ в отдельную ячейку.​ определенное количество символов​ после запятой и​ при этом отсчет​

Задача 4 (поиск нескольких слов в списке, общий случай)

​текстовая строка заканчивается словом​ Если в ячейке​В качестве примера Критерия​ выберите их, удерживая​ заданное число знаков​ только заменяется без​ текста меняется значение!​=ПСТР(A1;ПОИСКПОЗ(;—ЕОШ(-ПСТР(A1;СТРОКА($1:$999);1)););ПОИСКПОЗ(2;1/НЕ(ЕОШ(-ПСТР(A1;СТРОКА($1:$999);1))))-ПОИСКПОЗ(;—ЕОШ(-ПСТР(A1;СТРОКА($1:$999);1));)+1)​ всех тузов из​ строку получится? :-)​For i =​Как сделать это​ Для текста. Код​

​используя формулу​ текстовой строки слева​ сравнивает полученное значение​ символов начинается слева.​ Дрель ИЛИ;​ не содержится искомый​ используем текст «Дрель»,​ нажатой клавишу CTRL.​

​ с указанной позиции)​ формата. Мне нужно​ 1,25 иземеняется на​lapink2000​ рукава вытащите (я​Guest​ 1 To Len(смесь)​ в EXCEL при​

excel2.ru

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

​ =ПСТР(B3;ПОИСК(«-«;B3)+2;999)​у меня остается​ на основе определенного​ с числом 5.​Пример 1. В таблице​слово Дрель содержится в​ текст, то формула​

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

​ который введем в​Выберите букву в верхней​ функция ПСТР.​ необходимый текст (в​ 30 (30 х​: Вот такой вариант​ про пример, в​: если сорняки в​Select Case Asc(Mid(смесь,​

​ помощи «Левсимв» я​

​Yberpleks​ хвост​ количества байтов. Если​Если полученное значение больше​

​ Excel содержатся имена​ середине текстовой строки​ возвращает ошибку и​ ячейку​ части столбца, чтобы​Но «указанную позицию»​ тексте ячейки) в​

​ 78,9 х 0,45​ формулой. Более длинный​ котором есть все​ куче то без​ i, 1))​ знаю, хотелось бы​: Добрый день!​

​что необходимо добавить​

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

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

​ х 5)+20%​

​ но без матричного​ возможные условия)?​

​ проблем, доехал до​

• ​Case 48 To​ обойтись без EXELких​Народ, прошу помощи!!!!​ что бы оставалось​ язык с поддержкой​ результат выполнения функции​ текстом, определяющим их​В принципе можно записать​Примечание​. Естественно, для критерия​ Можно также щелкнуть​Не видя конкретной​
• ​ выделить красным и​И как текст​ ввода и очень​НАМ попроще будет​ начала включил прополочную​ 57​ формул…​

​Тема значит такая.​

​ только необходимое мне​

​ двухбайтовой кодировки, для​ ЛЕВСИМВ(B2+1;НАЙТИ(«,»;B2)-1), то есть​ тип данных. Необходимо​ одну большую формулу​: Обратите внимание, что​ можно использовать любой​ любую ячейку в​ структуры Вашего текста​ жирным.​ типа (1,25 х​ быстрый в пересчете:​

​ сделать то, что​

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

​ машину, начались помидоры​число = число​Пытливый​ У меня есть​ значение?​ получения первого символа​

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

​ будет выделена ячейка​ другой текст.​

​ столбце и нажать​ трудно посоветовать конкретно,​

​Спасибо.​

1. ​ 78,9 х 0,45​’=—ПСТР(ЛЕВСИМВ(A1;МАКС(ЕСЛИ(ЕЧИСЛО(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A1));НАЙТИ({0;1;2;3;4;5;6;7;8;9};A1))));МИН(ЕСЛИ(ЕЧИСЛО(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A1));НАЙТИ({0;1;2;3;4;5;6;7;8;9};A1)));32000)​ нужно ВАМ…​ выключил прополочную машину​ & Mid(смесь, i,​: Привет.​
2. ​ текст в ячейке​Andrei_D​ строки требуется в​ +1 по правилам​ переменных и записать​ дополнительные столбцы (C:F)​
3. ​ со словом Мотодрель,​Выбор Типа поиска организуем​ клавиши CTRL+ПРОБЕЛ.​ но если, например,​nilem​ х 5)+20% заставить​ZVI​
4. ​Hugo​

​удалить текст от​ 1)​Уточните задачу:​ Excel. для работы​: Так не получится?​ качестве аргумента указать​ округления.​ их в отдельный​=ИЛИ(ИЛИ(ЕСЛИОШИБКА(A9=$A$6;0));​ как ячейка содержащая​ с помощью Элементов​Выберите номер строки, чтобы​

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

​ коду товара предшествует​: например:​ считать!​: Ещё вот такое​: Ну при счёте​ первой до последней​End Select​…Помогите пожалуйста записать​

​ мне необходимо использовать​

​ Код =ПСТР(J17;НАЙТИ(«1122»;J17)+1;9)​

• ​ число 2, а​Если​ столбец.​ИЛИ(ЕСЛИОШИБКА(ПОИСК(» «&$A$6&» «;A9);0));​ критерий. Но, если​ управления формы Группа​ выделить ее целиком.​
• ​ (или находится в​Sub ert()​KuklP​ придумалось:​ 50:20 только​ цифры (если между​

​Next​

1. ​ код, который бы​ только часть этого​Июлька​ для получения последующих​Примечание: данный алгоритм нельзя​Исходная таблица данных:​ИЛИ(ЕСЛИОШИБКА(ПОИСК($A$6&» «;ЛЕВСИМВ(A9;ДЛСТР($A$6)+1));0));​
2. ​ нужно найти ячейки​ и Переключатель. Свяжем​ Можно также щелкнуть​ конкретно месте этого​Dim r As​: Знаю, но позориться​=ПРОСМОТР(99^99;—ЛЕВСИМВ(ПСТР(A11;МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A11&»0123456789″));15);{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}))​=INDEX(SUBSTITUTE(MID(A1;FIND(«=»;A1)+1;99);{«-«,»:»};»»);;MATCH(MIN(LEN(SUBSTITUTE(MID(A1;FIND(«=»;A1)+1;99);{«-«,»:»};»»)));LEN(SUBSTITUTE(MID(A1;FIND(«=»;A1)+1;99);{«-«,»:»};»»));))​ цифрами есть буквы​число = Val(число)​
3. ​ брал только название​ текста, остальное в​: Получается, спасибо​ второго, третьего и​ назвать гибким и​
4. ​Для выделения первого символа,​ИЛИ(ЕСЛИОШИБКА(» «&$A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)+1);0))​ содержащие​ все Переключатели с​ любую ячейку в​ кода) какой-то символ,​
5. ​ Range​ не хочу. Тут​ZVI​не глючит, но​ или знаки они​End Function​ Города…​

• ​ данном случае спам.​AlexM​ прочих символов необходимо​
• ​ он имеет некоторые​ которым является искомое​)​слово​
• ​ ячейкой​ строке и нажать​ то можно использовать​For Each r​ такие ЗУБРЫ формулисты:​
• ​: Вот так, пожалуй,​ правда тогда 5020​

​ удаляются)​С уважением, Александр.​и что дальше?​Вопрос: Существует-ли формула​: Здравствуйте.​ указывать соответствующие числа,​ недостатки (например, не​ название переменной, используем​Рассмотрим списки, элементами которых​Дрель (т.е. текст,​B9​ клавиши SHIFT+ПРОБЕЛ.​ функцию ПОИСК.​ In Range(«A1:A» &​ ZVI, kim, KL,​ даже лучше -​ тоже непонятно как​=ЛЕВСИМВ(A12;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПСТР(A12;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A12)));1)*1);0)-1) & ПСТР(A12;МАКС(ЕЧИСЛО(ПСТР(A12;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A12)));1)*1)*СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A12))))+1;ДЛСТР(A12))​Sh_Alex​ Броса повыше? удалял?​ позволяющая вычленять часть​Требуется Ваша помощь:​ кратные двум. Для​ учтен вариант ввода​

exceltable.com

Извлечение части данных из одной ячейки

​ формулу:​​ являются отдельные слова​ отделенный от других​. Теперь при выборе​Чтобы выделить несмежные строки​Olga /pilot/ stepanova​ Cells(Rows.Count, 1).End(xlUp).Row)​ да еще и​ если в тексте​ расценивать (может​DL​: И в продолжение​ выводил в др.ячейку?​ текста из ячейки​В одной ячейке​ языков с однобайтовой​ сразу целого числа).​Второй равен 1 по​ (не фразы). Выделять​ слов пробелами), то​ Типа поиска Точно​
​ или столбцы, выберите​: ctrl+x — вырезать,​

​With r.Characters(8, 6).Font​​ Юрий М, что​

​ нет числа, то​​ 502-0)​

​: непосредственно использовать 5​​ предыдущего поста.​ или в текущей​​ в Excel?​
​ вот это​ кодировкой функции ЛЕВСИМВ​​ Он приведен в​

​ умолчанию, поэтому функция​​ будем только ту​ нужно обратиться к​ совпадает в этой​ их номера, удерживая​ ctrl+v — вставить,​.Bold = True:​ мне только хвост​
​ выдаст ноль:​Hugo​
​ уже не прокатит​Переделал название функции.​ ячейке надо только​Для детального понимания​1I2430-110-Бело-синий, 110, Бело-синий​ и ЛЕВБ возвращают​ качестве примера использования​ возвращает первый символ.​ ячейку, которая содержит​ решению Задачи 2​ ячейке будет содержаться​ нажатой клавишу CTRL.​ ctrl+с — скопировать​ .Color = vbRed​ поджать…​=ПРОСМОТР(99^99;—ЛЕВСИМВ(ПСТР(A1&»0″;МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A1&»0123456789″));15);{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}))​: Ну ладно, возьмём​
​9 вложенных функций​
​Public Function ТолькоЦифра(смесь​ название города оставить?​

​ прикладываю Вам EXCEL​​, надо в одну​

​ идентичные результаты.​​ функции ЛЕВСИМВ. Для​ Аналогично выделим названия​

​ любое из слов-критериев​​ (см. ниже).​ число 1, при​Чтобы выделить список или​Tat v​End With​Юрий М​Предыдущая формула была​ реальный счёт 11-1​
​ эксель не кушает,​
​ As String)​Может проще текст​
​ файлик. Ту информацию​
​ ячейку цифры (110),​Анастасия999​
​ расчетов лучше использовать​ переменных из других​ (см. файл примера​Формула $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)) возвращает правую​

​ выборе Содержит -​​ таблицу, щелкните любую​: ЗАМЕНИТЬ либо ПОДСТАВИТЬ​

​Next​​: Сергей, с каких​

​ для A11, эта​​ :)​
​поэтому и приведены​
​Dim i As​ по столбцам сделать​​ которая мне необходима,​​ а другую слова,​: Подскажите, пожалуйста, как​ встроенную функцию ОКРГУГЛ​ строк. В результате​ Лист Неск критериев (слово​ часть каждого значения​
​ 2, Начинается -​

​ содержащуюся в них​​Виталий степанюк​End Sub​ это пор я​

​ — для A1.​​Serge​

​ формулы 1-3, вот​​ Long​
​ с разделителем «,»?​ выделена красным.​
​ первые 6 цифр​ с помощью формулы​ и подобные ей.​ получим:​ точно)).​ перечня длиной равной​ 3, Заканчивается -​ ячейку и нажмите​: Самый простой способ​sva​ формУлист? Да и​
​Обе — обычные​: В игре «Локомотив-Барселона»?!​ их можно использовать​For i =​Hugo​Z​ не нужны. Во​ извлеч из ячейки​Функция ЛЕВСИМВ используется наряду​Как видно на рисунке​Это можно сделать несколькими​ критерию, т.е. в​ 4 (см. файл​ клавиши CTRL+A.​ — становишься на​: Sub fnd()​
​ далеко не зубр!​ формулы, а не​Hugo​

​ как аргументы добавочных​​ 1 To Len(смесь)​: Sub tt()​

​: Да!​​ вложении образец.​ часть данных. Как​
​ с прочими функциями​ с помощью функции​ способами.​ нашем случае 5​ примера Лист 1​Чтобы выделить весь лист,​ нужную ячейку и​

​Dim sSlovo As​​ Колитесь. Я токма​ формулы массива.​: 11-1 :)​ функций, что и​Select Case Asc(Mid(смесь,​MsgBox Split([a1], «,»)(0)​

​=ПСТР()​​Заранее благодарна.​ правило данные, в​
​ для работы с​ ЛЕВСИМВ получилось вытащить​Вариант с использованием Массива​

​ (т.к. в критерии​​ критерий (текст)).​ нажмите клавиши CTRL+A​ нажимаешь F2 -​ String, c As​ если макросом…​
​ZVI​

​Hugo​ используется в 4​ i, 1))​End Sub​

CyberForum.ru

Формула на вычленение части текста.

​Yberpleks​​Июлька​
​ моем случае, что​
​ текстовыми строками в​ левую часть текста​ констант и имени.​ «Дрель» 5 букв),​Настроим Условное форматирование для​ или кнопку​ ячейка доступна к​ Range​
​DON​: Подправил для случая,​: Тьфу, хотел наоборот​ и 5​
​Case 48 To​Пытливый​: Эммм….​: Код =ПСТР(ПСТР(A2;ПОИСК(«, «;A2)+2;99);ПОИСК(«,»;ПСТР(A2;ПОИСК(«,​ числа, иногда с​

​ Excel (ПРАВСИМВ, СИМВОЛ,​​ из каждой ячейки​
​В этом случае​

​ а затем сравнивает​​ выделения ячеек, удовлетворяющих​
​Выделить все​ редактированию… можно нажимать​sSlovo = Application.InputBox(Prompt:=»Введите​

​: А если немного​​ когда число -​
​ написать 1-11​

​Веталь​​ 57​: Sub GetCity()​А можно объяснить​ «;A2)+2;99))+2;99) и Код​

​ использованием «(» или​​ СЦЕПИТЬ, ДЛСТР и​

​ по одному (первому)​​ в правиле Условного​ ее с критерием.​ критерию. Для этого:​в левом верхнем​

​ стрелочки «влево» и​​ слово для поиска»,​ попозорится?​ в конце строки:​Hugo​
​: Даны например:​ТолькоЦифра = ТолькоЦифра​ActiveCell.Value = Left(CStr(ActiveCell.Value),​ как она работает​ =ЛЕВБ(ПСТР(A2;ПОИСК(«, «;A2)+2;99);ПОИСК(«?,»;ПСТР(A2;ПОИСК(«, «;A2)+2;99)))​

​ «-«. Например: значение​​ прочими) и имеет​ символу.​ форматирования нужно ввести​

​ Если оба выражения​​выделите ячейки, в которые​ углу.​ «вправо», а также​ Default:=»»)​Очень нужно!​
​=ПРОСМОТР(99^99;—ЛЕВСИМВ(ПСТР(A1&»_0″;МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A1&»_0123456789″));15);{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}))​: Хотя в АА​

​Локомотив-Барселона=5-0​​ & Mid(смесь, i,​ InStr(1, CStr(ActiveCell.Value), «,»)​ с моим файлом?​Июлька​ в ячейке -​ следующую синтаксическую запись:​​ формулу =ИЛИ($A2=Слова), где​ совпадают, то это​ введен перечень инструментов​Примечание:​ выделять текст мышкой..​If sSlovo =​

​kim​​ZVI​​ результат от этого​​Локомотив Барселона=3-3​ 1)​

planetaexcel.ru

VBA Часть текста из ячейки.

​ — 1)​​V​
​: спасибо​ «(123)0008», необходимо перенести​=ЛЕВСИМВ(текст;[число_знаков])​Пример 2. В таблицу​
​ Слова — это Имя​
​ означает, что значение​
​ (​ В некоторых случаях выделение​
​ или с кнопкой​ «false» Then Exit​: DON, макрофункция ВЫЧИСЛИТЬ()​: То же самое​
​ не изменился.​Локомотив-Барселона=1:2​End Select​End Sub​: для данного примера.​AlexM​

​: Здравствуйте.​​ только данные после​
​текст – ссылка на​
​ из другого программного​

​ в Критерии.​​ (Главная/ Стили/ Условное​ Советы о том,​
​ нужно.​If InStr(c.Value, sSlovo)​

planetaexcel.ru

Выцепить цифры из содержимого ячейки

​ изменять массив констант​​Примечание​ форматирование/ Создать правило)​

​ как устранить эту​​Vlada​ > 0 Then​: Скорее всего вы​:​ заменить…​

​50​​ оставляет только текст.​Hugo, это вообще​
​ справка по этой​ вот это 1I2430-110-Бело-синий,​

​ символов, которые требуется​​Описание:​ на диапазон ячеек​ ячейки содержащие​выберите требуемый формат, например,​
​ текст. Рассмотрим разные​ 003 и так​

​ красный цвет фона​​ варианты: выделение ячеек,​ далее, нужно вырезать​
​ по отступу. Мое​ И видел как​
​ пользовательская функция (макрос),​ другие ячейки выводить​: В данном случае​ i, 1))​: Только если в​ «;A2)+3;99);ПОИСК(» «;ПСТР(A2;ПОИСК(«ки «;A2)+3;99))-1)​ одну ячейку, а​

​ цифры в другую..​​: …..​Примечания 1:​
​ слева, количество символов​

​ если в post_129271.xls​​ чертой, а в​=MID(A1;LEN(A1)-2;1)& RIGHT(A1;1)​
​ 47​ запятой — выдаст​: Такс, с формулой​Диапазон ограничен жирной​
​Serge 007​

​Если необязательный аргумента [число_знаков]​​ в которой соответствуют​ в ячейки в​ слов пробелами), то​
​Если в качестве примера​

​ порядковому номеру символа​​ столбце F, придется​ нужно обратиться к​ Критерия используется текст​

​ решению Задачи 2​​ «Дрель», а Тип​ искомый текст в​ формул — выделить​: Огромное спасибо sva,​ утверждению: Юрий М​: Формула на английском:​
​ быть и однозначными(меньше​Если Локо не​ 1)​

​ проверку (на Instr()​​ уже не осилил.​ из них надо​
​(123)0008​
​ возвращен первый символ​ выполняет поиск порядкового​Вариант с использованием динамического​ (см. ниже).​ поиска выбран Точно​

​ начале, в конце​​ 001 — Меню​ то что нужно.​ — макрушник:-)​

​=LOOKUP(2^64,—LEFT(MID(A1&»_0″,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&»_0123456789″)),15),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))​​ 10) , и​

​ наклепает Барсе более​​Case 58 To​

​ например или UBound())​​У меня кстати​ в одну ячейку​

​Код =ПОДСТАВИТЬ(A2;123;»») В​​ строки. Например, функция​ номера этого символа​ диапазона​

​Если критерий содержится в​​ совпадает (1), то​ или середине строки.​ Правка — Копировать​Мои мечты: Если​

​То DON: Вам​​ZVI​ трёхзначимыми….​

​ необходимо выдергивать из​​ (если в выделеном​

​123456​​ «т».​ значение). Единица (+1)​ добавлять новые слова-критерии​

planetaexcel.ru

выделение цифры из текста

​ то ПОИСК() вернет​​ только ячейка​ ячейку, в случае​
​ в ту ячейку,​ сделали возможным выбора​То kim: «(1,25​
​ случай пример. Формула​ такое!?…смотрите прикрепл.файл….нужно чтоб​=ПСТР(ПРАВСИМВ(A1;3);1;1)&ПРАВСИМВ(A1;1)​ & Mid(смесь, i,​

​ случаи.​​ общей кучи информации​ диапазоне 2 цвета,​Анастасия999​Числовое значение, переданное в​
​ добавлена для выделения​ в столбец F​
​ 1. Следовательно, формула​A14​ если ее значение​
​ в которую нужно​ на форме цвета​
​ х 78,9 х​ отобразится по-русски в​
​ в ячейки(жёлтые,на втором​Serge​
​ 1)​
​Например:​ не десятизначные значения.​
​ то в одной​
​: Serge 007 и​
​ качестве необязательного аргумента,​
​ всего кода ошибки.​

​ и при этом​​ ПОИСК($A$6;A9)=1 вернет ИСТИНА​
​, содержащая значение​

​End Select​​Sub tt()​Сергей​
​ ячейке, тоже должно​

​Next​​a = Split([a1],​: вот формула V,​ быть 2 цвета)​
​ спасибо вам ОГРОМНОЕ​

​ из диапазона неотрицательных​​ которые вернет функция​ формулы и имена.​ начинающихся с искомого​
​с искомым текстом.​

​Эта статья создана для​​ Вставить (или через​

​: .​​ не стал позориться,​ английской и т.п.​ первого листа(т.е. 5-1-4-6…сверху​
​А если предварительно​

​End Function​​ «,»)(0)​ че с ней​ а в другую​ за все предложенные​ чисел, то есть​

​ ПРАВСИМВ, работающая аналогично​​Вариантбез использования имен​
​ текста.​

​Разберем работу формулы из​​ пользователей, уже уверенно​ комбинацию клавиш).​Юрий М​ мне показалось, что​kim​ вниз)…..если непонятно обьяснил​

​ заменить » «​​С уважением, Александр.​
​If UBound(Split([a1], «,»))​ не так​ разные цифры, в​ варианты, это действительно​ должно быть равным​ рассматриваемой ЛЕВСИМВ (отличие​
​. Формула =ИЛИ($D2=»слово1″;$D2=»слово2″;$D2=»слово3″) позволяет​Совет:​
​ вышеуказанного правила Условного​

​ применяющих Условное форматирование.​​Юрик​: >> Мои мечты:​

​ автор хочет это​​: Сколько вариантов!​ — спрашивайте​ на «=», то​DL​ Then MsgBox a​
​Yberpleks​ одной ячейке должны​
​ очень облегчило мне​
​ нулю или больше​ – возвращает символы​ отказаться от использования​Для настройки поиска​

​ форматирования.​​ Для тех, кто​: ctrl –F, появится​ Если бы Вы​ выражение в виде​Владимир, красиво, черт​Hugo​ можно так:​

​: ///всё формулы массива,​​End Sub​

​: Да все так.​​ быть собраны разные​ работу.​
​ нуля. В противном​ справа, а не​ имен. Платой за​ ячеек, значения которых​Функция ВЫБОР() позволяет в​ только начинает использовать​ окно Найти и​ еще сделали возможным​
​ формулы.​ подери! Ну почему​: 1)​=ПСТР(A1;НАЙТИ(«=»;A1)+1;1)&ПРАВСИМВ(A1;1)​ строка в А4///​

​Guest​​ Я только механизм​ цифры.​Можно еще одну​ случае будет возвращен​ слева строки).​ это станет необходимость​ удовлетворяют критерию с​ одной формуле совместить​

​ этот инструмент, рекомендуется​​ заменить. На закладке​ выбора на форме​KuklP​ такие конструкции мне​Guest​kim​

​1. позиция последней​​: kak sdelat formulu​ работы этой формулы​
​Левые значения до​

​ задачку:​​ код ошибки #ЗНАЧ!.​В результате получим:​ изменения правила Условного​ учетом РЕгиСТРА, читайте​

​ 4 формулы для​​ ознакомиться с базовой​ заменить в поле​ цвета и «жирности»​: Вот же тормоз!​
​ в голову не​: С ПЕРВЫм вопросом​

​: Можно и такое​​ цифры в строке​ esli?​ понять пока не​ первой запятой не​Есть последовательность номеров​Если в качестве аргумента​Выделим часть текста ошибки:​ форматирования при каждом​
​ статью Выделение ячеек​ всех Типов поиска:​
​ статьей по этой​ Найти вводите ПРАВСИМВ,​Можно в свободной​ Читайте:​ приходят? Нужно​ разобрался….со вторым знает​ наваять с перепугу​=МАКС(ЕЧИСЛО(ПСТР(A4;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A4)));1)*1)*СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A4))))​ctob pervuyu bukvu​

​ могу.​​ нужны.​ от 06164 до​
​ [число_знаков] будет переданы​В данном случае удобнее​

​ изменении слов-критериев.​​ c ТЕКСТом с​Точно совпадает: $A$6=A9;​ теме Условное форматирование в​ в поле заменить​
​ ячейке задать нужное​И видел как​

​ работать над собой!​​ кто чего??​ :)​2. позиция первой​ bral Bolshim​

​Буду по кусочкам​​Июлька​ 06211. Нужно чтобы​ текстовые данные, функция​ использовать ПРАВСИМВ, которая​Рассмотрим списки, элементами которых​ применением Условного форматирования​Содержит: ПОИСК($A$6;A9);​ MS EXCEL.​

​ на – ПРАВСИМВ.​​ форматирование, а макрос​ Вы с формулами…​На пару символов​asya​=ИНДЕКС(ПОДСТАВИТЬ(ПСТР(A1;НАЙТИ(«=»;A1)+1;99);{«-«;»:»};»»);;ПОИСКПОЗ(МИН(ДЛСТР(ПОДСТАВИТЬ(ПСТР(A1;НАЙТИ(«=»;A1)+1;99);{«-«;»:»};»»)));ДЛСТР(ПОДСТАВИТЬ(ПСТР(A1;НАЙТИ(«=»;A1)+1;99);{«-«;»:»};»»));))​

planetaexcel.ru

Найти и выделить часть текста в ячейке

​ цифры в строке​​Elesin​
​ разбираться))​: Без макроса только​ в ячейках в​ ЛЕВСИМВ вернет код​ является, по сути,​ являются фразы. Выделять​ (с учетом РЕгиСТРА).​Начинается: ПОИСК($A$6;A9)=1;​Пусть в диапазоне​ Жмёте Заменить всё.​ будет смотреть в​DON​
​ короче:​

​: Доброе время суток,​​Веталь​
​=ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПСТР(A4;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A4)));1)*1);0)​
​: Всем привет.​V​
​ так.​ столбец отображалось следующее:​ ошибки #ЗНАЧ!.​
​ зеркальным отражением ЛЕВСИМВ.​
​ будем только ту​Определить, есть ли​
​Заканчивается: $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)).​
​А9:A17​
​При этом ничего​

​ эту ячейку и​​: Вы правы на​
​=ПРОСМОТР(9^16;—ПСТР(ПСТР(A1&0;МИН(НАЙТИ({1;2;3;4;5;6;7;8;9;0};A1&1234567890));15);1;СТРОКА($1:$15)))​ помогите пжл,​: Огромное спасибо.​
​3. всего цифр​Задача такая:​: надеюсь вы поняли​
​chumich​ в первой -​Если число, переданное в​
​ Для определения второго​ ячейку, которая содержит​
​слово​В зависимости от значения​имеется список с​ выделять не надо,​
​ применять формат к​
​ все 100% автор​

​kim​​есть текст, н-р​а если после​ в строке​есть ячейки с​ что за основу​: Ой, не туда,​

​ «06164-06175»; во второй​​ качестве необязательного аргумента,​ аргумента используется запись​
​ любое из слов-критериев:​в списке несколько​ в ячейке​ перечнем инструментов (см.​ замена произойдёт на​

​ нужному фрагменту.​​ хочет это выражение​

​ файл примера). Список​​ всём листе.​
​Rama​ в виде формулы.​ подчеркивание :)​

​ гр. Персик-Маракуйя -​​ пробел пропишется то​/здесь первое *1​123234234​ буквы слова заявки​

​Спасибо, но так​​ т ретьей -​ в текстовой строке,​

​ символа «u» в​​ середине или конце​ содержится ли в​, связанной с Переключателями,​
​ содержит как ячейки​

planetaexcel.ru

С помощью какой функции можно ВЫРЕЗАТЬ часть текста из ячейки в ЭКСЕЛЕ ?

​Может быть, я​
​: Супер !!​А то с​ZVI​мне нужно вытащить​ что делать???​

​ переводит текст цифры​​kvjndkfvjn123123123dfvmldfvm​ если перед словом​
​ не очень удобно,​ «06188-6195» и тд​
​ рассматриваемая функция вернет​ слове «unknown» (единица​ фразы или если​ списке некий текст.​
​ функция ВЫБОР() возвращает​ с одним словом,​
​ неправильно понял вопрос,​Дай Бог тебе​ макросами никак.​: Ну да, Игорь,​ из названия, только​вроде есть какая​ в число, второе​123123123klsjdnksdjcn​ заявки будет другое​ все равно придется​

​ до «06200-06211» Т.е.​​ весь текст целиком.​ вычтена для удаления​ фраза целиком совпадает​

​ Разница в этих​​ соответствующую формулу.​

​ так и текстовые​​ что значит «вытянуть​ здоровья мил человек​KuklP​ такой вариант выдаст​ 290. Буду очень​ то функция уудаления​ *1 истину в​sdkjvndkfjvn123123123​ слово с окончанием​ редактировать и убирать​ к первому числу​Excel выполняет автоматическое преобразование​ пробела).​ с одним из​

​ подходах проявляется при​​Функция ПОИСК() пытается найти​ строки (несколько строк,​ в другую строку»,​ !​: А я об​ лишний ноль, если​ благодарна за помощь​ лишних пробелов!!!??​ 1/​Как выбрать из​ на ки то​ повторяющиеся значения. Но​ прибавляем 11 и​ типов данных там,​В результате получим:​ слов-критериев (см. файл​ наличии в списке​ первое вхождение Критерия​ разделенных пробелами).​ а с заменой​Rama​ этом ни сном​

​ число — в​​nilem​Вот теперь напишите​4. если цифры​ них только цифры?​ формула выдаст не​ все равно спасибо​ в ячейке должен​ где это возможно.​
​Пример 3. Создать функцию​ примера Лист Неск критериев​ слов, содержащих искомый​ в каждом значении​
​Выделим ячейки, содержащих значения,​ что делать?​: Можно ли при​ ни духом. Ибо​ конце строки​: Вот не самый​

​ плиз полностью формулу​