Функция ЛЕВСИМВ в EXCEL

Старт:16.12.2024

Срок обучения:

Курс «Способы самореализации как основа счастья личности»

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

2 600 ₽4 000 ₽

Подробнее

ВЛЕВО в 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. Вот некоторые из этих возможностей: удалить лишние пробелы и ненужные символы, изменить регистр текста, подсчитать буквы и слова, добавить тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить по ячейкам, извлекать отдельные слова находить повторяющиеся слова.