Как вытащить часть текста из ячейки excel с разным количеством знаков

Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов

ПСТР — одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста.

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

Cинтаксис.

Функция ПСТР возвращает указанное количество знаков, начиная с указанной вами позиции.

Функция Excel ПСТР имеет следующие аргументы:

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

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

Все 3 аргумента обязательны.

Например, чтобы извлечь 6 знаков из A2, начиная с 17-го, используйте эту формулу:

5 вещей, которые вы должны знать о функции Excel ПСТР

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

1. Функция ПСТР всегда возвращает текстовую строку, даже если извлеченная подстрока содержит только цифры. Это может иметь большое значение, если вы хотите использовать результат формулы ПСТР в других вычислениях. Чтобы преобразовать цифры в число, применяйте ПСТР в сочетании с функцией ЗНАЧЕН (VALUE в английской версии), как показано в этом примере. (ссылка на последний раздел).
2. Когда начальная позиция больше, чем общая длина исходного текста, формула Excel ПСТР возвращает пустое значение («»).
3. Если начальная позиция меньше 1, формула ПСТР возвращает ошибку #ЗНАЧ!.
4. Когда третий аргумент меньше 0 (отрицательное число), формула ПСТР возвращает ошибку #ЗНАЧ!. Если количество знаков для извлечения равно 0, выводится пустая строка (пустая ячейка).
5. В случае, если сумма начальной позиции и количества знаков превышает общую длину исходного текста, функция ПСТР в Excel возвращает подстроку начиная с начальной позиции и до последнего символа.

При решении реальных задач в Excel вам чаще всего потребуется использовать ПСТР в сочетании с другими функциями, как показано в следующих примерах.

Как извлечь имя и фамилию.

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

Получаем имя.

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

ПОИСК используется для сканирования исходного значения на предмет пробела (» «) и возврата его позиции, из которой вы вычитаете 1, чтобы избежать пробелов после имени. Затем вы используете ПСТР, чтобы вернуть подстроку, начинающуюся с первого знака и заканчивая предшествующим пробелу, таким образом извлекая первое имя.

Получаем фамилию.

Чтобы извлечь фамилию из A2, используйте эту формулу:

Опять же, вы используете ПОИСК, чтобы определить начальную позицию (пробел). Нам не нужно точно рассчитывать конечную позицию (как вы помните, если вместе взятые начальная позиция и количество символов больше, чем общая длина текста, возвращаются просто все оставшиеся). Итак, в аргументе количество символов вы просто указываете общую первоначальную длину , возвращаемую функцией ДЛСТР . Впрочем, вместо этого вы можете просто ввести число, представляющее самую длинную фамилию, которую вы ожидаете найти, например 100. Наконец, СЖПРОБЕЛЫ удаляет лишние интервалы, и вы получаете следующий результат:

Как выделить подстроку между двумя разделителями.

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

Чтобы узнать количество извлекаемых знаков, вычтите позицию первого пробела из положения второго. И затем еще вычтите 1 из результата, поскольку вам не нужны лишние интервалы в получившемся результате. Таким образом, у вас есть второй аргумент для формулы:

ПОИСК(» «; A2; ПОИСК(» «; A2) +1) — ПОИСК(» «; A2)

Соединив все аргументы, мы получаем формулу для извлечения подстроки между двумя пробелами:

На следующем скриншоте показан результат:

Аналогичным образом вы можете извлечь текст между любыми другими разделителями:

ПСТР( строка ; ПОИСК( разделитель ; строка ) +1; ПОИСК( разделитель ; строка ; ПОИСК( разделитель ; строка ) +1) — ПОИСК( разделитель ; строка ) -1)

Например, чтобы извлечь отрезок, выделенный запятой с пробелом после неё, используйте следующее выражение:

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

Как получить N-е слово из текста.

Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей:

• ДЛСТР — чтобы получить общую длину.
• ПОВТОР — повторение определенного знака заданное количество раз.
• ПОДСТАВИТЬ — заменить один символ другим.
• ПСТР — извлечь подстроку.
• СЖПРОБЕЛЫ — удалить лишние интервалы между словами.

Общая формула выглядит следующим образом:

СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ( строка ; » «; ПОВТОР (» «; ДЛСТР( строка ))); ( N -1) * ДЛСТР( строка ) +1; ДЛСТР( строка )))

• Строка — это исходный текст, из которого вы хотите извлечь желаемое слово.
• N – порядковый номер слова, которое нужно получить.

Например, чтобы вытащить второе слово из A2, используйте это выражение:

Или вы можете ввести порядковый номер слова, которое нужно извлечь (N) в какую-либо ячейку, и указать эту ячейку в формуле, как показано на скриншоте ниже:

Как работает эта формула?

По сути, Excel «оборачивает» каждое слово исходного текста множеством пробелов, находит нужный блок «пробелы-слово-пробелы», извлекает его, а затем удаляет лишние интервалы. Чтобы быть более конкретным, это работает по следующей логике:

• ПОДСТАВИТЬ и ПОВТОР заменяют каждый пробел в тексте несколькими. Количество этих дополнительных вставок равно общей длине исходной строки: ПОДСТАВИТЬ($A$2;» «;ПОВТОР(» «;ДЛСТР($A$2)))

Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР.

• Затем вы определяете начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * ДЛСТР(A1) +1. Это вычисление возвращает либо позицию первого знака первого слова, либо, чаще, позицию в N-й группе пробелов.
• Количество букв и цифр для извлечения (второй аргумент) — самая простая часть — вы просто берете общую первоначальную длину: ДЛСТР(A2).
• Наконец, СЖПРОБЕЛЫ избавляется от начальных и конечных интервалов в извлечённом тексте.

Приведенная выше формула отлично работает в большинстве ситуаций. Однако, если между словами окажется 2 или более пробелов подряд, это даст неверные результаты (1). Чтобы исправить это, вложите еще одну функцию СЖПРОБЕЛЫ в ПОДСТАВИТЬ, чтобы удалить лишние пропуски между словами, оставив только один, например:

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

Следующий рисунок демонстрирует улучшенный вариант (2) в действии:

Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки:

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

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

Извлекаем слово, содержащее определенный символ.

В этом примере показана еще одна нестандартная формула Excel ПСТР, которая извлекает слово, содержащее определенную букву или цифру, из любого места:

СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ ( строка , » «, ПОВТОР(» «, 99)), МАКС(1, НАЙТИ( символ , ПОДСТАВИТЬ( строка , » «, ПОВТОР(» «, 99))) — 50), 99))

Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму:

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;» «;ПОВТОР(» «;99)); МАКС(1;НАЙТИ(«$»;ПОДСТАВИТЬ(A2;» «;ПОВТОР(» «;99)))-50);99))

Аналогичным образом вы можете извлекать адреса электронной почты (на основе знака «@»), имена веб-сайтов (на основе «www») и так далее.

Теперь разберём пошагово:

Как и в предыдущем примере, ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходном тексте в несколько, точнее, в 99.

НАЙТИ находит позицию нужного символа (в данном примере $), из которой вы вычитаете 50. Это возвращает вас на 50 позиций назад и помещает где-то в середине блока из 99 пробелов, который предшествует слову, содержащему указанный символ.

МАКС используется для обработки ситуации, когда нужное значение появляется в начале исходного текста. В этом случае результат ПОИСК() — 50 будет отрицательным числом, а МАКС(1, ПОИСК() — 50) заменяет его на 1.

С этой начальной точки ПСТР отбирает следующие 99 знаков и возвращает интересующее нас слово, окруженное множеством пробелов. Как обычно, СЖПРОБЕЛЫ помогает избавиться от лишних из них, оставив только один.

Совет. Если извлекаемый отрезок очень большой, замените 99 и 50 на более крупные числа, например 1000 и 500.

Как заставить ПСТР возвращать число?

Как и другие текстовые функции, Excel ПСТР всегда возвращает текст, даже если он содержит только цифры и очень похож на число. Вы можете убедиться с этом, взглянув на пример чуть выше, когда мы получили число «20%» как текст.

Чтобы преобразовать результат в число, просто передайте полученный результат в функцию ЗНАЧЕН (VALUE в английской версии), которая преобразует текстовое значение, состоящее из цифр, в число.

Например, чтобы извлечь подстроку из 3 символов, начинающуюся с 7- го символа, и преобразовать ее в число, используйте:

На скриншоте ниже показан результат. Обратите внимание, что числа с выравниванием по правому краю помещены в столбец B, в отличие от исходных текстовых значений с выравниванием по левому краю в столбце A:

Тот же подход работает и для более сложных случаев. В приведенном выше примере, предполагая, что коды ошибок имеют переменную длину, вы можете извлечь их с помощью ПСТР, которая получает подстроку между двумя разделителями, вложенную в ЗНАЧЕН:

Вот как можно использовать функцию ПСТР в Excel.

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

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

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

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

Вот пример: из наименования товара — Коммутатор Optimus U1E-8F/1G/1S, нужно извлечь номер модели. Поскольку этот номер находится в конце наименования, то будем искать то, что записано после последнего пробела.

В результате получим U1E-8F/1G/1S, что и требовалось.

Как извлечь текст из ячейки с помощью Ultimate Suite

Как вы только что видели, Microsoft Excel предоставляет набор различных функций для работы с текстовыми строками. Если вам нужно извлечь какое-то слово или часть текста из ячейки, но вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте работу Ultimate Suite for Excel. Заодно не придётся возиться с формулами.

Вы просто переходите на вкладку Ablebits Data > Текст, выбираете инструмент Split Text и в выпадающем списке нажимаете Извлечь (Extract) :

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

1. Укажите, сколько символов вы хотите получить из начала, конца или середины строки; или выберите извлечение всего текста до или после определенного символа.
2. Щелкните Вставить результаты(InsertResults). Готово!

Кроме того, вы можете извлечь любое число символов с начала или в конце текста, из середины текста, между какими-то символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, вы выбираете чекбокс Все после текста (All after text) и вводите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все до текста (All before text), как показано на рисунке ниже.

Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность — он поможет вам изучить формулы Excel в целом и функции подстроки в частности. Как? Выбрав флажок Вставить как формула (Insert as formula) в нижней части панели, вы убедитесь, что результаты выводятся в виде формул, а не просто как значения. Естественно, эти формулы вы можете использовать в других таблицах.

В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:

• Чтобы извлечь имя пользователя:

• Чтобы извлечь домен:

Сколько времени вам потребуется, чтобы самостоятельно составить эти выражения? 😉

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

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

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

СЦЕПИТЬ в Excel: как объединять текстовые строки, ячейки и столбцы — В этом руководстве вы узнаете о различных способах объединения текстовых значений, ячеек, диапазонов, столбцов и строк в Excel с помощью функций СЦЕПИТЬ, СЦЕП и оператора «&» . В ваших файлах Excel…
Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в…
Как быстро извлечь число из текста в Excel — В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно…
Как удалить пробелы в ячейках Excel — Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с…
Функция СЖПРОБЕЛЫ — как пользоваться и примеры — Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как…
Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…
5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…
Как быстро сосчитать количество символов в ячейке Excel — В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Как в Excel разделить текст из одной ячейки в несколько — В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…

Как вытащить часть текста из ячейки excel?

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

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

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

2. ДЛСТР – это функция позволяет узнать количество символов строки.

3. НАЙТИ – находит в тексте положение определенного элемента или слова.

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

Пример №1.

Перед нами таблица, в которой указаны ссылка на разные статьи, нам необходимо убрать из каждой ссылки буквы: .html

Чтобы решить данную задачу, мы будем использовать две функции ДЛСТР и ЛЕВСИМВ. С помощью функции ДЛСТР будем определять длину строки, так как строки имеют у нас разную длину. А функцией ЛЕВСИМВ, будем оставлять нужное количество символов. Также заметим, что строчка .html состоит из 5 символов, т.е. в независимости какая длина строки, всегда нужно убирать пять элементов. И так в ячейке «С2» напишем формулу: =ЛЕВСИМВ(B2;ДЛСТР(B2)-5). После её скопируем в оставшиеся ячейки.

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

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

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

Как в Эксель (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)),»Число не введено»)’ >

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