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 и так далее.

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

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

Читайте также:  Как в excel сцепить ячейки с датой

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: 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

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 есть очень удобная функция для «вытаскивания» из текста или слова определенного заданного нами количества символов.

Зачастую такие задачи возникают при обработке кодов, артикулов, номеров телефонов и т.д.

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

Для этого:

  1. В ячейке напротив кода введем =ПСТР( и нажмем fx.

  2. В аргументах функции укажем ячейку с исходным текстом , первоначальным кодом.
  3. Зададим Начальную позицию (номер символа, с которого начнет вытаскивать текст функция).
  4. Количество знаков – то самое к-во, которое должно быть «вытащено» из текста или строки. Пробел и символы – также являются знаками.


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

Для этого:

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

Далее в Количество знаков указываем любое число , превышающее с запасом возможное количество символов второй части кода. Можно указать «100» .

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

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

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее “перевести”)

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