Завантажте зразок робочої книги
Цей підручник демонструє, як користуватися Функція Excel LEN в Excel для підрахунку кількості символів.
Огляд функції LEN
Функція LEN підраховує кількість символів у текстовому рядку.
Щоб скористатися функцією робочого аркуша Excel LEN, виберіть клітинку та введіть:
(Зверніть увагу, як виглядають вхідні дані формул)
Функція LEN Синтаксис та входи:
= LEN (текст)
текст - Рядок тексту.
Що таке функція LEN?
Функція Excel LEN, скорочена від “length”, вказує кількість символів у даному текстовому рядку.
Хоча він використовується переважно для рядкових (текстових) даних, ви також можете використовувати його для числових даних - але, як ми побачимо, є кілька речей, які потрібно мати на увазі.
Як користуватися функцією LEN
Щоб скористатися функцією Excel LEN, введіть наступне, розмістивши будь -який текстовий рядок, посилання на клітинку або формулу між дужками:
= LEN (B2)
Давайте розглянемо деякі приклади:
У прикладі №1 ми запитали у LEN, скільки символів у комірці В2, яка містить «Лука» - тому Excel повертає 4.
Як поєднати функцію LEN
Якщо ви хочете повернути загальну кількість символів у двох клітинках, у вас є кілька варіантів. Ви можете або об’єднати дві клітинки, використовуючи амперсанд (“&”), як у прикладі №1, або використовувати LEN для обох клітинок і підсумувати результати, як у прикладі №2.
Важлива примітка: LEN враховує пробіли як символи. Подивіться на приклад №3 - у цьому випадку LEN повертає 14, тому що він також підраховує пробіл між "Лукою" та "Скайуокером".
Як LEN обробляє цифри
Функція Excel LEN призначена для повернення кількості символів у текстовому рядку. Якщо ви передаєте число, LEN спочатку перетворює його на рядок, а потім повертає його довжину.
Як бачите, LEN підраховує десяткові коми, і якщо ви відформатуєте клітинку у валюті, символ валюти не зараховується.
Зауважте також, що хоча форматування валюти відображає цифри до двох знаків після коми, LEN не враховує нулі в кінці.
Як LEN обробляє форматовані клітинки
Подивіться на наступні приклади:
Клітини В2 і В3 містять одну і ту ж цифру: 3.33333. То чому LEN повертає різні значення для кожного?
Відповідь полягає в тому, що в B2 я набрав значення 3.33333, але в B3 я набрав формулу = 10/3, а потім відформатував її до 6 знаків після коми.
З форматованими клітинками LEN повертає фактичні значення комірки, незалежно від кількості знаків після коми, які ви вибрали для відображення.
Але чому 16? Це тому, що Excel може зберігати максимум 15 чисел у комірці, а також вирізатиме довші числа, коли вони досягнуть цієї межі. Тож LEN повертає числа 16: 15 плюс десяткову точку.
Як LEN обробляє дати
Excel зберігає дати у вигляді чисел, представлених як кількість днів з 0 січня 1900 року.
Приклади №1 та №2 еквівалентні, просто формат В2 відформатовано як дату, а В3 - як число. Тож LEN повертає 5 у будь -якому випадку.
Приклад №3 відформатовано як текст, тому LEN повертає 10. Це те саме, що введення = LEN (“27/12/1977”).
Корисні приклади LEN
Ви можете поєднувати LEN з деякими функціями Excel деякими корисними способами.
Обрізання заданої кількості символів з кінця рядка
Ви можете поєднати LEN з LEFT, щоб обрізати задану кількість символів з кінця рядка. Якщо рядок, який потрібно обрізати, був у форматі А2, і ви хотіли обрізати 5 символів з кінця, ви б зробили це так:
= ВЛІВО (A2, LEN (A2) -5)
ЛЕВА функція Excel повертає задану кількість символів від початку рядка.
Перевірте приклад нижче:
= ВЛІВО (A2, LEN (A2) -5)
Тут ми маємо ряд довідкових номерів з чотиризначним кодом міста на кінці. Ми використовували LEN з LEFT, щоб взяти рядки в стовпці A і повернути кількість символів, рівну довжині рядка мінус 5, що дозволить позбутися чотирьох цифр і дефіса.
Використання LEN без урахування пробілів перед і позаду
Іноді наші клітинки можуть містити пробіли до та після фактичних даних. Це звичайне явище, коли дані вводяться вручну, і іноді люди випадково забирають місце під час копіювання та вставки даних.
Це може скинути результати нашої функції LEN, але ми можемо вирішити цю проблему, поєднавши LEN з TRIM, наприклад:
= ДОГОЛОГА (ТРИМ (A2))
TRIM видаляє будь -які кінцеві або провідні пробіли з рядка. У наведеному нижче прикладі кожне речення має пробіли перед текстом або після нього. Зверніть увагу на різні результати між LEN та LEN з TRIM.
Використання LEN з SUBSTITUTE, щоб уникнути підрахунку заданого символу
Якщо ви хочете підрахувати кількість символів у рядку, але не хочете, щоб Excel підраховував певний символ, ви можете це зробити, поєднавши LEN з SUBSTITUTE.
За допомогою SUBSTITUTE ви можете взяти рядок і поміняти будь -який символ або підрядок у ньому на будь -що інше. Ми можемо використовувати SUBSTITUTE, щоб замінити символ, якого ми не хочемо рахувати, нічим, ефективно видаливши його.
Наприклад:
= SUBSTITUTE ("Хан Соло", "", "")
Повернеться:
HanSolo
Отже, уявіть, що у нас є список імен, і ми хочемо знати кількість символів у кожному імені. Однак ім’я та прізвище зберігаються в одній комірці, і ми не хочемо рахувати пробіл між ними.
Ми можемо поєднати LEN із SUBSTITUTE наступним чином:
= LEN (ЗАМІНА (A2, "", ""))
Що дасть нам такі результати:
Зауважте, що оскільки четвертий приклад не містить пробілів, обидві формули дають однаковий результат.
Знайдіть N -е слово у рядку
Ми можемо об'єднати TRIM, MID, SUBSTITUTE, REPT з LEN наступним чином, щоб отримати останнє слово рядка.
= TRIM (MID (SUBSTITUTE (A $ 2, "", REPT ("", LEN (A $ 2))), (B2-1)*LEN (A $ 2) +1, LEN (A $ 2)))
Що дасть нам такі результати:
Витягти номер зліва/справа рядка
Коли ви працюєте з Excel, вам потрібно точно визначити число з даної деталі. Іноді вам потрібно точно визначити число зліва, а іноді - з правого. Тож функція LEN допоможе вам виконати це завдання, поєднавши її з кількома іншими функціями.
Витяг номер зліва
Ми могли б об'єднати LEFT, SUM, SUBSTITUTE з LEN наступним чином:
= ВЛІВО (A2, SUM (LEN (A2) -LEN (ЗАМІНА (A2, {"0", "1", "2", "3", "4", "5", "6", "7") , "8", "9"}, ""))))
Що дасть нам такі результати:
Витягніть номер праворуч
Ми можемо поєднати RIGHT, MIN, FIND з LEN наступним чином:
= ПРАВО (A2, LEN (A2) -MIN (НАЙДИ ({0,1,2,3,4,5,6,7,8,9}, A2 & "0123456789")+1)
Що дасть нам такі результати:
LEN з REPT, щоб додати провідні ZEROS до номера
Додати провідні ZEROS дуже корисно під час роботи з Excel. У цьому прикладі ми пояснимо, як це можна зробити, поєднавши LEN з REPT.
= REPT (0, 6-LEN (A2)) & A2
У наведеному вище прикладі ми пояснюємо додавання початкових нулів лише до 5 цифр, але, виходячи з вимоги, ви можете змінити число 6, щоб отримати потрібне. Ви можете звернутися до прикладу нижче, щоб отримати нуль до 10 цифр.
Інші приклади додавання провідних ZEROS до номера.
Нижче наведена таблиця допоможе вам надати більше способів додавання початкових нулів до встановленого числа.
LEN у Google Таблицях
Функція LEN працює точно так само в Google Таблицях, як і в Excel:
додаткові нотатки
Функція LEN дуже корисна при роботі з іншими текстовими функціями. Наприклад, зліва, справа або посередині.
І, нарешті, однією з найбільш корисних ролей, яку може виконувати функція LEN, є перевірка діапазону, який повинен мати певну довжину.
У цьому прикладі користувачеві потрібно ввести 5 -значні поштові індекси в діапазоні A2: A10. На малюнку ви помітите, що кожен раз, коли користувач вводить вміст комірки, що не дорівнює 5 символам, колір комірки змінюється, що робить його дуже легким для перевірки.
Це досягається за допомогою функції LEN із умовним форматуванням, ось як:
1. Виберіть Діапазон або клітинку, до якої потрібно застосувати умовне форматування
2. Виберіть Формат у головному меню
3. Виберіть Умовне форматування
4. У спадному меню зміна Значення комірки дорівнює до Формула така
5. Введіть = LEN (A2: A10) 5
6. Натисніть на Формат… і виберіть формат, який буде застосовано, якщо умова виконана. Я змінив Візерунок або колір фону
7. Натисніть OK, OK
Ось фрагмент мого вікна умовного форматування за допомогою LEN
Приклади LEN у VBA
Ви також можете використовувати функцію LEN у VBA. Тип:application.worksheetfunction.len (текст)
Для аргументів функції (текст тощо) ви можете або ввести їх безпосередньо у функцію, або визначити змінні для використання замість них.
Виконання наступних операторів VBA
Діапазон ("B2") = Len (Діапазон ("A2")) Діапазон ("B3") = Len (Діапазон ("A3")) Діапазон ("B4") = Len (Діапазон ("A4")) Діапазон ( "B5") = Len (Діапазон ("A5")) Діапазон ("B6") = Len (Діапазон ("A6"))
дасть такі результати
Повернення до списку всіх функцій Excel
