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