Функция LEVSIMW в Excel. Примеры применения и советы.
В этом уроке показано, как использовать функцию ЛЕВСИМВ в Excel для получения подстроки из начала текстовой строки, извлечения текста перед определенным символом, возвращения формуле числа и многого другого.
Среди множества различных функций, которые Microsoft Excel предоставляет для работы с текстовыми данными, LEFT является одной из наиболее часто используемых. Как следует из названия, она позволяет извлечь определенное количество символов, начиная с левой части текста. Однако он способен на гораздо большее, чем это простое действие. В этом учебнике вы найдете несколько основных формул для понимания синтаксиса, а затем я покажу вам несколько способов использования LEVSIMW далеко за пределами его самых простых приложений.
- Правила синтаксиса.
- Как получить подстроку слева от определенного символа?
- Удалите последние N символов.
- Как заставить LEVSIMW возвращать число вместо текста.
- Почему это не работает.
Cинтаксис.
Функция ЛЕВСИМВ в Excel возвращает заданное количество символов (подстроку) от начала содержимого ячейки.
Синтаксис следующий:
LEVSIMW (текст; [количество_символов])
Где:
- Текст (обязательно) - это текст, из которого мы хотим извлечь подстроку. Обычно дается как ссылка на ячейку, в которой он хранится.
- Второй аргумент (необязательный) - это количество символов для извлечения, начиная слева.
- Если параметр опущен, по умолчанию подразумевается 1, т.е. возвращается 1 символ.
- Если введенное значение больше, чем общая длина ячейки, формула вернет все содержимое ячейки.
Например, чтобы извлечь первые 6 символов A2, запишите следующее выражение:
= LEFTSIMV(A2; 6)
На скриншоте ниже показан результат:
Важное замечание: LEVSIMV относится к категории текстовых функций, поэтому результатом всегда будет текстовая строка, даже если исходное значение, из которого вы извлекаете цифры, является числом. Если вы работаете с числовым набором данных и хотите извлечь именно число, используйте его в сочетании с функцией SIGN, как показано в одном из примеров ниже.
Что еще можно сделать, кроме извлечения текста из левой части? Следующие примеры показывают, как его можно использовать в сочетании с другими функциями Excel для решения более сложных задач.
Как извлечь подстроку перед определенным символом.
В некоторых случаях вам может понадобиться извлечь часть текста, которая предшествует определенному символу. Например, вы можете захотеть извлечь имена из столбца имен или извлечь коды стран из столбца телефонных номеров. Проблема в том, что каждое имя и каждый код содержат разное количество символов, поэтому вы не можете просто указать точное количество символов для разделения, как мы это сделали в примере выше.
Если имя и фамилия разделены пробелом, проблема сводится к определению положения этого разделителя. Это можно легко сделать с помощью кнопок ПОИСК или ПОИСК .
Предполагая, что полное имя находится в ячейке A2, положение пробела возвращается с помощью этой простой формулы: SEARCH(" "; A2)). Теперь вставьте это выражение в LEFTSIMV:
=LEFTSIMV(A2; SEARCH(" "; A2))
Чтобы еще больше улучшить результат, избавьтесь от пробелов, вычитая 1 из результата поиска. Наконец, невидимые конечные пробелы могут вызвать множество проблем, особенно если вы планируете использовать извлеченные имена в других вычислениях:
=LEFTSIMV(A2; SEARCH(" "; A2)-1)
Таким же образом можно извлечь коды стран из столбца телефонных номеров. Единственное отличие заключается в том, что теперь вам нужно знать положение первого дефиса ('-'), а не пробела:
=LEFTSIMV(A2; SEARCH("-"; A2)-1)
Думаю, вы догадались, что с помощью этой универсальной формулы можно получить подстроку, которая предшествует любому другому символу:
LEVSIMB( строка ; SEARCH( символ ; строка ) - 1)
Как удалить последние N символов.
Вы уже знаете, как отрезать фрагмент от начала текстовой строки. Но иногда вы можете захотеть сделать что-то другое - удалить определенное количество символов из конца строки, а остальные перенести в другую ячейку. Для этого можно также использовать функцию LEVSIMW в сочетании с DLSTR , например:
LEVSIMW ( текст ; DLSTR ( текст ) - количество_символов_для_удаления )
Это работает по такой логике: DLSTR получает общее количество символов в ячейке, затем вы вычитаете количество ненужных символов из общей длины, а LEVSIMV возвращает остаток.
Например, чтобы удалить последние 7 символов из текста в A2, напишите выражение следующим образом:
=LEVSIMV(A2; DLSTR(A2)-11)
Как видно на скриншоте, формула успешно удаляет слово 'products' (8 букв, разделитель и 2 пробела) из текстовых значений в столбце A.
Однако использовать метод, о котором мы говорили в предыдущем примере, будет очень сложно, поскольку все разделители разные.
Как заставить ЛЕВСИМВ возвращать число.
Как вы уже знаете, LEVSIMW в Excel всегда возвращает текст, даже если вы извлекаете из ячейки несколько первых цифр. Для вас это означает, что вы не сможете использовать эти результаты в расчетах или в других функциях Excel, работающих с числами.
Как же заставить LEVSIMW выводить числовое значение, а не текстовую строку, состоящую из чисел? Просто заключив его в функцию VALUE, которая используется для преобразования текста, состоящего из цифр, в число.
Например, чтобы извлечь символы перед разделителем "-" из A2 и преобразовать результат в число, сделайте следующее:
=SIGN(LEFTSYMBOL(A2;SEARCH("-";A2;1)-1))
Результат будет выглядеть примерно так:
Как видно на скриншоте выше, числа в столбце B, полученные в результате преобразования, автоматически выравниваются в ячейках вправо, в отличие от выровненного по левому краю текста в столбце A. Поскольку Excel распознает суммы как числа, вы можете суммировать и усреднять эти значения, находить минимальное и максимальное значения и выполнять с ними любые другие вычисления.
Это лишь некоторые из многих возможных вариантов использования LEVSIMS в Excel.
Другие примеры формул LEVSIMW можно найти на следующих страницах:
- Разделите строку запятой, двоеточием, косой чертой, тире или другим разделителем
- Как разделить текстовую строку типа "число + текст" на столбцы
- Подсчет количества символов до или после символа
Не работает ЛЕВСИМВ — причины и решения
Если LEVSIMW не работает корректно на вашем листе, скорее всего, это связано с одной из причин, перечисленных ниже.
1. Аргумент «количество знаков» меньше нуля
Если ваша формула возвращает ошибку #ZNAK!, первое, что вам нужно проверить, это значение аргумента number_characters. Если вы видите отрицательное число, просто уберите знак минус, и ошибка исчезнет (конечно, очень маловероятно, что кто-то специально поставит отрицательное число, но человек может ошибиться :)
Самая распространенная ошибка #SIGN! возникает, когда аргумент является результатом вычисления, а не написан от руки. В этом случае скопируйте это вычисление в другую ячейку или выделите его в строке формул и нажмите F9, чтобы увидеть результат. Если значение меньше 0, проверьте наличие ошибок.
Чтобы лучше проиллюстрировать этот момент, давайте возьмем формулу, которую мы написали в первом примере для извлечения телефонных кодов стран:
LEVSIMV(A2; SEARCH("-"; A2)-1)
Как вы помните, функция ПОИСКПОЗ в наших примерах вычисляет позицию первого дефиса в исходной строке, из которой мы затем вычитаем 1, чтобы удалить дефис из конечного результата. Если я случайно заменю -1, скажем, на -11, Excel выдаст ошибку #DEFINE! потому что отрицательное количество букв и цифр не может быть извлечено:
2. Начальные пробелы в исходном тексте
Если вы скопировали данные из Интернета или экспортировали их из другого внешнего источника, довольно часто такие неприятные сюрпризы появляются в самом начале текста. И вы вряд ли заметите их присутствие, пока что-то не пойдет не так. Изображение ниже иллюстрирует проблему:
Чтобы избавиться от ведущих пробелов в листах, используйте LEFT SPACE (TRIM).
3. ЛЕВСИМВ не работает с датами.
Если вы попытаетесь использовать LEVSIMW для получения отдельной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Дело в том, что в Microsoft Excel все даты хранятся в виде чисел, представляющих количество дней с 1 января 1900 года. То, что вы видите в ячейке, - это просто визуальное представление даты. Вы можете легко изменить его отображение, используя другой формат.
Например, если у вас есть дата 15 июля 2020 года в ячейке A1 и вы пытаетесь извлечь день, используя выражение LEVSIMV(A1;2). Результатом будет 44, что является первыми двумя цифрами числа 44027, которое во внутренней системе Excel обозначает 15 июля 2020 года.
Чтобы извлечь определенную часть даты, воспользуйтесь одной из следующих функций: DAY(), MONTH() или YEAR().
Если даты введены в виде текстовых строк, то LEVSIMV будет работать без проблем, как показано на скриншоте справа:
Вот как можно использовать функцию LEVSIMW в Excel.
Все описанные выше операции, а также многие другие действия с текстовыми значениями в Excel можно выполнять с помощью специального инструмента работы с текстом, входящего в состав дополнения Ultimate Suite. Вот лишь некоторые из этих функций: удаление лишних пробелов и ненужных символов, изменение регистра букв в тексте, подсчет букв и слов, добавление одинакового текста в начало или конец всех ячеек в диапазоне, преобразование текста в числа, деление на ячейки, выделение отдельных слов, поиск дубликатов слов.
