Как найти заглавную букву в строке excel

Обновлено: 03.07.2024

Пользователи Excel, несмотря на то, что поиск нужного символа кажется довольно простой задачей, часто не понимают, как сделать это. С некоторыми из них проще, с частью – сложнее. Также иногда появляются проблемы с поиском таких символов, как знак вопроса или звездочка из-за того, что они используются в фильтрах. Сегодня мы опишем способы доставать символы самых разных типов.

Как найти в ячейке текстовые символы (буквы и цифры)

Для начала попробуем выполнить самую простую задачу: определить наличие текстовых символов в ячейки и найти тот, который надо. Для этого нужно воспользоваться дополнением !SEMTools, с помощью которого можно искать символы разных типов. Последовательность действий следующая:

Выделяем тот диапазон, который является изначальным и копируем его в соседнюю колонку.
Затем выделяем второй диапазон.
Открываем вкладку «!SEMTools». Там в самой левой части панели инструментов будет вкладка «Обнаружить».
После этого открываем меню «Символы».
Затем появится дополнительное меню, в котором надо найти пункт «Буквы-цифры» и нажать на него.

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

Как найти в ячейке таблицы цифры

Иногда нужно определить ячейки, в которой есть цифры, но они находятся вместе с текстом. Когда таких ячеек становится очень много, может быть довольно нелегко определить их. Перед тем, как реализовывать эту задачу, нужно определиться с некоторыми базовыми терминами. Главное наше понятие – «обнаружить». Это означает – проверить, есть ли определенный тип символа в строке. Если да, возвращается значение «ИСТИНА», если нет «ЛОЖЬ». Если же кроме поиска цифр в ячейке пользователь хочет произвести иные действия, то можно воспользоваться дальнейшими разделами этой инструкции.

Второе понятие, которое нужно разобрать – цифры. Это интегральный термин, который обозначает аж 10 символов, которые соответствуют числам от 0 до 9. Соответственно, чтобы проверить наличие чисел, пользователю нужно 10 раз проверить диапазон. Это можно сделать с помощью функции ЕСЛИ, но такой подход требует очень много времени.

Чтобы решить эту проблему, можно воспользоваться специальной формулой, которая выполнит все проверки за один раз: =СЧЁТ(ПОИСК(;A1))>0. Эта функция имеет тот же синтаксис, что и та, которая ищет кириллические символы в тексте.

Также можно воспользоваться надстройкой, в которой уже вмонтирован макрос для того, чтобы выполнять эту задачу. В таком случае достаточно просто воспользоваться специальной вкладкой !SEMTools, которую нужно применять на дополнительном столбце, который является полной копией того, который является исходным.

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

Предположим, нам нужно найти только определенные цифры из всех приведенных. Как можно это сделать? Для начала давайте покажем, как это делать с помощю !SEMTools. Использовать инструмент просто. Достаточно в скобках записывать все нужные цифры, и потом нажать кнопку ОК для подтверждения. С помощью этого же метода можно найти латиницу или найти большие буквы в строке текста.

Также можно воспользоваться формулой для того, чтобы отыскать в диапазоне ячеек необходимые цифры. Для этого надо применять комбинацию функций СЧЕТ и ПОИСК. С ее помощью можно обнаружить не только отдельные цифры, но и целые числовые последовательности: =СЧЁТ(ПОИСК(;A1))>0.

Иногда надо найти числа, отделенные пробелами. В этом случае они называются словами-числами. Чтобы их найти, необходимо также воспользоваться соответствующими инструментами !SEMTools. На этой анимации наглядно показано, какие действия нужно выполнить, чтобы это сделать.

Как узнать, что ячейка Эксель содержит латиницу

Очень часто пользователи Excel смешивают понятия «Найти» и «Извлечь», хотя между ними довольно большая разница. Под первым выражением подразумевают проверку, есть ли определенный символ в текстовой строке или диапазоне данных. В свою очередь, под понятием «Извлечь» подразумевают вытащить нужный символ из текста и передать его другой функции или записать в ячейку.

Что же нужно делать для того, чтобы обнаружить латиницу? Например, можно воспользоваться специальными шрифтами, которые дадут возможность определить английские символы на глаз. Например, это делает шрифт Dubai Medium, который делает английские символы жирными.

Но что же делать, если много данных? В этом случае определения на глаз нужной последовательности значений для того, чтобы проанализировать данные, недостаточно. В этом случае нужно искать способы автоматизации этого процесса. Есть несколько способов, как можно это сделать.

Использование специальной функции

Основная проблема поиска латинских букв, что их в два с половиной раза больше, чем цифр. Следовательно, нужно задавать программе цикл, состоящий из 26 итераций, что может быть довольно напряжно. Но если использовать формулу массива, состоящую из приведенных выше функций СЧЕТ и ПОИСК, то эта затея уже и не кажется настолько сложной: =СЧЁТ(ПОИСК(;A1))>0. Эта формула вполне подходит для большинства ситуаций. Например, если нет возможности установить соответствующие макросы, которые бы смогли делать это проще и быстрее.

В описанной выше формуле A1 – это та ячейка, в которой осуществляется проверка. Соответственно, вам нужно поставить ту, которая подходит в вашей ситуации. В результате проверки с помощью этой функции возвращается логическое значение. Если совпадения обнаруживаются, то оператор возвращает ИСТИНА, если их нет – ЛОЖЬ.

Функция ПОИСК не дает возможности учитывать регистр при поиске символов. Чтобы это сделать, надо воспользоваться оператором НАЙТИ, который выполняет те же операции, имеет такие же самые аргументы, только уже учитывает регистр. Еще один способ – сделать описанную выше формулу формулой массива. В таком случае она будет иметь следующий вид:.

Поскольку это формула массива, ее нужно указывать без скобок. При этом после ввода надо нажать комбинацию клавиш Ctrl + Shift + Enter (вместо простого нажатия клавиши ввода, как в случае с обычной функцией), после чего фигурные скобки появятся сами.

Эта формула должна записываться так, как формула массива. Следовательно, нужно нажать комбинацию клавиш Ctrl + Shift + Enter вместо простого нажатия клавиши ввода. Но есть некоторые исключения, при которых эта функция не будет работать. Нужно предварительно убедиться в том, что язык по умолчанию для программ не в кодировке Unicode стоит русский. В этом случае проблем возникнуть не должно. Эти формулы имеют некоторые отличия между собой. Вместо 33 букв последняя формула применяет только 32. То есть, она не учитывает букву ё, как кириллическую.

В этом случае, аналогично, как и в предыдущем, для поиска нужных символов с учетом регистра, необходимо использовать функцию НАЙТИ. Поэтому можно искать, к примеру, половину алфавита, записанную маленькими буквами и половину, записанную большими. Аргументы те же самые.

Как найти слова в ячейке, которые содержат кириллицу и латиницу

Мы можем логически сделать вывод, что для поиска тех слов, которые содержат и кириллицу, и латиницу, нужно использовать в качестве того, что мы ищем, все символы русского и английского алфавитов.

Как найти в ячейке заглавные буквы

Чтобы найти заглавные буквы, необходимо воспользоваться функцией НАЙТИ, а в качестве аргументов указать заглавные кириллические буквы (или элементы латинского алфавита, если нужно найти их) или же их коды.

При поиске кириллических букв через коды нужно помнить о том, что предварительно таблица ASCII должна быть настроена на русский язык. Простыми словами, чтобы стояла русская локализация.

Если же нужно найти какие-угодно заглавные буквы, независимо от алфавита, в котором их надо искать, нужно использовать функции СТРОЧН и СОВПАД. Последовательность действий следующая:

Делаем нижний регистр значений в отдельной ячейке.
Сравниваем результаты с первоначальными.
После этого используем следующую формулу: =НЕ(СОВПАД(СТРОЧН(A1);A1))

Если эти ячейки не совпадают, это говорит о том, что какой-то из символов в изначальной ячейке был в верхнем регистре.

Поиск символов в Excel при помощи регулярных выражений

Можно также воспользоваться регулярными выражениями для того, чтобы найти символы. Лучше всего это делать с помощью инструмента !SEMTools, поскольку он автоматизирует множество процессов их использования. Спектр использования регулярных выражений в Excel достаточно широк. Мы же остановимся прежде всего на функциях ПОИСК, ЗАМЕНИТЬ, ИЗВЛЕЧЬ.

Приятная новость: эти функции можно использовать как в Гугл таблицах, так и в Excel с этой настройкой.

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

Что делать с найденными символами

Хорошо. Предположим, мы нашли символы. А что с ними можно делать дальше? Здесь есть несколько возможных вариантов, как можно поступить. Например, можно их удалить. Например, если мы среди кириллических значений нашли латиницу. Также можно заменить ее на аналогичный символ, только в кириллице (например, большую английскую М на русскую М) или же извлечь этот символ для использования в другой формуле.

Удаление лишних символов в Excel

Извлечение определенных символов в Excel

Для этого можно использовать функцию «Найти», но также можно использовать соответствующее регулярное выражение, где первым аргументом является текст, который надо извлечь, а вторым – та ячейка или диапазон, в которой будет осуществляться поиск.

Изменение символов в Excel

Процедура такая же самая, как и удаление, только искомый символ нужно заменять на другой знак (в том числе, и на непечатаемый), а не писать в соответствующем аргументе пустую строку.

Буквы могут находиться в ВЕРХНЕМ и нижнем регистре (ПРОПИСНЫЕ и строчные). Текстовые строки, соответственно, могут состоять целиком из строчных или ПРОПИСНЫХ букв, а также состоять из букв находящихся в разном РЕгиСТре (например, ЗагоЛовок). Научимся подсчитывать значения с учетом их регистра.

Подсчет текстовых значений с учетом регистра разобьем на несколько частных случаев:

подсчет значений из букв в ВЕРХНЕМ регистре ;
подсчет значений из букв в нижнем регистре ;
подсчет значений с первой Заглавной буквой;
подсчет значений из букв в РаЗныХ регистрах (включая значения с первой заглавной буквой);
подсчет значений по критерию.

Функции, учитывающие регистр: НАЙТИ() , СОВПАД() , СТРОЧН() , ПРОПИСН() , ПРОПНАЧ() , ПОДСТАВИТЬ() . В принципе, еще может помочь функция КОДСИМВ() , возвращающая код символа, т.к. код ПРОПИСНЫХ символов всегда меньше кода строчных ( КОДСИМВ("Б")=193 , а КОДСИМВ("б")=225 ) и функция СИМВОЛ() .

Пусть в исходном списке (диапазон А2:А16 ) содержатся слова, состоящие из букв в различных регистрах. Исходный список не должен содержать чисел, пустых ячеек и ошибок. (См. файл примера ).

Подсчет значений из букв в ВЕРХНЕМ регистре

Для подсчета значений из букв в ВЕРХНЕМ регистре используем формулу =СУММПРОИЗВ(--(СОВПАД(A2:A16;ПРОПИСН(A2:A16))))

Функция СОВПАД() возвращает результат ИСТИНА, если 2 значения совпадают, причем с учетом регистра. В нашем случае каждое значение, начиная с А2 до А16 , сравнивается со своей прописной версией.

Выделив в строке формул СОВПАД(A2:A16;ПРОПИСН(A2:A16)) и нажав клавишу F9 получим массив

Как и следовало ожидать, только 3 значения (ЯБЛОКИ) находятся в ВЕРХНЕМ регистре. Применяя операцию двойного отрицания переводим этот массив в форму . Далее функция СУММПРОИЗВ() возвратит сумму элементов массива, т.е. 3.

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(ПРОПИСН(A2:A16);A2:A16);1;0)) После окончания редактирования формулы в ячеке, нужно вместо ENTER нажать CTRL+SHIFT+ENTER . В принципе, можно было обойтись и без формулы массива, но тогда необходимо было бы создать вспомогательный столбец рядом с исходным, записав в каждую ячейку формулу =ЕСЛИ(СОВПАД(ПРОПИСН(A2);A2);1;0) , и просуммировать результат.

Подсчет значений из букв в нижнем регистре

Для подсчета значений из букв в нижнем регистре используем формулу

Как видно, она практически аналогична формуле предыдущего случая: только функция ПРОПИСН() заменена на СТРОЧН() .

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(СТРОЧН($A$2:$A$16);$A$2:$A$16);1;0))

Подсчет значений с первой Заглавной буквой

Для подсчета значений с первой Заглавной буквой используем формулу

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(СТРОЧН($A$2:$A$16);$A$2:$A$16);1;0))

Подсчет значений, состоящих из букв в РаЗныХ регистрах (включая значения с первой заглавной буквой)

В этом случае подсчет становится немногим более сложным.

Выделив два аргумента функции СУММПРОИЗВ() и нажав клавишу F9 получим два массива: – все значения, кроме ПРОПИСНЫХ – все значения, кроме строчных

Попарное перемножение элементов массива с последующим сложением (функция СУММПРОИЗВ() ) приводит к нужному результату – 10.

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(ПРОПИСН($A$2:$A$16);$A$2:$A$16);0; ЕСЛИ(СОВПАД(СТРОЧН($A$2:$A$16);$A$2:$A$16);0;1)))

Подсчет значений по критерию

Иногда требуется подсчитать значения соответствующие определенному критерию, например ябЛОки (причем будут подсчитаны все ячейки содержащие слово ябЛОки в любой части значения, т.е. будут подсчитаны значения типа «свежие ябЛОки »).

Для этого используем замечательную функцию СУММПРОИЗВ() : =СУММПРОИЗВ(--НЕ(ЕОШИБКА(НАЙТИ("ябЛОки";A2:A16))))

Функция НАЙТИ() ищет построчно с учетом регистра вхождение критерия ябЛОки в ячейкиот А2 до А16 . Не найдя значение равное критерию, функция возвращает ошибку, иначе - начальную позицию вхождения (в нашем случае это всегда 1).

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(ЕОШИБКА(НАЙТИ("ябЛОки";A2:A16));0;1))

В файле примера выбор критерия можно осуществить через Выпадающий (раскрывающийся) список , элементы которого сформированы из исходного диапазона без повтора .

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

Описание

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы "n" в слове "printer", можно использовать следующую функцию:

Эта функция возвращает 4, так как "н" является четвертым символом в слове "принтер".

Можно также находить слова в других словах. Например, функция

возвращает 5, так как слово "base" начинается с пятого символа слова "database". Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

Эти функции могут быть доступны не на всех языках.

Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

Искомый_текст Обязательный. Текст, который требуется найти.

Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.

Начальная_позиция Необязательный. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.

Замечание

Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Если аргумент начальная_позиция опущен, то он полагается равным 1.

Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой "МДС0093.МужскаяОдежда". Чтобы найти первое вхождение "М" в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — "МДС0093"). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Поиск именованных сущностей — задача, встречающаяся во множестве профессий. Возникает там, где в текстовом массиве встречаются те или иные имена собственные. Это могут быть названия брендов, ФИО людей, географические названия (топонимы), названия организаций и другие наименования живых и неживых существ.

Все их объединяет простое правило — они пишутся с заглавной буквы, что одновременно становится и своеобразным маркером, позволяющим их выделить среди остального текста.

При работе с большими массивами данных часто бывает предпочтительнее сначала обнаружить среди ячеек содержащие такие сущности, и далее уже производить более «тяжелые» операции:

либо удалить символы, слова, словосочетания или все содержимое ячеек

Найти прописные символы латиницы

В Excel есть функция НАЙТИ — она регистрозависимая, что позволяет найти буквы в том регистре, в котором они перечислены. В сочетании с формулой массива эта функция возвращает позицию любого найденного символа из массива.

Поскольку в ASCII таблице символов прописные английские буквы идут друг за другом по порядку с 65 по 90, можно использовать менее громоздкую формулу массива для их поиска:

Данная формула вернет ИСТИНА, если в ячейке A1 есть хотя бы одна заглавная английская буква.

Важно: фигурные скобки в данном случае появляются сами, вводить их не нужно, нужно вводить формулу без них и использовать сочетание Ctrl + Shift + Enter .

Найти заглавные символы кириллицы

Формула аналогична предыдущей, разница лишь в диапазоне символов — от 192 до 223:

Есть пара замечаний к этой формуле:

Данный диапазон не включает в себя букву Ё
Язык системы (Windows) должен быть русский, т.к. ASCII таблица привязана именно к нему.

Найти прописные символы кириллицы или латиницы

Функция не отличается изящностью, но это лучшее, что предоставляет нам Excel в его базовой конфигурации. В зависимости от наличия в строке прописных букв, функция возвращает ИСТИНА или ЛОЖЬ.

Можно решить задачу чуть более компактной формулой массива, совмещающей формулы выше:

Найти любые заглавные буквы

Помимо кириллицы и латиницы есть и другие алфавиты. Не перечислять же их нам все — формулы будут гигантскими и ресурсозатратными.

Для поиска заглавных букв нам поможет логика, функция СТРОЧН и функция СОВПАД, позволяющая сравнить значения регистрозависимым сравнением.

переведем значение в нижний регистр
сравним результат с исходным
если не совпадут, значит, хотя бы один символ был в верхнем регистре
Вуаля!

А вот и формула:

Найти любые строчные буквы

Формула аналогична той, что ищет символы в верхнем регистре. Единственное отличие — в ней используется функция ПРОПИСН. Вот формула:

Инструмент «Обнаружить заглавные буквы» в !SEMTools

Чтобы не приходилось запоминать сложный синтаксис составных функций или носить их с собой на компьютере в отдельном файле, можно пользоваться готовыми решениями. !SEMTools располагает набором подобных возможностей, включая и процедуру обнаружения заглавных букв.

Макрос находится в группе обнаружить в первом меню «Символы». Инструкция безумно проста:

скопируйте исходный столбец с данными в соседний
оставив выделенным скопированный диапазон (иначе говоря, ничего больше не делая) выберите процедуру в меню «Найти символы«

Ячейки, содержащие заглавные буквы, заменятся на значение «ИСТИНА», не содержащие их — на «ЛОЖЬ». Далее можно фильтровать, сортировать, копировать и производить любые дальнейшие действия с маркированными таким образом данными.

Более подробную инструкцию по использованию функционала надстройки можно посмотреть на видео, где рассмотрены некоторые подводные камни:

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

Измените все буквы на ВЕРХНИЙ или правильный регистр с помощью Kutools for Excel

Найти позицию первого верхнего регистра в строке с формулой

Вот формула, которая может помочь вам быстро найти позицию первого верхнего регистра в строке, сделайте следующее:

Выберите пустую ячейку рядом со строкой, в которой вы хотите найти первый верхний регистр, например B1, введите эту формулу =MIN(IF(ISERROR(FIND(CHAR(ROW(INDIRECT("65:90"))),A1)),"",FIND(CHAR(ROW(INDIRECT("65:90"))),A1)))

Нажмите Shift + Ctrl + Enter , чтобы получить правильный результат, а затем перетащите маркер автозаполнения по ячейкам, которые необходимы для применения этой формулы. Смотрите скриншот:

Найти позицию первого верхнего регистра в строке с помощью определенной функции

Кроме формулы, вы также можете использовать Определенную функцию, чтобы узнать позицию первого верхнего регистра в строке.

1. Включите лист, содержащий строку, которую вы хотите найти в верхнем регистре, и нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.

2. Щелкните правой кнопкой мыши на листе, в котором вы хотите использовать код. Project Explore панель и щелкните Вставить > Модульи вставьте приведенный ниже код в пустой скрипт модуля. Смотрите скриншот:

Код: найти позицию первого верхнего регистра в строке.

3. Сохраните код и вернитесь к листу, который вы используете, и выберите пустую ячейку рядом со строкой, которую вы хотите найти в верхнем регистре, введите эту формулу. = GetFirstUpper (F1) , Нажмите Enter key, а затем перетащите дескриптор автозаполнения на ячейки, в которых нужна эта формула. Смотрите скриншот:

Чаевые: Если в строке нет верхнего регистра, результат отображается как -1.

Измените все буквы на ВЕРХНИЙ или правильный регистр с помощью Kutools for Excel

Обычно при работе с Excel вам может потребоваться изменить буквенные строки на ВЕРХНИЕ, Правильный регистр, строчные буквы, Регистр предложений или ToGgLe CaSe, как показано ниже.

Работы С Нами Kutools для Exceл, вы можете применить его Изменить регистр утилита для быстрой смены регистра букв по мере необходимости.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите строки, регистр которых вы хотите изменить, и нажмите Kutools > Текст > Изменить регистр. Смотрите скриншот:

2. Затем в всплывающем Изменить регистр диалоговом окне выберите нужный вариант в Изменить тип раздел, и вы можете предварительно просмотреть результат изменения на панели предварительного просмотра. Смотрите скриншот:

3. Затем нажмите Ok or Применить изменить регистр.

Читайте также: