Четыре метода извлечения подстроки в Excel • BUOM
9 декабря 2021 г.
Профессионалы используют Excel для выполнения самых разных задач, таких как организация и фильтрация данных, создание графиков и диаграмм и поиск конкретной информации. Одной из задач, которую позволяет выполнять Excel, является извлечение определенной части текста из ячейки и отображение ее в другой ячейке, называемой подстрокой. Знание того, как извлечь подстроку, может помочь вам улучшить свои навыки работы с Excel и повысить производительность. В этой статье мы обсудим, как извлечь подстроку в Excel, в том числе что такое подстрока, функции, которые вы можете использовать, и четыре метода для получения разных результатов.
Что такое подстрока в Excel?
В Excel подстрока — это часть текста, содержащегося в одной ячейке. Когда вы вводите данные в электронную таблицу Excel, каждая ячейка обычно содержит определенную часть данных. Вся часть данных, содержащихся в ячейке, представляет собой строку.
Вы можете использовать подстроки для нацеливания на определенные разделы данных из всей строки. Например, если вы храните такие данные, как адреса электронной почты, вы можете выбрать извлечение только части имени пользователя электронной почты, то есть всего, что находится до символа «@». Этот навык может помочь вам найти и использовать конкретную информацию из ячеек.
Что такое функции Excel ТЕКСТ?
Хотя в Excel нет специальной функции для извлечения подстрок, есть несколько функций TEXT, которые вы можете использовать для выполнения этой задачи. Каждая функция имеет определенный синтаксис или шаблон, который можно использовать для извлечения текста. Есть семь функций TEXT, которые вы можете использовать в Excel для извлечения подстрок:
Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)
1. ПРАВАЯ функция
Функция ПРАВИЛЬНО позволяет вам отделить и извлечь часть строки внутри ячейки, начиная с самого правого символа и заканчивая несколькими указанными вами символами.
Функция имеет следующий синтаксис:
=ВПРАВО(Текст, количество символов)
или
=ВПРАВО(Имя ячейки, количество символов)
Где:
RIGHT — это имя функции.
«Текст» — это постоянная строка, такая как целое слово или число, например Apples, 12345 или A51.
Количество символов — это количество символов, которое вы хотите извлечь, начиная с самого правого символа в тексте или ячейке.
«Имя ячейки» — это переменная строка, которая ссылается на конкретную ячейку, такую как A1, B32 или AB 10.
2. ЛЕВАЯ функция
Функция LEFT позволяет вам отделить и извлечь подстроку, начиная с самого левого символа и заканчивая несколькими указанными вами символами. Функция имеет следующий синтаксис:
=LEFT(Текст, количество символов)
или
=LEFT(Имя ячейки, количество символов)
Где:
LEFT — это имя функции.
«Текст» — это постоянная строка, такая как целое слово или число, например бананы, 54321 или B52.
«Количество символов» — это количество символов, которое вы хотите извлечь, начиная с самого левого символа в тексте или ячейке.
«Имя ячейки» — это переменная строка, которая ссылается на конкретную ячейку, такую как B1, Z97 или BA3.
3. СРЕДНЯЯ функция
Функция MID позволяет вам отделять и извлекать подстроку, начинающуюся после нескольких символов, которые вы указываете при вводе функции. Он имеет следующий синтаксис:
=MID(Текст, начальный номер, количество символов)
или
=MID(Имя ячейки, начальный номер, количество символов)
Где:
MID — это имя функции.
«Текст» представляет собой постоянную неразрывную строку, такую как слово «манго», число 547 или буквенно-цифровое число, такое как M5.
«Начальный номер» — это количество символов слева, с которого вы хотите начать извлечение.
«Количество символов» — это количество символов после начального числа, которое вы хотите извлечь.
«Имя ячейки» — это переменная строка, которая ссылается на конкретную ячейку, такую как M12, N2 или C418.
4. НАЙТИ функцию
Функция НАЙТИ позволяет искать определенную строку или символ в электронной таблице Excel. Хотя он не разделяет строки сам по себе, вы можете использовать его как часть других функций для создания определенных запросов на извлечение. Его синтаксис:
=НАЙТИ(Поисковое слово, текстовая строка, [starting number])
или
=НАЙТИ(Поисковое слово, имя ячейки, [starting number])
Где:
НАЙТИ — это функция, которую вы хотите, чтобы Excel выполнял.
«Поисковое слово» — это подстрока, которую вы хотите найти
«Текстовая строка» — это постоянная строка, такая как слово ананасы, число 18 или буквенно-цифровой код, такой как C4.
«Начальный номер» — это необязательный компонент, который позволяет вам искать термин после определенного количества символов в текстовой строке или имени ячейки.
«Имя ячейки» — это переменная строка, которая ссылается на определенную ячейку или диапазон ячеек, например A5, B2:C7 или AB13.
5. Функция ДЛСТР
Функция ДЛСТР позволяет вернуть точное количество символов в текстовой строке. Хотя он не находит и не разделяет символы сам по себе, вы можете использовать его с другими функциями для создания определенных запросов для Excel. Его синтаксис:
=ДЛСТР(Текст)
Или
=LEN(имя ячейки)
Где:
«Текст» — это постоянная строка, такая как слово «оранжевый».
«Имя ячейки» — это переменная строка, которая ссылается на конкретную ячейку или диапазон ячеек.
6. ПОДСТАВИТЬ функцию
Функция ПОДСТАВИТЬ позволяет найти замену определенной строки или подстроки на новую. Например, он может заменить любой экземпляр «15» на букву «о» для таких практик, как криптография и кодирование. Его синтаксис:
=ПОДСТАВИТЬ(Текст, старый текст, новый текст, [instance number]
Где:
ПОДСТАВИТЬ — это функция, которую должен выполнять Excel.
«Текст» — это ссылка на ячейку или строка постоянного значения, в которую вы хотите внести изменения.
«Старый текст» — это строка или подстрока, которую вы хотите найти и заменить новым текстом.
«Новый текст» — это строка или подстрока, которую вы хотите применить вместо старого текста.
«Номер экземпляра» — это необязательный выбор, когда вы можете заменить только один экземпляр старого текста, в противном случае вы изменяете каждый экземпляр старого текста.
7. Функция ОБРЕЗКИ
Функция TRIM позволяет удалить все пробелы из строки текста. Например, он может изменить константу «Адрес электронной почты» на константу «Адрес электронной почты». Его синтаксис:
=ОТРЕЗАТЬ(текст)
Где:
TRIM — это функция, которую должен выполнять Excel.
«Текст» — это постоянное значение, такое как фраза или ссылка на ячейку, например A1 или A3:B3.
Как извлечь подстроку в Excel
Ниже приведены четыре метода, которые вы можете использовать для извлечения подстроки в Excel:
Метод 1: использование LEFT, RIGHT и MID
Вы можете использовать функции LEFT, RIGHT и MID для извлечения определенного текста из ячейки.
Какой из них вы используете, зависит от длины строк, но шаги по их использованию одинаковы:
Начните вводить функцию: если вы хотите использовать эти функции для извлечения текста, найдите пустую ячейку в электронной таблице и введите первую часть функции, начиная со знака равенства и заканчивая первой скобкой.
Выберите свой диапазон: это ячейки, в которых вы хотите применить свою функцию. Это может быть одна ячейка, например A1, или диапазон ячеек, например B2:B24.
Завершите синтаксис: завершите ввод оставшейся части функции, используя синтаксис, описанный выше. Это может помочь вам получить желаемые результаты.
Сохраните свою работу: после того, как вы извлекли нужные значения из каждого диапазона ячеек, сохраните свою работу с помощью кнопки «Сохранить» или нажав «CTRL + S» на клавиатуре.
Способ 2: Использование TRIM
Иногда вместо того, чтобы удалить текст из строки, вы хотите удалить только пробелы.
Для этого можно использовать функцию TRIM. Ниже приведены шаги, которые вы можете выполнить, чтобы использовать функцию TRIM:
Начните вводить функцию: если вы хотите использовать функцию ОБРЕЗ, найдите пустую ячейку и начните вводить функцию, начиная со знака равенства и заканчивая первой скобкой.
Выберите строку: после того, как вы введете начальную часть функции в пустую ячейку, вы можете выбрать строку, ячейку или диапазон ячеек, где вы хотите применить функцию TRIM. Затем нажмите «Enter» на клавиатуре.
Сохраните свою работу: после того, как вы удалили пробелы из своей строки, сохраните свою работу с помощью параметра «Сохранить» в правом верхнем углу Excel или нажав «Ctrl + S» на клавиатуре.
Способ 3: Использование MID и FIND
Вы можете комбинировать некоторые функции, чтобы создать более конкретные правила для Excel. Одним из примеров является использование функции MID с функцией FIND для извлечения конкретной подстроки из диапазона ячеек.
Ниже приведены шаги, которые вы можете выполнить, чтобы использовать обе функции вместе:
Начните вводить функцию MID: порядок, в котором вы пишете функции, имеет значение, поэтому, начиная с функции MID, Excel будет следовать именно тем правилам, которые вам нужны. Начните со знака равенства и закончите ссылкой на ячейку.
Введите функцию НАЙТИ: Во второй части уравнения используйте функцию НАЙТИ в качестве начального числа. Это позволяет функции MID запускаться в правильном месте, независимо от количества символов в ссылке на ячейку.
Завершите функцию MID: после того, как вы выполните правила для функции FIND, вы сможете выполнить компоненты функции MID. Как только вы нажмете «Ввод» на клавиатуре, Excel извлечет каждую подстроку, начиная с первого экземпляра текста, который вы использовали в функции НАЙТИ.
Сохраните свою работу. Наконец, вы можете сохранить рабочий лист Excel.
Способ 4: использование быстрой заливки
Мгновенное заполнение — это метод, который можно использовать для объединения двух строк в одну ячейку, где каждая из них становится подстрокой.
Например, вы можете объединить имена в столбце A и фамилии в столбце B с именами и фамилиями в столбце C. Ниже приведены шаги, которые вы можете выполнить, чтобы использовать мгновенное заполнение.
Запустите шаблон: Excel — это программа, которая понимает шаблоны в данных, поэтому вы можете использовать ее для использования функции мгновенного заполнения. В приведенном выше примере, если вы введете имя и фамилию в ячейки C1 и C2, Excel предложит вам продолжить этот шаблон для остальной части столбца.
Нажмите «Ввод»: как только Excel покажет вам вариант и предварительный просмотр того, что он может сделать, нажмите «Ввод» на клавиатуре, чтобы позволить Excel заполнить столбец шаблоном, который вы ему дали.
Сохраните свою работу: наконец, сохраните свою работу, используя методы, описанные выше.
Обратите внимание, что ни один из продуктов или компаний, упомянутых в этой статье, не связан с компанией Indeed.
VBA Excel. Функция Split (синтаксис, параметры, значения)
Использование функции Split в VBA Excel, ее синтаксис и параметры.
Значения, возвращаемые функцией Split. Примеры использования.
Функция Split предназначена в VBA Excel для разделения строки на подстроки по специальным меткам — разделителям. Разделителем может быть как отдельный символ, так и строка из нескольких символов. Функция Split по своему действию является обратной функции Join, которая создает одну строку из массива подстрок.
Синтаксис функции
Split (Expression,[Delimiter],[Limit],[Compare]) |
Обязательным параметром функции Split является Expression. Если остальные параметры явно не указаны, используются их значения по умолчанию.
Параметры функции
| Параметр | Описание | Значение по умолчанию |
|---|---|---|
| Expression | Строка, содержащая подстроки и разделители | Нет |
| Delimiter | Разделитель, представляющий один или более символов | Пробел |
| Limit | Максимальное число подстрок, на которые должна быть разделена входная строка | -1 |
| Compare* | Определяет, какое используется сравнение, двоичное — CompareMethod. | 0 |
*Если используется двоичное сравнение (0 или CompareMethod.Binary), функция чувствительна к регистру букв. Если используется текстовое сравнение (1 или CompareMethod.Text), функция не чувствительна к регистру букв.
Возвращаемые значения
Функция Split возвращает одномерный массив с индексацией от нуля, который содержит указанное параметром Limit число подстрок. Чаще всего, функция Split используется со значением параметра Limit по-умолчанию, равному -1, когда возвращаются все найденные в исходной строке подстроки.
Пример 1
Sub Test1() Dim a() As String a = Split(«vremya ne zhdet») MsgBox a(0) & vbNewLine & a(1) & vbNewLine & a(2) End Sub |
Результат в MsgBox:
vremya
ne
zhdet
В первом примере используются Delimiter и Limit по-умолчанию.
Пример 2
| Sub Test2() Dim a() As String a = Split(«vremya-ne-zhdet»,»-«, 2) MsgBox a(0) & vbNewLine & a(1) End Sub |
Результат в MsgBox:
vremya
ne-zhdet
Во втором примере Delimiter = «-«, а Limit = 2.
Для присваивания результатов функции Split используется предварительно объявленный текстовый динамический массив, который можно использовать в строке присваивания с пустыми скобками или без них. В представленных выше примерах массив указан без скобок.
Вы можете скопировать коды из приведенных примеров в модуль VBA своей рабочей книги Excel, посмотреть, как они работают. Поэкспериментируйте, подставляя свои данные, чтобы на практике ознакомиться с возможностями функции Split.
Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.
Excel ЛЕВАЯ функция с примерами формул
В этом учебном пособии показано, как использовать ЛЕВУЮ функцию в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу «ЛЕВАЯ» возвращать число и более.
Среди многих различных функций, которые Microsoft Excel предоставляет для работы с текстовыми данными, ЛЕВАЯ является одной из наиболее широко используемых. Как следует из названия, эта функция позволяет извлекать определенное количество символов, начиная с левой стороны текстовой строки. Однако Excel LEFT способен на гораздо большее, чем его чистая сущность. В этом уроке вы найдете пару основных левых формул для понимания синтаксиса, а затем я покажу вам несколько способов, с помощью которых вы можете использовать функцию ЛЕВАЯ в Excel далеко за пределами ее основного использования.
Функция Excel ВЛЕВО — синтаксис
Функция ВЛЕВО в Excel возвращает указанное количество символов (подстроки) от начала строки.
Синтаксис функции ВЛЕВО следующий:
ЛЕВЫЙ(текст, [число_знаков])
Где:
- Текст (обязательно) — это текстовая строка, из которой вы хотите извлечь подстроку. Обычно он предоставляется как ссылка на ячейку, содержащую текст.
- Число_знаков (необязательно) — количество символов для извлечения, начиная с левой стороны строки.
- Если num_chars опущено, по умолчанию используется значение 1, что означает, что формула Left будет возвращать 1 символ.
- Если num_chars больше, чем общая длина text , формула Left вернет весь text .
Например, чтобы извлечь первые 3 символа из текста в ячейке A2, используйте следующую формулу:
=ЛЕВО(A2, 3)
На следующем снимке экрана показан результат:
Важное примечание! LEFT относится к категории текстовых функций, поэтому результатом формулы Left всегда является текстовая строка , даже если исходное значение, из которого вы извлекаете символы, является числом. Если вы работаете с числовым набором данных и хотите, чтобы функция LEFT возвращала число, используйте ее в сочетании с функцией VALUE, как показано в этом примере.
Как использовать ЛЕВУЮ функцию в Excel — примеры формул
Помимо извлечения текста слева от строки, что еще может делать функция LEFT? В следующих примерах показано, как можно использовать ЛЕВЫЙ в сочетании с другими функциями Excel для решения более сложных задач.
Как извлечь подстроку перед определенным символом
В некоторых случаях может потребоваться извлечь часть текстовой строки, которая предшествует определенному символу. Например, вы можете получить имена из столбца полных имен или получить коды стран из столбца телефонных номеров. Проблема в том, что каждое имя и каждый код содержат разное количество символов, и поэтому вы не можете просто указать предопределенное число для 9.0003 num_chars
Если имя и фамилия разделены пробелом, проблема сводится к определению позиции пробела в строке, что легко сделать с помощью функции ПОИСК или НАЙТИ.
Предположим, что полное имя находится в ячейке A2, позиция пробела возвращается по этой простой формуле: ПОИСК(» «,A2)).
А теперь вы вставляете эту формулу в аргумент num_chars функции LEFT:
=ВЛЕВО(A2, ПОИСК(" ", A2))
Чтобы еще больше улучшить формулу, избавьтесь от завершающего пробела, вычтя 1 из результата формулы поиска (не отображается в ячейках, завершающие пробелы могут вызвать много проблем, особенно если вы планируете использовать извлеченные имена в других формулах):
=ВЛЕВО(A2, ПОИСК(" ", A2)-1)
Таким же образом вы можете извлечь код страны из столбца телефонных номеров. Единственное отличие состоит в том, что вы используете функцию поиска, чтобы узнать положение первого дефиса («-«), а не пробела:
=ВЛЕВО(A2, ПОИСК("-", A2)-1)
Подводя итог, вы можете использовать эту общую формулу для получения подстроки, которая предшествует любому другому символу:
ЛЕВЫЙ( строка , ПОИСК( символа , строка ) — 1)
Как удалить последние N символов из строки
Вы уже знаете, как использовать функцию Excel НАЛЕВО для получения подстроки из начала текстовой строки.
Но иногда вы можете захотеть сделать что-то другое — удалить определенное количество символов с конца строки и перетащить оставшуюся часть строки в другую ячейку. Для этого используйте функцию LEFT в сочетании с LEN, например:
ЛЕВЫЙ( строка, ДЛСТР( строка ) — число_символов_для_удаления )
Формула работает по следующей логике: функция ДЛСТР получает общее количество символов в строке, затем вы вычитаете количество ненужных символов из общей длины, а функция ЛЕВЫЙ возвращает оставшиеся символы.
Например, чтобы удалить последние 7 символов из текста в формате A2, используйте следующую формулу:
=ЛЕВО(A2, ДЛСТР(A2)-7)
Как показано на скриншоте ниже, формула успешно отрезает постфикс « — ToDo» (4 буквы, дефис и 2 пробела) из текстовых строк в столбце А.
Как заставить ЛЕВУЮ функцию возвращать число
Как вы уже знаете, функция Excel ВЛЕВО всегда возвращает текст, даже если вы извлекаете из числа несколько первых цифр.
Для вас это означает, что вы не сможете использовать результаты формул Left в вычислениях или в других функциях Excel, которые работают с числами.
Итак, как же заставить Excel LEFT выводить число, а не текстовую строку? Просто поместив его в функцию ЗНАЧ, предназначенную для преобразования строки, представляющей число, в число, например: ЗНАЧЕНИЕ(ЛЕВО())
Например, чтобы извлечь первые 2 символа из строки в A2 и преобразовать вывод в числа, используйте следующую формулу:
= ЗНАЧЕНИЕ (ЛЕВО (A2,2))
Результат будет выглядеть примерно так:
Как вы можете видеть на снимке экрана выше, числа в столбце B, полученные с помощью формулы Value Left, выделены в ячейках по правому краю, в отличие от текста, выровненного по левому краю в столбце A. Поскольку Excel распознает выходные данные как числа, вы можете чтобы суммировать и усреднить эти значения, найти минимальное и максимальное значение и выполнить любые другие вычисления.
Это лишь некоторые из многих возможных применений LEFT в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить пример рабочего листа функции Excel LEFT.
Для получения дополнительных примеров левой формулы, пожалуйста, ознакомьтесь со следующими ресурсами:
- Разделить строку запятой, двоеточием, косой чертой, тире или другим разделителем
- Как разделить строку по разрыву строки
- Как преобразовать 8-число в дату
- Подсчитать количество символов до или после заданного символа
- Формула массива для выполнения различных вычислений над числами в разных диапазонах
Excel ЛЕВАЯ функция не работает — причины и решения
Если функция Excel ВЛЕВО не работает должным образом на ваших листах, это, скорее всего, вызвано одной из следующих причин.
1. Аргумент Num_chars меньше нуля
Если ваша формула Excel Left возвращает #VALUE! ошибка, первое, что вы должны проверить, это значение в аргументе num_chars .
Если это отрицательное число, просто уберите минус, и ошибка исчезнет (конечно, маловероятно, что кто-то намеренно поставит туда отрицательное число, но ошибаться свойственно человеку 🙂
Чаще всего ошибка ЗНАЧ возникает, когда аргумент num_chars представлен другой функцией. В этом случае скопируйте эту функцию в другую ячейку или выберите ее в строке формул и нажмите F9, чтобы увидеть, чему она соответствует. Если значение меньше 0, то проверьте функцию на наличие ошибок.
Чтобы лучше проиллюстрировать это, давайте возьмем формулу Left, которую мы использовали в первом примере для извлечения телефонных кодов страны: LEFT(A2, SEARCH(«-«, A2)-1). Как вы помните, функция поиска в 9Аргумент 0003 num_chars вычисляет позицию первого дефиса в исходной строке, из которой мы вычитаем 1, чтобы удалить дефис из окончательного результата. Если я случайно заменю -1, скажем, на -11, формула выдаст ошибку #ЗНАЧ, потому что аргумент
2.
Начальные пробелы в исходном текстеЕсли ваша формула Excel Left не работает без видимой причины, проверьте исходные значения на наличие начальных пробелов. Если вы скопировали свои данные из Интернета или экспортировали из другого внешнего источника, многие такие пробелы могут скрываться незамеченными перед текстовыми записями, и вы никогда не узнаете, что они там, пока что-то не пойдет не так. Следующее изображение иллюстрирует проблему:
Чтобы избавиться от начальных пробелов на ваших листах, используйте функцию Excel TRIM или надстройку Text Toolkit.
3. Excel ЛЕВЫЙ не работает с датами
Если вы попытаетесь использовать функцию Excel LEFT для получения отдельной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Дело в том, что в Microsoft Excel все даты хранятся в виде целых чисел, представляющих собой количество дней, прошедших с 1 января 19 года.
00, который хранится как номер 1 (дополнительную информацию см. в формате даты Excel). То, что вы видите в ячейке, — это просто визуальное представление даты, и его отображение можно легко изменить, применив другой формат даты.
Например, если у вас есть дата 11 января 2017 года в ячейке A1, и вы пытаетесь извлечь день с помощью формулы ЛЕВЫЙ (A1,2), результатом будет 42, что является первыми двумя цифрами числа 42746. который представляет 11 января 2017 года во внутренней системе Excel.
Чтобы извлечь определенную часть даты, используйте одну из следующих функций: ДЕНЬ, МЕСЯЦ или ГОД.
В случае, если ваши даты введены в виде текстовых строк, ЛЕВАЯ функция будет работать без заминок, как показано в правой части скриншота:
Вот как вы используете функцию ВЛЕВО в Excel. Я благодарю вас за чтение и надеюсь увидеть вас снова на следующей неделе.
Вас также может заинтересовать
Функция Excel LEN: подсчет символов в ячейке
Вы ищете формулу Excel для подсчета символов в ячейке? Если это так, то вы, безусловно, попали на нужную страницу.
В этом кратком руководстве вы узнаете, как использовать функцию ДЛСТР для подсчета символов в Excel с пробелами или без них.
Из всех функций Excel функция LEN, пожалуй, самая простая и понятная. Имя функции легко запомнить, это не что иное, как первые 3 символа слова «длина». Именно это и делает функция LEN — возвращает длину текстовой строки или длину ячейки.
Другими словами, вы используете функцию ДЛСТР в Excel, чтобы подсчитать все символы в ячейке, включая буквы, цифры, специальные символы и все пробелы.
В этом кратком руководстве мы сначала кратко рассмотрим синтаксис, а затем более подробно рассмотрим некоторые полезные примеры формул для подсчета символов на листах Excel.
Функция ДЛСТР Excel
Функция ДЛСТР в Excel подсчитывает все символы в ячейке и возвращает длину строки. У него всего один аргумент, который явно необходим:
.=ДЛСТР(текст)
Где text — это текстовая строка, для которой вы хотите подсчитать количество символов.
Нет ничего проще, верно?
Ниже вы найдете несколько простых формул, чтобы получить общее представление о том, что делает функция ДЛСТР в Excel.
=LEN(123) — возвращает 3, потому что 3 числа переданы аргументу text .
=LEN("хорошо") — возвращает 4, так как слово good содержит 4 буквы. Как и любая другая формула Excel, LEN требует заключения текстовых строк в двойные кавычки, которые не учитываются.
В ваших реальных формулах LEN вы, скорее всего, будете указывать ссылки на ячейки, а не числа или текстовые строки, для подсчета символов в определенной ячейке или диапазоне ячеек.
Например, чтобы получить длину текста в ячейке A1, вы должны использовать следующую формулу:
=ДЛСТР(A1)
Ниже приведены более содержательные примеры с подробными пояснениями и скриншотами.
Как использовать функцию ДЛСТР в Excel — примеры формул
На первый взгляд функция ДЛСТР выглядит настолько простой, что вряд ли требует дополнительных пояснений.
Однако есть несколько полезных приемов, которые могут помочь вам настроить формулу Excel Len для ваших конкретных нужд.
Как подсчитать все символы в ячейке (включая пробелы)
Как уже было сказано, функция ДЛСТР в Excel подсчитывает абсолютно все символы в указанной ячейке, включая все пробелы — начальные, конечные и пробелы между словами.
Например, чтобы получить длину ячейки A2, используйте следующую формулу:
=ДЛСТР(A2)
Как показано на снимке экрана ниже, наша формула LEN насчитывала 36 символов, включая 29 букв, 1 цифру и 6 пробелов.
Подсчет символов в нескольких ячейках
Чтобы подсчитать символы в нескольких ячейках, выберите ячейку с формулой Len и скопируйте ее в другие ячейки, например, перетащив маркер заполнения. Подробные инструкции см. в разделе Как скопировать формулу в Excel.
Как только формула будет скопирована, функция ДЛСТР вернет символов для каждой ячейки отдельно .
И еще раз обращаю ваше внимание, что функция ДЛСТР считает абсолютно все, включая буквы, цифры, пробелы, запятые, кавычки, апострофы и так далее:
Примечание. При копировании формулы вниз по столбцу обязательно используйте относительную ссылку на ячейку, например LEN(A1) , или смешанную ссылку, например LEN($A1) , которая фиксирует только столбец, чтобы ваша формула Len правильно настраивалась. для нового места. Дополнительные сведения см. в разделе Использование абсолютных и относительных ссылок на ячейки в Excel.
Подсчитать общее количество символов в нескольких ячейках
Самый очевидный способ получить общее количество символов в нескольких ячейках — сложить несколько функций ДЛСТР, например:
=ДЛСТР(A2)+ДЛСТР(A3)+ДЛСТР(A4)
Или используйте функцию СУММ для подсчета количества символов, возвращаемых формулами ДЛСТР:
=СУММ(ДЛСТР(A2), ДЛСТР(A3), ДЛСТР(A4))
В любом случае формула подсчитывает количество символов в каждой из указанных ячеек и возвращает общую длину строки:
Этот подход, несомненно, прост для понимания и использования, но это не лучший способ подсчета символов в диапазоне, состоящем, скажем, из 100 или 1000 ячеек.
В этом случае вам лучше использовать функции СУММПРОИЗВ и ДЛСТР вместе, как показано в этом примере.
Как считать символы, исключая начальные и конечные пробелы
При работе с большими листами распространенной проблемой являются начальные или конечные пробелы, то есть лишние пробелы в начале или в конце элементов. Вы вряд ли заметите их на листе, но после того, как вы столкнулись с ними пару раз, вы научитесь их остерегаться.
Если вы подозреваете, что в ваших ячейках есть несколько невидимых пробелов, вам поможет функция ДЛСТР Excel. Как вы помните, он включает все пробелы в количестве символов:
Чтобы получить длину строки без начальных и конечных пробелов , просто вставьте функцию TRIM в формулу Excel LEN:
=LEN(TRIM(A2))
Как подсчитать количество символов в ячейке, исключая все пробелы
Если ваша цель состоит в том, чтобы получить количество символов без пробелов, будь то начальные, конечные или промежуточные, вам потребуется немного более сложная формула:
=ДЛСТР(ПОДСТАВИТЬ(A2," ",""))
Как вы, наверное, знаете, функция ПОДСТАВИТЬ заменяет один символ другим.
В приведенной выше формуле вы заменяете пробел («») ничем, то есть пустой текстовой строкой («»). И поскольку вы встраиваете ПОДСТАВИТЬ в функцию ДЛСТР, замена на самом деле не выполняется в ячейках, она просто указывает вашей формуле ДЛСТР вычислить длину строки без пробелов.
Более подробное описание функции ПОДСТАВИТЬ в Excel можно найти здесь: Самые популярные функции Excel с примерами формул.
Как подсчитать количество символов до или после заданного символа
Иногда вам может понадобиться узнать длину определенной части текстовой строки, а не подсчитывать общее количество символов в ячейке.
Предположим, у вас есть такой список артикулов:
И все действительные SKU имеют ровно 5 символов в первой группе. Как определить недействительные элементы? Да, подсчитав количество символов до первого тире.
Итак, наша формула длины в Excel выглядит следующим образом:
=ДЛСТР(СЛЕВА($A2, ПОИСК("-", $A2)-1))
А теперь давайте разберем формулу, чтобы вы могли понять ее логику.
- Вы используете функцию ПОИСК для возврата позиции первого дефиса («-«) в A2:
ПОИСК("-", $A2) - Затем вы используете функцию ВЛЕВО, чтобы вернуть такое количество символов, начинающихся с левой стороны текстовой строки, и вычтите 1 из результата, потому что вы не хотите включать тире:
ЛЕВЫЙ($A2, ПОИСК("-", $A2,1)-1)) - И, наконец, у вас есть функция LEN для возврата длины этой строки.
Как только количество символов будет достигнуто, вы можете сделать еще один шаг и выделить недопустимые SKU, настроив простое правило условного форматирования с помощью формулы, например =$B2<>5:
Или вы можете идентифицировать недопустимые SKU, внедрив приведенную выше формулу LEN в функцию ЕСЛИ:
=ЕСЛИ(ДЛСТР(СЛЕВА($A2, ПОИСК("-", $A2)-1))<>5, "Недействительно", "")
Как показано на снимке экрана ниже, формула отлично идентифицирует недопустимые SKU на основе длины строки, и вам даже не нужен отдельный столбец с количеством символов:
Аналогичным образом вы можете использовать функцию Excel LEN для подсчета количества символов после определенного символа.
Например, в списке имен вам может понадобиться узнать, сколько символов содержит фамилия. Следующая формула LEN делает свое дело:
=ДЛСТР(ПРАВО(A2,ДЛСТР(A2)-ПОИСК(" ",A2)))
Как работает формула:
- Сначала вы определяете положение пробела (» «) в текстовой строке с помощью функции ПОИСК:
ПОИСК(" ",A2))) - Затем вы подсчитываете, сколько символов следует за пробелом. Для этого вы вычитаете пробел из общей длины строки:
LEN(A2)-SEARCH(" ",A2))) - После этого у вас есть ПРАВАЯ функция, чтобы вернуть все символы после пробела.
- И, наконец, вы используете функцию LEN для получения длины строки, возвращаемой функцией RIGHT.
Обратите внимание, для корректной работы формулы в каждой ячейке должен быть только один пробел, т.е. только Имя и Фамилия, без отчеств, титулов и суффиксов.
Вот как вы используете формулы LEN в Excel. В следующей статье мы собираемся изучить другие возможности функции ДЛСТР Excel, и вы узнаете еще несколько полезных формул для подсчета символов в ячейках и диапазонах Excel.