ЛЕВАЯ функция в 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. Аргумент «количество знаков» меньше нуля
Если ваша формула возвращает ошибку #ЗНАЧ!, первое, что нужно проверить, — это значение аргумента число_символов. Если вы видите отрицательное число, просто уберите минус и ошибка исчезнет (конечно, маловероятно, что кто-то намеренно поставил отрицательное число, но человек может ошибиться