Эксель если начинается с буквы то
В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое.
Среди множества различных функций, которые Microsoft Excel предоставляет для работы с текстовыми данными, ЛЕВСИМВ - одна из наиболее широко применяемых. Как следует из названия, она позволяет извлекать определенное количество знаков, начиная с левой стороны текста. Однако она способна на гораздо большее, чем такая простая операция. В этом руководстве вы найдете несколько базовых формул для понимания синтаксиса, а затем я покажу вам несколько способов, с помощью которых вы можете использовать ЛЕВСИМВ далеко за пределами ее базового применения.
Cинтаксис.
Функция ЛЕВСИМВ в Excel возвращает указанное количество символов (подстроку) от начала содержимого ячейки.
ЛЕВСИМВ (текст; [колич_знаков])
- Текст (обязательно) - это текст, из которого вы хотите извлечь подстроку. Обычно предоставляется как ссылка на ячейку, в которой он записан.
- Второй аргумент (необязательно) - количество знаков для извлечения, начиная слева.
- Если параметр опущен, то по умолчанию подразумевается 1, то есть возвращается 1 знак.
- Если введенное значение больше общей длины ячейки, формула вернет всё ее содержимое.
Например, чтобы извлечь первые 6 символов из A2, запишите такое выражение:
На следующем скриншоте показан результат:
Важное замечание! ЛЕВСИМВ относится к категории текстовых функций, поэтому результатом её всегда является текстовая строка, даже если исходное значение, из которого вы извлекаете цифры, является числом. Если вы работаете с числовым набором данных и хотите, чтобы было извлечено именно число, применяйте ее вместе с функцией ЗНАЧЕН, как показано в одном из примеров ниже.
Что еще можно делать, помимо извлечения текста слева? В следующих примерах показано, как можно применять её в сочетании с другими функциями Excel для решения более сложных задач.
Как извлечь подстроку перед определенным символом.
В некоторых случаях может потребоваться извлечь часть текста, который предшествует определенному знаку. Например, вы можете извлечь имена из столбца ФИО или получить коды стран из колонки с телефонными номерами. Проблема в том, что каждое имя и каждый код содержат разное количество символов, и поэтому вы не можете просто указать точное число сколько знаков отделить, как мы сделали в приведенном выше примере.
Если имя и фамилия разделены пробелом, проблема сводится к определению положения этого разделителя. Это можно легко сделать с помощью функции ПОИСК или НАЙТИ .
Предположим, что полное имя находится в ячейке A2, позиция пробела возвращается по этой простой формуле: ПОИСК(" "; A2)). А теперь вы вставляете это выражение в ЛЕВСИМВ:
Чтобы еще немного улучшить результат, избавьтесь от конечного пробела, вычтя 1 из результата поиска. Ведь невидимые конечные пробелы могут вызвать множество проблем, особенно если вы планируете использовать извлеченные имена в других вычислениях:
Таким же образом вы можете извлечь коды стран из столбца телефонных номеров. Единственное отличие состоит в том, что вам теперь нужно узнать позицию первого дефиса ("-"), а не пробела:
Думаю, вы поняли, что можете брать эту универсальную формулу, чтобы получить подстроку, которая предшествует любому другому знаку:
Как удалить последние N символов.
Вы уже знаете, отрезать кусочек от начала текстовой строки. Но иногда вы можете захотеть сделать что-то другое — удалить определенное количество символов из конца строки и перенести оставшееся в другую ячейку. Для этого можно также применять функцию ЛЕВСИМВ в сочетании с ДЛСТР , например:
ЛЕВСИМВ ( текст ; ДЛСТР( текст ) - число_символов_для_удаления )
Это работает с такой логикой: ДЛСТР получает общее количество символов в ячейке, затем вы вычитаете количество ненужных знаков из общей длины, а ЛЕВСИМВ возвращает оставшееся.
Например, чтобы удалить последние 7 знаков из текста в A2, запишите такое выражение:
Как показано на скриншоте, формула успешно отсекает слово «продукты» (8 букв, разделитель и 2 пробела) из текстовых значений в столбце A.
При этом взять на вооружение способ, рассмотренный нами в предыдущем примере, будет очень сложно, так как все разделители разные.
Как заставить ЛЕВСИМВ возвращать число.
Как вы уже знаете, ЛЕВСИМВ в Эксель всегда возвращает текст, даже если вы извлекаете несколько первых цифр из ячейки. Для вас это означает, что вы не сможете использовать эти результаты в вычислениях или в других функциях Excel, которые работают с числами.
Итак, как заставить ЛЕВСИМВ выводить числовое значение, а не текстовую строку, состоящую из цифр? Просто заключив его в функцию ЗНАЧЕН (VALUE), которая предназначена для преобразования текста, состоящего из цифр, в число.
Например, чтобы извлечь символы перед разделителем “-” из A2 и преобразовать результат в число, можно сделать так:
Результат будет выглядеть примерно так:
Как вы можете видеть на скриншоте выше, числа в столбце B, полученные с помощью преобразования, автоматически выровнены по правому краю в ячейках, в отличие от текста с выравниванием по левому краю в столбце A. Поскольку Эксель распознает итоговые данные как числа, вы можете суммировать и усреднять эти значения, находить минимальное и максимальное значение и выполнять любые другие вычисления с ними.
Это лишь некоторые из множества возможных вариантов использования ЛЕВСИМВ в Excel.
Дополнительные примеры формул ЛЕВСИМВ можно найти на следующих ресурсах:
Не работает ЛЕВСИМВ — причины и решения
Если ЛЕВСИМВ не работает на ваших листах должным образом, это, скорее всего, связано с одной из причин, которые мы перечислим ниже.
1. Аргумент «количество знаков» меньше нуля
Чтобы лучше проиллюстрировать эту мысль, возьмем формулу, которую мы записали в первом примере для извлечения телефонных кодов страны:
ЛЕВСИМВ(A2; ПОИСК("-"; A2)-1)
2. Начальные пробелы в исходном тексте
Если вы скопировали свои данные из Интернета или экспортировали из другого внешнего источника, довольно часто такие неприятные сюрпризы попадаются в самом начале текста. И вы вряд ли заметите, что они там есть, пока что-то не пойдет не так. Следующее изображение иллюстрирует проблему:
Чтобы избавиться от ведущих пробелов на листах, воспользуйтесь СЖПРОБЕЛЫ (TRIM).
3. ЛЕВСИМВ не работает с датами.
Если вы попытаетесь использовать ЛЕВСИМВ для получения отдельной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Дело в том, что в Microsoft Excel все даты хранятся как числа, представляющие количество дней с 1 января 1900 года. То, что вы видите в ячейке, это просто визуальное представление даты. Ее отображение можно легко изменить, применив другой формат.
Например, если у вас есть дата 15 июля 2020 года в ячейке A1 и вы пытаетесь извлечь день с помощью выражения ЛЕВСИМВ(A1;2). Результатом будет 44, то есть первые 2 цифры числа 44027, которое представляет 15 июля 2020г. во внутренней системе Эксель.
Чтобы извлечь определенную часть даты, возьмите одну из следующих функций: ДЕНЬ(), МЕСЯЦ() или ГОД().
Если же ваши даты вводятся в виде текстовых строк, то ЛЕВСИМВ будет работать без проблем, как показано в правой части скриншота:
Вот как можно использовать функцию ЛЕВСИМВ в Excel.
Все описанные выше операции, а также многие другие действия с текстовыми значениями в Excel вы можете выполнить при помощи специального инструмента работы с текстом, включённого в надстройку Ultimate Suite. Вот только некоторые из этих возможностей: удалить лишние пробелы и ненужные символы, изменить регистр текста, подсчитать буквы и слова, добавить один и тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить по ячейкам, извлечь отдельные слова, найти дубликаты слов.
Как быстро посчитать количество слов в Excel - В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в…
Как быстро извлечь число из текста в Excel - В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно…
Как удалить пробелы в ячейках Excel - Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с…
Функция СЖПРОБЕЛЫ — как пользоваться и примеры - Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как…
Функция ПРАВСИМВ в Excel — примеры и советы. - В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…
Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов - ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим…
5 примеров с функцией ДЛСТР в Excel. - Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…
Как быстро сосчитать количество символов в ячейке Excel - В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Как в Excel разделить текст из одной ячейки в несколько - В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…
Как преобразовать текст в число в Excel — 10 способов. - В этом руководстве показано множество различных способов преобразования текста в число в Excel: опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое. Иногда значения в ваших…
Приношу свои извинения модераторам, но, тем не менее, сначала хочу сказать спасибо всем участникам данного сайта, посколько довольно много чего интересного я нашел здесь и воспользовался этим в своей работе.
А теперь непосредственно к теме: в представленном приложении я нарисовал таблицу, несложную, из которой необходимо выдернуть всего одно число, но загвоздка в том, что выдернуть надо при одном условии, а именно когда в ячейке B1 написано слово, начинающуюся на определенную букву. Пробовал функцией "Если" (уставший мозг после бурного трудового дня больше ничего умней не придумал), но увы, - не получилось. На одном из сайтов (не помню точно где) встретил, что вместо цифер можно вставлять как слова, так и часть оных, как начало, так и конец. Этим я воспользовался, но опять - крах. Эту функцию я вписал, но не критикуйте меня сильно ибо, как оказалось, я еще далек от уверенного пользования Excel. С первого взгляда вроде как работает, но стоит поставить в ячейке B1 другое слово, результат останется положительным. Помогите, кто чем может
Приношу свои извинения модераторам, но, тем не менее, сначала хочу сказать спасибо всем участникам данного сайта, посколько довольно много чего интересного я нашел здесь и воспользовался этим в своей работе.
А теперь непосредственно к теме: в представленном приложении я нарисовал таблицу, несложную, из которой необходимо выдернуть всего одно число, но загвоздка в том, что выдернуть надо при одном условии, а именно когда в ячейке B1 написано слово, начинающуюся на определенную букву. Пробовал функцией "Если" (уставший мозг после бурного трудового дня больше ничего умней не придумал), но увы, - не получилось. На одном из сайтов (не помню точно где) встретил, что вместо цифер можно вставлять как слова, так и часть оных, как начало, так и конец. Этим я воспользовался, но опять - крах. Эту функцию я вписал, но не критикуйте меня сильно ибо, как оказалось, я еще далек от уверенного пользования Excel. С первого взгляда вроде как работает, но стоит поставить в ячейке B1 другое слово, результат останется положительным. Помогите, кто чем может Slaventiy
Приношу свои извинения модераторам, но, тем не менее, сначала хочу сказать спасибо всем участникам данного сайта, посколько довольно много чего интересного я нашел здесь и воспользовался этим в своей работе.
А теперь непосредственно к теме: в представленном приложении я нарисовал таблицу, несложную, из которой необходимо выдернуть всего одно число, но загвоздка в том, что выдернуть надо при одном условии, а именно когда в ячейке B1 написано слово, начинающуюся на определенную букву. Пробовал функцией "Если" (уставший мозг после бурного трудового дня больше ничего умней не придумал), но увы, - не получилось. На одном из сайтов (не помню точно где) встретил, что вместо цифер можно вставлять как слова, так и часть оных, как начало, так и конец. Этим я воспользовался, но опять - крах. Эту функцию я вписал, но не критикуйте меня сильно ибо, как оказалось, я еще далек от уверенного пользования Excel. С первого взгляда вроде как работает, но стоит поставить в ячейке B1 другое слово, результат останется положительным. Помогите, кто чем может Автор - Slaventiy
Дата добавления - 23.07.2012 в 17:36Как проверить, начинается ли ячейка или заканчивается определенным символом в Excel?
Для списка данных вам может потребоваться проверить определенные ячейки, начинаются ли они или заканчиваются определенным символом. Эта статья порекомендует вам два метода решения этой проблемы.
Check if cell begins or ends with a specific character easily with Kutools for Excel:
With the Select Specific Cells utility of Kutools for Excel, you can easily check and select cells if it begins or ends with a specific character in a specific range. See screenshot:
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!
Проверьте, начинается ли ячейка или заканчивается определенным символом с формулой
Например, вы хотите проверить, начинается ли ячейка с символа «s» или заканчивается ли символом «n». Для этого примените следующие формулы.
1. Выберите пустую ячейку (ячейка B2) в столбце справки для отображения результата, введите формулу в панель формул и нажмите кнопку Enter ключ. Смотрите скриншот:
=IF(LEFT(TRIM(A2),1)="s","OK","Not OK")
2. Продолжайте выбирать ячейку B2 и перетаскивайте маркер заполнения вниз, пока не будут отмечены все ячейки.
Заметки:
1. В формуле A2 и «s» - это ячейка и первый символ, который необходимо проверить. Если результат отображается как «Не ОК», это означает, что соответствующая ячейка не начинается с символа «s», в противном случае будет получен результат ОК.
=IF(RIGHT(TRIM(A2),1)="n","OK","Not OK")
Проверьте, начинается ли ячейка или заканчивается определенным символом с помощью Kutools for Excel
Выбрать определенные ячейки полезности Kutools for Excel поможет вам легко проверить, начинается ли ячейка или заканчивается определенным символом в Excel.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон, в котором вы хотите проверить, что ячейки начинаются или заканчиваются определенным символом, а затем щелкните Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. в Выбрать определенные ячейки в диалоговом окне выберите Ячейка вариант в Тип выбора раздел, а в Конкретный тип раздел, выбрать Начинается с or Заканчивается из раскрывающегося списка, затем введите символ, который вы отметите, в текстовое поле. Щелкните значок OK кнопку.
3. Затем появится диалоговое окно, в котором будет указано, сколько ячеек квалифицировано и будет выбрано. Щелкните значок OK кнопка. Затем немедленно выбираются ячейки, которые начинаются или заканчиваются указанным символом.
Внимание: Эта утилита поддерживает выбор ячеек по двум критериям. Например, вы можете выбрать ячейки, которые начинаются с буквы «S» или заканчиваются буквой «n» одновременно, как показано на скриншоте ниже.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Например, у меня есть диапазон ячеек, который содержит некоторые имена, теперь я хочу подсчитать количество ячеек с именами, которые начинаются с буквы B, как я могу решить это на листе Excel?
Подсчет ячеек, начинающихся с определенной буквы, с помощью формул
Следующие формулы могут помочь вам получить количество ячеек, значение ячейки которых начинается с буквы B и не начинается с буквы B.
1. Введите любую из следующих формул в пустую ячейку:
= СЧЁТЕСЛИ (A2: A15; «B *»)
= СУММПРОИЗВ ((ЛЕВЫЙ (A2: A15) = "B") * 1)
Внимание: В приведенных выше формулах A2: A15 это диапазон данных, который вы хотите подсчитать, а буква B - это критерии, с которых вы хотите начать подсчет.
2, Затем нажмите Enter key, и вы получите нужный результат подсчета, см. снимок экрана:
Советы: Если вы хотите подсчитать количество ячеек, значения которых не начинаются с буквы B, вы можете применить эту формулу: = СУММПРОИЗВ ((ЛЕВЫЙ (A2: A15) <> "B") * (A2: A15 <> "")) , и нажмите Enter ключ для получения результата. Смотрите скриншот:
Подсчет ячеек, начинающихся с определенной буквы, с помощью Kutools for Excel
Если вы устали от формул, я расскажу о вас как о простом и мощном инструменте -Kutools for Excel, С его Выбрать определенные ячейки утилита, вы можете не только подсчитывать ячейки, начинающиеся с буквы B, но и выбирать эти конкретные ячейки.
После установки Kutools для Excel, выполните шаги:
1. Выберите диапазон данных, который вы хотите использовать.
2. Нажмите Кутулс > Выберите > Выбрать определенные ячейки, см. снимок экрана:
3. В Выбрать определенные ячейки диалоговое окно, выберите Ячейка из Тип выбораи укажите Начинается с or Не начинается с из раскрывающегося списка под Конкретный тип, а затем введите конкретную букву, которую вы хотите посчитать, см. снимок экрана:
4. Затем нажмите Ok or Применитьпоявится окно с подсказкой, в котором будет указано количество ячеек, соответствующих вашему условию, и они также будут выбраны. Смотрите скриншот:
Как отфильтровать данные, начинающиеся с цифры или буквы в Excel?
Предположим, есть список значений данных, которые начинаются с числовых цифр и за которыми следуют несколько букв или наоборот. И теперь вы хотите отфильтровать данные, которые начинаются с цифр или начинаются только с букв, как показано на следующих снимках экрана. Есть ли какие-нибудь хорошие приемы для решения этой задачи в Excel?
Фильтр начинается с букв Фильтр начинается с цифр
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Отфильтровать данные, начинающиеся с буквы или цифры, с помощью вспомогательного столбца
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
1. Рядом со списком данных введите эту формулу: = ЕЧИСЛО (ЛЕВО (A2) * 1) (A2 указывает первые данные, исключая заголовок в вашем списке) в пустую ячейку, см. снимок экрана:
2. Затем перетащите маркер заполнения в диапазон, в котором вы хотите применить эту формулу, и все данные, начинающиеся с числа, будут отображаться как ИСТИНА, а остальные, начинающиеся с буквы, отображаются как НЕПРАВДА, см. снимок экрана:
3. Затем отфильтруйте данные по этому новому столбцу, выберите ячейку B1 и щелкните Данные > Фильтр.
4. Затем щелкните рядом с ячейкой B1 и отметьте True или False, как вам нужно, см. снимок экрана:
5. Затем нажмите OK, все необходимые данные были отфильтрованы. Если вы проверите Правда вариант, вы отфильтруете только данные, начинающиеся с числа, и если вы отметите Ложь вариант, вы отфильтруете все данные, которые начинаются с буквы. Смотрите скриншоты:
Данные фильтра начинаются с номера Данные фильтра начинаются с буквы 6. Наконец, вы можете удалить вспомогательный столбец, как хотите.
Читайте также: