ЛЕВАЯ функция в Excel. Примеры использования и советы.
В этом руководстве показано, как использовать функцию LEFT в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое.
Среди множества различных функций, которые Microsoft Excel предлагает для работы с текстовыми данными, ЛЕВАЯ является одной из наиболее часто используемых. Как следует из названия, он позволяет извлекать определенное количество символов из левой части текста. Однако он способен на гораздо большее, чем такая простая операция. В этом руководстве вы найдете несколько основных формул для понимания синтаксиса, а затем я покажу вам несколько способов использования LEFT, выходящих далеко за рамки его основного использования.
- Синтаксические правила.
- Как получить подстроку слева от определенного символа?
- Удалить последние N символов.
- Как заставить ЛЕВЫЙ возвращать число, а не текст.
- Потому что не работает?
Cинтаксис.
Функция ВЛЕВО в Excel возвращает указанное количество символов (подстроку) от начала содержимого ячейки.
Синтаксис следующий:
ВЛЕВО(текст, [количество_символов])
Где:
- Текст (обязательно) — это текст, из которого вы хотите извлечь подстроку. Обычно предоставляется как ссылка на ячейку, в которой она написана.
- Второй (необязательный) аргумент — это количество символов для извлечения, начиная слева.
- Если параметр опущен, по умолчанию принимается 1, то есть возвращается 1 символ.
- Если введенное значение больше общей длины ячейки, формула вернет все ее содержимое.
Например, чтобы извлечь первые 6 символов из A2, введите следующее выражение:
=ВЛЕВО(A2, 6)
На следующем снимке экрана показан результат:
Важная заметка! LEFT классифицируется как текстовая функция, поэтому ее результатом всегда является текстовая строка, даже если исходное значение, из которого вы извлекаете цифры, является числом. Если вы работаете с числовым набором данных и хотите получить точное число, используйте его в сочетании с функцией ЗНАЧ, как показано в одном из приведенных ниже примеров.
Что еще вы можете сделать, кроме извлечения текста слева? В следующих примерах показано, как его можно использовать в сочетании с другими функциями Excel для решения более сложных задач.
Как извлечь подстроку перед определенным символом.
В некоторых случаях может потребоваться извлечь фрагмент текста, предшествующий определенному символу. Например, вы можете извлечь имена из столбца полных имен или получить коды стран из столбца телефонных номеров. Проблема в том, что каждое имя и каждый код содержат разное количество символов, и поэтому нельзя просто указать точное количество, сколько символов отделять, как мы это сделали в примере выше.
Если имя и фамилия разделены пробелом, проблема состоит в том, чтобы определить положение этого разделителя. Это можно легко сделать с помощью функции ПОИСК или НАЙТИ .
Предполагая, что полное имя находится в ячейке A2, позиция в пробеле возвращается по этой простой формуле: ПРОСМОТР(» «; A2)). А теперь вы вставляете это выражение СЛЕВА:
=ВЛЕВО(A2, ПОИСК(» «, A2))
Чтобы еще больше улучшить результат, удалите завершающий пробел, вычтя 1 из результата поиска. Ведь невидимые конечные пробелы могут доставить немало хлопот, особенно если вы планируете использовать извлеченные имена в других вычислениях:
=ВЛЕВО(A2, ПОИСК(» «, A2)-1)
Точно так же вы можете извлечь коды стран из столбца телефонных номеров. Разница лишь в том, что теперь вам нужно знать позицию первого дефиса («-«) вместо пробела:
=ВЛЕВО(A2, ПОИСК(«-«, A2)-1)
Я думаю, вы понимаете, что вы можете использовать эту универсальную формулу, чтобы получить подстроку, которая предшествует любому другому символу:
ВЛЕВО(строка; ПОИСК(символ; строка) — 1)
Как удалить последние N символов.
Вы уже знаете, как вырезать кусок из начала строки текста. Но иногда вы можете захотеть сделать что-то другое: удалить определенное количество символов с конца строки и переместить остальные в другую ячейку. Для этого вы также можете использовать функцию ВЛЕВО в сочетании с ВЛЕВО, например:
ВЛЕВО(текст; ВЛЕВО(текст) — количество_символов_для_удаления)
Это работает по следующей логике: ЛЕВЫЙ получает общее количество символов в ячейке, затем вычитает количество ненужных символов из общей длины, и ЛЕВЫЙ возвращает остаток.
Например, чтобы удалить последние 7 символов текста в формате A2, введите следующее выражение:
=ВЛЕВО(A2, ВЛЕВО(A2)-11)
Как показано на скриншоте, формула успешно удаляет слово «товары» (8 букв, разделитель и 2 пробела) из текстовых значений в столбце А.
При этом перенять метод, который мы рассмотрели в предыдущем примере, будет очень сложно, так как все разделители разные.
Как заставить ЛЕВСИМВ возвращать число.
Как вы знаете, функция LEFT в Excel всегда возвращает текст, даже если вы извлекаете первые несколько цифр ячейки. Для вас это означает, что вы не сможете использовать эти результаты в вычислениях или других функциях Excel, работающих с числами.
Так как же заставить LEFT отображать числовое значение вместо текстовой строки, состоящей из чисел? Просто поместив его в функцию VALUE, предназначенную для преобразования числового текста в число.
Например, чтобы извлечь символы перед разделителем «-» из A2 и преобразовать результат в число, вы можете сделать следующее:
=ЗНАЧ(ЛЕВАЯ(A2,ПРОСМОТР(«-«,A2,1)-1))
Результат будет примерно таким:
Как вы можете видеть на снимке экрана выше, числа в столбце B, полученные в результате преобразования, автоматически выравниваются по правому краю в ячейках, в отличие от текста, выровненного по левому краю в столбце A. Поскольку Excel распознает итоги как числа, вы можете складывать и усреднять эти значения, найдите минимальное и максимальное значение и выполните любые другие вычисления с ними.
Это лишь некоторые из многих возможных применений LEFT в Excel.
Дополнительные примеры ЛЕВЫХ формул см в следующих ресурсах:
- Разделить строку запятой, двоеточием, косой чертой, дефисом или другим разделителем
- Как разбить текстовую строку типа «число + текст» на столбцы
- Подсчитайте количество символов до или после заданного символа
Не работает ЛЕВСИМВ — причины и решения
Если LEFT не работает правильно на ваших листах, это, скорее всего, связано с одной из причин, перечисленных ниже.
1. Аргумент «количество знаков» меньше нуля
Если ваша формула возвращает ошибку #ЗНАЧ!, первое, что нужно проверить, — это значение аргумента число_символов. Если вы видите отрицательное число, просто уберите минус и ошибка исчезнет (конечно, маловероятно, что кто-то намеренно поставил отрицательное число, но человек может ошибиться 🙂
Самая распространенная ошибка #ЗНАЧ! происходит, когда этот аргумент является результатом вычисления и не вводится вручную. В этом случае скопируйте этот расчет в другую ячейку или выберите его в строке формул и нажмите F9, чтобы увидеть результат своей работы. Если значение меньше 0, проверьте наличие ошибок.
Чтобы лучше проиллюстрировать эту идею, давайте возьмем формулу, которую мы написали в первом примере, для извлечения телефонных кодов страны:
ВЛЕВО(A2, ПОИСК(«-«, A2)-1)
Помните, что функция ПРОСМОТР в наших примерах вычисляет позицию первого дефиса в исходной строке, из которой мы затем вычитаем 1, чтобы удалить дефис из окончательного результата. Если я случайно заменю -1, скажем, -11, Excel выдаст ошибку #ЗНАЧ! потому что он не может извлечь отрицательное количество букв и цифр:
2. Начальные пробелы в исходном тексте
Если вы скопировали свои данные из Интернета или экспортировали их из другого внешнего источника, довольно часто можно обнаружить такие неприятные сюрпризы в начале текста. И вы едва замечаете их присутствие, пока что-то не пойдет не так. Следующее изображение иллюстрирует проблему:
Чтобы избавиться от начальных пробелов в листах, используйте TRIM).
3. ЛЕВСИМВ не работает с датами.
Если вы попытаетесь использовать LEFT для получения одной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Это связано с тем, что Microsoft Excel хранит все даты в виде чисел, представляющих количество дней с 1 января 1900 года. То, что вы видите в ячейке, — это просто визуальное представление даты. Его отображение можно легко изменить, применив другой формат.
Например, если у вас есть дата 15 июля 2020 года в ячейке A1, и вы пытаетесь извлечь день, используя выражение LEFT(A1, 2). Результатом будет 44, то есть первые 2 цифры числа 44027, которое соответствует 15 июля 2020 года во внутренней системе Excel.
Чтобы извлечь определенную часть даты, используйте одну из следующих функций: ДЕНЬ(), МЕСЯЦ() или ГОД().
Если ваши даты вводятся в виде текстовых строк, LEFT будет работать нормально, как показано в правой части снимка экрана:
Вот как вы можете использовать функцию ВЛЕВО в Excel.
Все описанные выше операции, а также многие другие действия с текстовыми значениями в Excel можно выполнить с помощью специального текстового инструмента, входящего в надстройку Ultimate Suite. Вот лишь некоторые из этих функций: удаление лишних пробелов и нежелательных символов, изменение форматирования текста, подсчет букв и слов, добавление одного и того же текста в начало или конец всех ячеек в диапазоне, преобразование текста в числа, разделение на ячейки, извлечение отдельные слова, найти повторяющиеся слова.