Как извлечь из ячейки первые 5 символов в excel
В этой статье описаны синтаксис и использование функций ЛЕВСИМВ и ЛЕВБ в Microsoft Excel.
Описание
Функция ЛЕВСИМВ возвращает первый символ или несколько первых символов текстовой строки на основе заданного числа символов.
Функция ЛЕВБ возвращает первый символ или несколько первых символов текстовой строки на основе заданного числа байтов.
Эти функции могут быть доступны не на всех языках.
Функция ЛЕВБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой двухбайтовой кодировки. В противном случае функция ЛЕВБ работает так же, как функция ЛЕВСИМВ, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы этих функций описаны ниже.
Текст Обязательный. Текстовая строка, содержащая символы, которые требуется извлечь.
Количество_знаков Необязательный. Количество символов, извлекаемых функцией ЛЕВСИМВ.
«Количество_знаков» должно быть больше нуля или равно ему.
Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ возвращает весь текст.
Если значение «количество_знаков» опущено, оно считается равным 1.
Количество_байт Необязательный. Количество символов, извлекаемых функцией ЛЕВБ.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.
Примеры использования функции ЛЕВСИМВ в 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)),»Число не введено»)’ > 
- Первая функция ЕСЛИ проверяет с помощью формулы ЕПУСТО, заполнена ли ячейка, которая должна содержать значение, которое требуется округлить.
- Если число введено, запись ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(«,»;B2)+1)) возвращает числовое значение первого символа после запятой и сравнивает полученное значение с числом 5.
- Если полученное значение больше 5, будет возвращен результат выполнения функции ЛЕВСИМВ(B2+1;НАЙТИ(«,»;B2)-1), то есть целой части числа +1 по правилам округления.
- Если <5, то будет возвращен результат работы функции ЛЕВСИМВ(B2;НАЙТИ(«,»;B2)-1)), то есть просто целая часть исходного числа.
Примечание: данный алгоритм нельзя назвать гибким и он имеет некоторые недостатки (например, не учтен вариант ввода сразу целого числа). Он приведен в качестве примера использования функции ЛЕВСИМВ. Для расчетов лучше использовать встроенную функцию ОКРГУГЛ и подобные ей.
Функция ЛЕВСИМВ в Excel и особенности ее использования
Функция ЛЕВСИМВ используется наряду с прочими функциями для работы с текстовыми строками в Excel (ПРАВСИМВ, СИМВОЛ, СЦЕПИТЬ, ДЛСТР и прочими) и имеет следующую синтаксическую запись:
- текст – ссылка на ячейку, содержащую текст, или текстовая строка, в которой требуется выделить требуемое количество символов слева. Аргумент обязателен для заполнения.
- [число_знаков] – необязательный для заполнения аргумент; числовое значение, характеризующее количество символов, которые требуется выделить из текстовой строки.
- Если необязательный аргумента [число_знаков] явно не указан, по умолчанию будет возвращен первый символ строки. Например, функция =ЛЕВСИМВ(«текст») вернет значение «т».
- Числовое значение, переданное в качестве необязательного аргумента, должно быть взято из диапазона неотрицательных чисел, то есть должно быть равным нулю или больше нуля. В противном случае будет возвращен код ошибки #ЗНАЧ!.
- Если в качестве аргумента [число_знаков] будет переданы текстовые данные, функция ЛЕВСИМВ вернет код ошибки #ЗНАЧ!.
- Если число, переданное в качестве необязательного аргумента, превышает количество знаков в текстовой строке, рассматриваемая функция вернет весь текст целиком.
- Excel выполняет автоматическое преобразование типов данных там, где это возможно. Например, рассмотрим некоторые возможные варианты записи функции ЛЕВСИМВ и результаты ее выполнения:
- ЛЕВСИМВ(«текст»;200%) вернет значение «те» (преобразование процентов в числовое значение);
- при аргументах: («текст»;ИСТИНА) вернет «т» (логическое ИСТИНА эквивалентно числовому значению 1);
- при аргументах: («текст»;ЛОЖЬ) вернет пустое значение «», поскольку логическое ЛОЖЬ эквивалентно числовому 0 (нуль);
- при аргументах: («текст»;15/7) вернет значение «те».
Примечание 2: в Excel также существует схожая по принципу работы функция ЛЕВБ, возвращающая определенное количество символов текстовой строки слева на основе определенного количества байтов. Если по умолчанию используется язык с поддержкой двухбайтовой кодировки, для получения первого символа строки требуется в качестве аргумента указать число 2, а для получения последующих второго, третьего и прочих символов необходимо указывать соответствующие числа, кратные двум. Для языков с однобайтовой кодировкой функции ЛЕВСИМВ и ЛЕВБ возвращают идентичные результаты.
Функция ЛЕВСИМВ в Excel. Примеры использования и советы.
В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое.
Среди множества различных функций, которые Microsoft Excel предоставляет для работы с текстовыми данными, ЛЕВСИМВ — одна из наиболее широко применяемых. Как следует из названия, она позволяет извлекать определенное количество знаков, начиная с левой стороны текста. Однако она способна на гораздо большее, чем такая простая операция. В этом руководстве вы найдете несколько базовых формул для понимания синтаксиса, а затем я покажу вам несколько способов, с помощью которых вы можете использовать ЛЕВСИМВ далеко за пределами ее базового применения.
Cинтаксис.
Функция ЛЕВСИМВ в Excel возвращает указанное количество символов (подстроку) от начала содержимого ячейки.
ЛЕВСИМВ (текст; [колич_знаков])
- Текст (обязательно) — это текст, из которого вы хотите извлечь подстроку. Обычно предоставляется как ссылка на ячейку, в которой он записан.
- Второй аргумент (необязательно) — количество знаков для извлечения, начиная слева.
- Если параметр опущен, то по умолчанию подразумевается 1, то есть возвращается 1 знак.
- Если введенное значение больше общей длины ячейки, формула вернет всё ее содержимое.
Например, чтобы извлечь первые 6 символов из A2, запишите такое выражение:
На следующем скриншоте показан результат:
В некоторых случаях может потребоваться извлечь часть текста, который предшествует определенному знаку. Например, вы можете извлечь имена из столбца ФИО или получить коды стран из колонки с телефонными номерами. Проблема в том, что каждое имя и каждый код содержат разное количество символов, и поэтому вы не можете просто указать точное число сколько знаков отделить, как мы сделали в приведенном выше примере.
Если имя и фамилия разделены пробелом, проблема сводится к определению положения этого разделителя. Это можно легко сделать с помощью функции ПОИСК или НАЙТИ .
Предположим, что полное имя находится в ячейке A2, позиция пробела возвращается по этой простой формуле: ПОИСК(» «; A2)). А теперь вы вставляете это выражение в ЛЕВСИМВ:
Чтобы еще немного улучшить результат, избавьтесь от конечного пробела, вычтя 1 из результата поиска. Ведь невидимые конечные пробелы могут вызвать множество проблем, особенно если вы планируете использовать извлеченные имена в других вычислениях:
Таким же образом вы можете извлечь коды стран из столбца телефонных номеров. Единственное отличие состоит в том, что вам теперь нужно узнать позицию первого дефиса («-«), а не пробела:
Думаю, вы поняли, что можете брать эту универсальную формулу, чтобы получить подстроку, которая предшествует любому другому знаку:
Как удалить последние N символов.
Вы уже знаете, отрезать кусочек от начала текстовой строки. Но иногда вы можете захотеть сделать что-то другое — удалить определенное количество символов из конца строки и перенести оставшееся в другую ячейку. Для этого можно также применять функцию ЛЕВСИМВ в сочетании с ДЛСТР , например:
Как показано на скриншоте, формула успешно отсекает слово «продукты» (8 букв, разделитель и 2 пробела) из текстовых значений в столбце A.
При этом взять на вооружение способ, рассмотренный нами в предыдущем примере, будет очень сложно, так как все разделители разные.
Как заставить ЛЕВСИМВ возвращать число.
Как вы уже знаете, ЛЕВСИМВ в Эксель всегда возвращает текст, даже если вы извлекаете несколько первых цифр из ячейки. Для вас это означает, что вы не сможете использовать эти результаты в вычислениях или в других функциях Excel, которые работают с числами.
Итак, как заставить ЛЕВСИМВ выводить числовое значение, а не текстовую строку, состоящую из цифр? Просто заключив его в функцию ЗНАЧЕН (VALUE), которая предназначена для преобразования текста, состоящего из цифр, в число.
Например, чтобы извлечь символы перед разделителем “-” из A2 и преобразовать результат в число, можно сделать так:
Результат будет выглядеть примерно так:
Как вы можете видеть на скриншоте выше, числа в столбце B, полученные с помощью преобразования, автоматически выровнены по правому краю в ячейках, в отличие от текста с выравниванием по левому краю в столбце A. Поскольку Эксель распознает итоговые данные как числа, вы можете суммировать и усреднять эти значения, находить минимальное и максимальное значение и выполнять любые другие вычисления с ними.
Это лишь некоторые из множества возможных вариантов использования ЛЕВСИМВ в Excel.
Дополнительные примеры формул ЛЕВСИМВ можно найти на следующих ресурсах:
Не работает ЛЕВСИМВ — причины и решения
Если ЛЕВСИМВ не работает на ваших листах должным образом, это, скорее всего, связано с одной из причин, которые мы перечислим ниже.
1. Аргумент «количество знаков» меньше нуля
Если ваша формула возвращает ошибку #ЗНАЧ!, то первое, что вам нужно проверить, — это значение аргумента количество_знаков. Если вы видите отрицательное число, просто удалите знак минус, и ошибка исчезнет (конечно, очень маловероятно, что кто-то намеренно поставит отрицательное число, но человек может ошибиться 🙂
Чаще всего ошибка #ЗНАЧ! возникает, когда этот аргумент получен в результате вычислений, а не записан вручную. В этом случае скопируйте это вычисление в другую ячейку или выберите его в строке формул и нажмите F9, чтобы увидеть результат ее работы. Если значение меньше 0, проверьте на наличие ошибок.
Чтобы лучше проиллюстрировать эту мысль, возьмем формулу, которую мы записали в первом примере для извлечения телефонных кодов страны:
ЛЕВСИМВ(A2; ПОИСК(«-«; A2)-1)
Как вы помните, функция ПОИСК в наших примерах вычисляет позицию первого дефиса в исходной строке, из которой мы затем вычитаем 1, чтобы удалить дефис из окончательного результата. Если я случайно заменю -1, скажем, на -11, Эксель выдаст ошибку #ЗНАЧ!, потому что нельзя извлечь отрицательное количество букв и цифр:
2. Начальные пробелы в исходном тексте
Если вы скопировали свои данные из Интернета или экспортировали из другого внешнего источника, довольно часто такие неприятные сюрпризы попадаются в самом начале текста. И вы вряд ли заметите, что они там есть, пока что-то не пойдет не так. Следующее изображение иллюстрирует проблему:
Чтобы избавиться от ведущих пробелов на листах, воспользуйтесь СЖПРОБЕЛЫ (TRIM).
3. ЛЕВСИМВ не работает с датами.
Если вы попытаетесь использовать ЛЕВСИМВ для получения отдельной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Дело в том, что в Microsoft Excel все даты хранятся как числа, представляющие количество дней с 1 января 1900 года. То, что вы видите в ячейке, это просто визуальное представление даты. Ее отображение можно легко изменить, применив другой формат.
Например, если у вас есть дата 15 июля 2020 года в ячейке A1 и вы пытаетесь извлечь день с помощью выражения ЛЕВСИМВ(A1;2). Результатом будет 44, то есть первые 2 цифры числа 44027, которое представляет 15 июля 2020г. во внутренней системе Эксель.
Чтобы извлечь определенную часть даты, возьмите одну из следующих функций: ДЕНЬ(), МЕСЯЦ() или ГОД().
Если же ваши даты вводятся в виде текстовых строк, то ЛЕВСИМВ будет работать без проблем, как показано в правой части скриншота:
Вот как можно использовать функцию ЛЕВСИМВ в Excel.
Все описанные выше операции, а также многие другие действия с текстовыми значениями в Excel вы можете выполнить при помощи специального инструмента работы с текстом, включённого в надстройку Ultimate Suite. Вот только некоторые из этих возможностей: удалить лишние пробелы и ненужные символы, изменить регистр текста, подсчитать буквы и слова, добавить один и тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить по ячейкам, извлечь отдельные слова, найти дубликаты слов.
СЦЕПИТЬ в Excel: как объединять текстовые строки, ячейки и столбцы — В этом руководстве вы узнаете о различных способах объединения текстовых значений, ячеек, диапазонов, столбцов и строк в Excel с помощью функций СЦЕПИТЬ, СЦЕП и оператора «&» . В ваших файлах Excel…
Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в…
Как быстро извлечь число из текста в Excel — В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно…
Как удалить пробелы в ячейках Excel — Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с…
Функция СЖПРОБЕЛЫ — как пользоваться и примеры — Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как…
Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…
Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов — ПСТР — одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим…
5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…
Как быстро сосчитать количество символов в ячейке Excel — В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Как в Excel разделить текст из одной ячейки в несколько — В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…Функция ПСТР (или функция ИЗВЛЕЧЬ) в Excel
Функция ПСТР (или функция ИЗВЛЕЧЬ) в Excel является одной из наиболее полезных функций для работы с текстовыми данными. Она позволяет извлекать определенное количество символов из текстовой строки, начиная с указанного места. В этой статье мы рассмотрим, как использовать функцию ПСТР в Excel и приведем несколько примеров ее использования.
Синтаксис функции ПСТР
Синтаксис функции ПСТР выглядит следующим образом:
=ПСТР(текст; начало; количество_символов)
В этом синтаксисе:
- Текст — текстовая строка, из которой нужно извлечь символы.
- Начало — номер символа, с которого нужно начать извлечение (начиная с 1).
- Количество_символов — количество символов, которые нужно извлечь из текстовой строки.
Пример 1: Извлечение подстроки из текстовой строки
Для извлечения подстроки из текстовой строки в Excel можно использовать функцию ПСТР. Рассмотрим простой пример, в котором мы извлечем из строки «Добро пожаловать» первые 5 символов. Для этого выполним следующие действия:
- Введите текст «Добро пожаловать» в ячейку A1, как показано на рисунке ниже:
- Введите следующую формулу в ячейку B1: =ПСТР(A1;1;5)
- Нажмите клавишу Enter.
Результатом выполнения этой формулы будет подстрока «Добро».
Пример 2: Использование функции ПСТР в формуле
Функция ПСТР может быть использована в более сложных формулах для обработки текстовых данных. Рассмотрим следующий пример, в котором мы используем функцию ПСТР для извлечения части текстовой строки, содержащей числовое значение.
- Введите текст в ячейку A1, как показано на рисунке ниже:
- Введите следующую формулу в ячейку B1: =ЗНАЧЕНИЕ(ПСТР(A1;НАЙТИ(» «;A1)+1;ДЛИНА(A1)-НАЙТИ(» «;A1)))
- Нажмите клавишу Enter.
Результатом выполнения этой формулы будет число, которое представляет собой значение, содержащееся в строке A1 после первого пробела.
Пример 3: Использование функции ПСТР для извлечения подстрок из таблицы
Функция ПСТР может быть использован в таблицах Excel для извлечения подстрок из столбцов с данными. Рассмотрим следующий пример, в котором мы используем функцию ПСТР для извлечения имен из столбца «ФИО».
- Введите данные в столбец «ФИО», как показано на рисунке ниже:
- Введите следующую формулу в ячейку B2: =ПСТР(A2;НАЙТИ(» «;A2)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2)+1)-НАЙТИ(» «;A2)-1)
- Нажмите клавишу Enter.
- Выделите ячейку B2 и перетащите ее вниз по столбцу, чтобы скопировать формулу во все оставшиеся ячейки.
Результатом выполнения этой формулы будет список имен из столбца «ФИО», извлеченных из исходной строки.
Функция ПСТР в Excel является мощным инструментом для работы с текстовыми данными. Она позволяет извлекать определенное количество символов из текстовой строки, начиная с указанного места. Функция ПСТР может быть использована для простых задач, таких как извлечение подстроки из текстовой строки, а также для более сложных операций, таких как обработка данных в таблицах. Знание этой функции может значительно упростить обработку и анализ текстовых данных в Excel.