Функция Excel ВЛЕВО с примерами формул

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

Среди многих различных функций, которые Microsoft Excel предоставляет для манипулирования текстовыми данными, ЛЕВАЯ является одной из наиболее широко используемых. Как следует из названия, эта функция позволяет извлекать определенное количество символов, начиная с левой стороны текстовой строки. Однако Excel LEFT способен на гораздо большее, чем его чистая сущность. В этом уроке вы найдете пару основных левых формул для понимания синтаксиса, а затем я покажу вам несколько способов, с помощью которых вы можете использовать функцию ЛЕВАЯ в Excel далеко за пределами ее основного использования.

ЛЕВАЯ функция Excel — синтаксис

Функция ВЛЕВО в Excel возвращает указанное количество символов (подстроки) от начала строки.

Синтаксис функции ЛЕВЫЙ следующий:

СЛЕВА(текст, [num_chars])

Где:

  • Текст (обязательно) — это текстовая строка, из которой вы хотите извлечь подстроку. Обычно он предоставляется как ссылка на ячейку, содержащую текст.
  • Num_chars (необязательно) — количество символов для извлечения, начиная с левой стороны строки.
    • Если num_chars опущен, по умолчанию он равен 1, что означает, что формула Left вернет 1 символ.
    • Если num_chars превышает общую длину текстформула Left вернет все текст.

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

=ВЛЕВО(A2, 3)

На следующем снимке экрана показан результат:
Пример использования функции ЛЕВЫЙ в Excel

Важная заметка! LEFT относится к категории текстовых функций, поэтому результатом формулы Left всегда является текстовая строка, даже если исходное значение, из которого вы извлекаете символы, является числом. Если вы работаете с числовым набором данных и хотите, чтобы функция LEFT возвращала число, используйте ее в сочетании с функцией VALUE, как показано в этот пример.

Как использовать функцию ЛЕВЫЙ в Excel – примеры формул

Помимо извлечения текста слева от строки, что еще может делать функция LEFT? В следующих примерах показано, как можно использовать ЛЕВЫЙ в сочетании с другими функциями Excel для решения более сложных задач.

Как извлечь подстроку перед определенным символом

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

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

Предположим, что полное имя находится в ячейке A2, позиция пробела возвращается по этой простой формуле: ПОИСК(” “,A2)). А теперь вы вставляете эту формулу в num_chars аргумент ЛЕВОЙ функции:

=ВЛЕВО(A2, ПОИСК(” “, A2))

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

=ВЛЕВО(A2, ПОИСК(” “, A2)-1)
Левая формула для извлечения имени

Таким же образом вы можете извлечь код страны из столбца телефонных номеров. Единственное отличие состоит в том, что вы используете функцию поиска, чтобы узнать положение первого дефиса (“-“), а не пробела:

=ВЛЕВО(A2, ПОИСК(“-“, A2)-1)
Левая формула для извлечения кода страны

Подводя итог, вы можете использовать эту общую формулу, чтобы получить подстроку, которая предшествует любому другому символу:

ОСТАВИЛ(нитьПОИСК(персонаж, нить) – 1)

Как удалить последние N символов из строки

Вы уже знаете, как использовать функцию Excel ВЛЕВО, чтобы получить подстроку из начала текстовой строки. Но иногда вы можете захотеть сделать что-то другое — удалить определенное количество символов с конца строки и перетащить оставшуюся часть строки в другую ячейку. Для этого используйте функцию LEFT в сочетании с LEN, например:

ОСТАВИЛ(нить, ДЛСТР(нить) – number_of_chars_to_remove)

Формула работает с такой логикой: функция ДЛСТР получает общее количество символов в строке, затем вы вычитаете количество нежелательных символов из общей длины, а функция ЛЕВЫЙ возвращает оставшиеся символы.

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

=ВЛЕВО(A2, ДЛИН(A2)-7)

Как показано на скриншоте ниже, формула успешно отсекает ” – Сделать” постфикс (4 буквы, дефис и 2 пробела) из текстовых строк в столбце A.
Формула Left для удаления последних семи символов

Как заставить функцию LEFT возвращать число

Как вы уже знаете, функция Excel ВЛЕВО всегда возвращает текст, даже если вы извлекаете из числа несколько первых цифр. Для вас это означает, что вы не сможете использовать результаты формул Left в вычислениях или в других функциях Excel, которые работают с числами.

Итак, как заставить Excel LEFT выводить число, а не текстовую строку? Просто поместив его в функцию ЗНАЧЕНИЕ, предназначенную для преобразования строки, представляющей число, в число, например: ЗНАЧЕНИЕ(ЛЕВО())

Например, чтобы извлечь первые 2 символа из строки в A2 и преобразовать вывод в числа, используйте эту формулу:

= ЗНАЧЕНИЕ (ЛЕВО (A2,2))

Результат будет выглядеть примерно так:
Используйте LEFT в сочетании с функцией VALUE, чтобы вернуть число, а не текст.

Как вы можете видеть на снимке экрана выше, числа в столбце B, полученные с помощью формулы Value Left, выделены в ячейках по правому краю, в отличие от текста, выровненного по левому краю в столбце A. Поскольку Excel распознает выходные данные как числа, вы можете свободно суммируйте и усредните эти значения, найдите минимальное и максимальное значение и выполните любые другие вычисления.

Это лишь некоторые из многих возможных применений LEFT в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить Образец рабочего листа Excel с функцией LEFT.

Дополнительные примеры формул Left можно найти на следующих ресурсах:

Функция Excel ЛЕВАЯ не работает – причины и решения

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

1. Аргумент Num_chars меньше нуля

Если ваша формула Excel Left возвращает значение #ЗНАЧ! ошибка, первое, что вы должны проверить, это значение в num_chars аргумент. Если это отрицательное число, просто уберите минус, и ошибка исчезнет (конечно, очень маловероятно, что кто-то намеренно поставит туда отрицательное число, но ошибаться свойственно человеку 🙂

Чаще всего ошибка VALUE возникает, когда num_chars аргумент представлен другой функцией. В этом случае скопируйте эту функцию в другую ячейку или выберите ее в строке формул и нажмите F9, чтобы увидеть, чему она соответствует. Если значение меньше 0, то проверьте функцию на наличие ошибок.

Чтобы лучше проиллюстрировать это, давайте возьмем формулу Левого, которую мы использовали в первый пример чтобы извлечь телефонные коды страны: ВЛЕВО(A2, ПОИСК(“-“, A2)-1). Как вы помните, функция поиска в num_chars аргумент вычисляет позицию первого дефиса в исходной строке, из которой мы вычитаем 1, чтобы удалить дефис из окончательного результата. Если я случайно заменю -1, скажем, на -11, формула выдаст ошибку #ЗНАЧ, потому что num_chars аргумент равен отрицательным числам:
Формула Left не работает, потому что аргумент num_chars меньше нуля.

2. Начальные пробелы в исходном тексте

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

Чтобы избавиться от начальных пробелов на ваших листах, используйте функцию Excel TRIM или надстройку Text Toolkit.

3. Excel ЛЕВЫЙ не работает с датами

Если вы попытаетесь использовать функцию Excel LEFT для получения отдельной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Дело в том, что в Microsoft Excel все даты хранятся как целые числа, представляющие количество дней с 1 января 1900 года, которое хранится как число 1 (дополнительную информацию см. в разделе Формат даты Excel). То, что вы видите в ячейке, — это просто визуальное представление даты, и его отображение можно легко изменить, применив другой формат даты.

Например, если у вас есть дата 11 января 2017 года в ячейке A1, и вы пытаетесь извлечь день с помощью формулы ЛЕВЫЙ (A1,2), результатом будет 42, то есть первые 2 цифры числа 42746, которое представляет 11 января 2017 года во внутренней системе Excel.

Чтобы извлечь определенную часть даты, используйте одну из следующих функций: ДЕНЬ, МЕСЯЦ или ГОД.

В случае, если ваши даты введены в виде текстовых строк, ЛЕВАЯ функция будет работать без заминок, как показано в правой части скриншота:
Excel LEFT не работает с датами, но работает с текстовыми строками, представляющими даты.

Вот как вы используете функцию ВЛЕВО в Excel. Я благодарю вас за чтение и надеюсь увидеть вас снова на следующей неделе.

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *