Как в excel найти номер символа

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

Описание

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

=ПОИСК(“н”;”принтер”)

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

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

=ПОИСК(“base”;”database”)

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

Важно: 

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

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

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

Синтаксис

ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])

ПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])

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

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

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

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

Замечание

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

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

• Если значение find_text не найдено, #VALUE! возвращается значение ошибки.

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

• Если start_num больше нуля или больше, чем длина аргумента within_text, #VALUE! возвращается значение ошибки.

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

Примеры

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

Данные
Выписки
Доход: маржа
маржа
Здесь “босс”.
Формула	Описание	Результат
=ПОИСК(“и”;A2;6)	Позиция первого знака “и” в строке ячейки A2, начиная с шестого знака.	7
=ПОИСК(A4;A3)	Начальная позиция строки “маржа” (искомая строка в ячейке A4) в строке “Доход: маржа” (ячейка, в которой выполняется поиск — A3).	8
=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;”объем”)	Заменяет слово “маржа” словом “объем”, определяя позицию слова “маржа” в ячейке A3 и заменяя этот знак и последующие пять знаков текстовой строкой “объем.”	Доход: объем
=ПСТР(A3;ПОИСК(” “;A3)+1,4)	Возвращает первые четыре знака, которые следуют за первым пробелом в строке “Доход: маржа” (ячейка A3).	марж
=ПОИСК(“”””;A5)	Позиция первой двойной кавычки (“) в ячейке A5.	5
=ПСТР(A5;ПОИСК(“”””;A5)+1;ПОИСК(“”””;A5;ПОИСК(“”””;A5)+1)-ПОИСК(“”””;A5)-1)	Возвращает из ячейки A5 только текст, заключенный в двойные кавычки.	босс

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

​Смотрите также​ не зависимо от​ подстроку заданного размера.​​Для заполнения столбца «Наименование»​​ в Excel».​​Ещё вариант сделать​​ «Вычитание». Если вводим​

Описание

​ что она вернет​​слово дрель, и​​которых составляет 5​​(Изменить списки…)​​А​(СИМВОЛ) возвращает определённый​ПРАВСИМВ, ПРАВБ​ доступна в выпуске​ и возвращает число​    Обязательный. Текст, который требуется​В этой статье описаны​ величины числа.​ Начальная позиция символа​ используем следующую формулу:​Функция ПСТР в Excel​

​ цифру, букву надстрочной​

​ символ амперсанд (&),​​ не одно число,​​ у которых есть​ символов.​Нажмите кнопку​

​. Вы можете ввести​ символ, код которого​Возвращают крайние справа знаки​

​ Excel 2013 и всех​

​ 9. Функция​​ найти.​​ синтаксис формулы и​В Excel подготовка и​ в строке соответствует​=ПСТР(A2;1;НАЙТИ(” “;A2))​ предназначена для выделения​​ или подстрочной. Пишем​​ то Excel понимает,​​ а массив целых​​ перед ним и​Критерий будет вводиться в​Import​А​ соответствует введённому числу,​ текстовой строки.​​ последующих версиях.​​ПОИСК​​Просматриваемый_текст​​ использование функций​ заполнение данных дополнительными​​ числу 1. Если​​Функция НАЙТИ возвращает номер​​ подстроки из строки​​ маленькую букву «о».​ что нужно соединить​ чисел – соответствующих​

​ после него как​​ ячейку​

• ​(Импорт), затем​в ячейку и​ опираясь на таблицу​

• ​ПОИСК, ПОИСКБ​Функция​всегда возвращает номер​    Обязательный. Текст, в котором​ПОИСК​ нулями являются достаточно​ данный аргумент принимает​ позиции символа пробела​ текста, переданной в​ Выделяем её. Нажимаем​ в ячейке два​ номеров строк. Для​ минимум 1 символ.​

​С2​ОК​ сослаться на неё,​ символов Вашего компьютера.​Ищут вхождения одного текстового​

Синтаксис

​Описание​

​ знака, считая от​

​ нужно найти значение​и​

• ​ простым способом в​​ дробное число из​ « » в​

• ​ качестве первого аргумента,​​ правой мышкой, выбираем​ текста.​ этого введите в​​Для создания списка, содержащего​​и выглядеть так:​

• ​и закройте меню​​ или ввести букву​ (Для Windows —​​ значения в другом​​ASC​ начала​

Замечание

• ​ аргумента​​ПОИСКБ​​ применении. Если, например,​​ диапазона допустимых значений,​​ просматриваемой строке, который​ и возвращает требуемое​ из контекстного меню​​Есть видимые символы,​​ Строке формул выражение​​ найденные значения, воспользуемся​​ «дрел?». Вопросительный знак​

• ​ настроек.​​А​​ это​ (без учета регистра).​Для языков с двухбайтовыми​​просматриваемого текста​​искомый_текст​​в Microsoft Excel.​​ каждое значение в​ дробная часть будет​ принимается в качестве​ количество символов начиная​ функцию «Формат ячейки»,​ которые видны в​ =СТРОКА(F8:F18). Выделите формулу​​ формулой массива:​​ является подстановочным знаком.​

• ​Теперь введите любую букву​​в функцию в​​ANSI​ПОДСТАВИТЬ​

• ​ наборами знаков (например,​​, включая символы, которые​​.​Функции​

• ​ поле «ID клиента»​​ усечена;​​ аргумента число_знаков функции​ с заданной позиции.​ ставим галочку у​​ ячейке. Но есть​​ и нажмите​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​

• ​Для реализации этого варианта​​ на листе Excel​​ виде текстовой строки.​, а для Macintosh​Заменяет в текстовой строке​ катакана) преобразует полноширинные​​ пропускаются, если значение​​Начальная_позиция​ПОИСК​ должно иметь по​число_знаков – обязательный аргумент,​ ПСТР. В результате​Один символ в языках​ функции «надстрочный». Нажимаем​​ невидимые символы, их​​F9​ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($G$2;Список);СТРОКА(Список)-СТРОКА($A$4);НД());””);​ поиска требуется функция​ и используйте автозаполнение,​=CODE(“A”) или =CODE(B1)​ – кодировка​ старый текст новым.​​ (двухбайтовые) знаки в​​ аргумента​    Необязательный. Номер знака в​И​ 10 чисел, то​​ принимающий значение из​​ расчетов получим:​ с однобайтовой кодировкой​ «ОК».​​ не видно в​​(см. файл примера).​СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список))))​ позволяющая использовать подстановочные​​ чтобы завершить последовательность.​​=КОДСИМВ(“А”) или =КОДСИМВ(B1)​Macintosh​Т​​ полуширинные (однобайтовые).​​начальная_позиция​

Примеры

​ аргументе​ПОИСКБ​ для реализации данной​ диапазона неотрицательных чисел,​Для заполнения столбца «Марка»​ соответствует 1 байту.​Или выделяем цифру.​ ячейках, но они​Получим массив последовательных чисел​)​ знаки: используем функцию​Вы можете создать справочную​В данном примере код​

​).​
​Преобразует аргументы в текст.​
​БАТТЕКСТ​
​больше 1.​
​просматриваемый_текст​
​находят одну текстовую​	​ задачи нужно для​	​ которое характеризует длину​
​ используем следующую формулу​	​ При работе с​ В формате ячеек​ есть и выполняют​ {8:9:10:11:12:13:14:15:16:17:18}. Подобные последовательности​	​Часть формулы ПОИСК($G$2;Список) определяет:​
​ ПОИСК(). Согласно критерию​	​ таблицу всех печатных​65​Функция​ТЕКСТ​Преобразует число в текст,​Скопируйте образец данных из​	​, с которого следует​
​ строку в другой​	​ каждого числа дописать​ в символах возвращаемой​ массива:​ такими языками результаты​ ставим галочку у​ свою функцию. Например,​ часто используются в​содержит​	​ «дрел?» (длина 5​
​ символов, используя функцию​	​соответствует первой букве​CHAR​Форматирует число и преобразует​ используя денежный формат​ следующей таблицы и​	​ начать поиск.​
​ и возвращают начальную​	​ соответственное количество нулей.​ подстроки. Если в​=ПСТР(A2:A8;НАЙТИ(” “;A2:A8)+1;100)​	​ функций ПСТР и​
​ функции «подстрочный».​	​ символ «пробел» или​ формулах массива (см.,​ли значение из​	​ символов) – должны​

support.office.com

Текстовые функции (справка)

​CHAR​ английского алфавита. Для​(СИМВОЛ) может решить​ его в текст.​

​ ß (БАТ).​​ вставьте их в​Функции​ позицию первой текстовой​ Например, для идентификатора​ качестве этого аргумента​Функция НАЙТИ возвращает позицию​ ПСТРБ (возвращает подстроку​Вставить символ кодом в​ «разрыв строки» в​ например, статью Сложение​ диапазона​

​ быть выведены 3​	​(СИМВОЛ). Такой справочник​
​ российского алфавита –​	​ следующие задачи:​ОБЪЕДИНИТЬ​СИМВОЛ​ ячейку A1 нового​ПОИСК​
​ строки (считая от​	​ с номером 1234567​ передано число 0​ символа пробела. К​
​ из строки на​	​ ячейку Excel.​ ячейке, другие символы.​
​ значений в каждой​	​A5:A13​ значения: Дрель, дрель,​
​ будет быстрым помощником,​	​ это код​Добавить разрыв строки в​Объединяет текст из нескольких​
​Возвращает символ с заданным​	​ листа Excel. Чтобы​и​ первого символа второй​ нужно дописать три​ (нуль), функция ПСТР​
​ полученному числу прибавляется​	​ основе количества заданных​Устанавливаем курсор в​
​ Они могут помешать​	​ третьей строке).​фразу «?дрел?». Критерию​ Дрели.​ если Вы захотите​192​ формулу.​
​ диапазонов или строк,​	​ кодом.​ отобразить результаты формул,​ПОИСКБ​
​ текстовой строки). Например,​	​ нуля и в​ вернет пустую строку.​
​ единица для нахождения​	​ байт) не отличаются.​ нужную ячейку.​ Excel считать, форматировать​
​Элементы этого массива можно​	​ также будут соответствовать​Для создания списка, содержащего​ ввести символ с​.​
​Найти определённую букву алфавита.​	​ вставляя между текстовыми​ПЕЧСИМВ​
​ выделите их и​	​не учитывают регистр.​ чтобы найти позицию​
​ результате получим корректную​	​ Если аргумент задан​ позиции первого символа​
​ Если на компьютере​	​Внимание!​ таблицу или текст​ вывести в отдельные​ значения содержащие фразы​
​ найденные значения, воспользуемся​	​ клавиатуры (с помощью​Далее Вы можете использовать​Сделать список всех букв​
​ значениями указанный разделитель.​	​Удаляет из текста все​ нажмите клавишу F2,​
​ Если требуется учитывать​	​ буквы “n” в​ запись 1234567000 для​ числом, превышающим количество​
​ названия марки продукта.​	​ используется двухбайтовый язык,​
​Код символа нужно​	​ в ячейках, др.​ ячейки. Для этого​
​ 5дрел7, Адрелу и​	​ формулой массива:​ клавиши​
​ функцию​	​ алфавита.​ Если в качестве​ непечатаемые символы.​
​ а затем — клавишу​	​ регистр, используйте функции​ слове “printer”, можно​
​ поля «ID клиента»​	​ символов в строке,​
​ Итоговое значение используется​	​ каждый символ при​ вводить на дополнительной​
​ Читайте об этом​	​ выделите диапазон​ т.п.​=ИНДЕКС(Список;​Alt​CHAR​Создать справочную таблицу с​ разделителя используется пустая​КОДСИМВ​
​ ВВОД. При необходимости​	​НАЙТИ​
​ использовать следующую функцию:​	​ в файле базы​ будет возвращена вся​ в качестве аргумента​
​ использовании ПСТРБ будет​	​ цифровой клавиатуре. Она​ в статье «Как​A5:A18​
​Критерий вводится в ячейку​	​НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ((ПОИСК($C$2;Список)=1)*(ДЛСТР($C$2)=ДЛСТР(Список))=1;СТРОКА(Список)-СТРОКА($A$4);НД());””);​и 4-значного кода​
​(СИМВОЛ), чтобы найти​	​ кодами символов.​ текстовая строка, функция​

​Возвращает числовой код первого​​ измените ширину столбцов,​и​=ПОИСК(“н”;”принтер”)​ данных.​ часть строки начиная​ начальная_позиция функции ПСТР.​ считаться за два.​ расположена НЕ над​ удалить лишние пробелы​, состоящий из 13​I2​

support.office.com

30 функций Excel за 30 дней: СИМВОЛ (CHAR)

​СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список))))​​ на цифровой клавиатуре).​ любую букву алфавита,​​Функция​ эффективно объединит диапазоны.​ знака в текстовой​​ чтобы видеть все​​НАЙТИБ​Эта функция возвращает​Ниже на рисунке изображено​ с указанной вторым​ Для упрощения, вместо​ Двухбайтовыми языками являются​ буквами вверху клавиатуры,​​ в Excel» тут.​​ ячеек, в Строке​​и выглядит так:​​)​​Запишите числа от 32​​ добавляя​CHAR​СЖПРОБЕЛЫ​ строке.​

​ данные.​.​​4​​ автоматическое дозаполнение недостающего​ аргументом позиции. В​ поиска номера последней​ корейский, японский и​ а – или​Символ​ формул введите выражение​

Функция 08: CHAR (СИМВОЛ)

​ «дрел?». В этом​​Часть формулы ПОИСК($C$2;Список)=1 определяет:​​ до 255 в​64​(СИМВОЛ) имеет вот​Удаляет из текста пробелы.​СЦЕП​Данные​В аргументе​​, так как “н”​​ количества символов нулями​ дробных числах, используемых​​ позиции (например, с​​ китайский.​

Как можно использовать функцию CHAR (СИМВОЛ)?

​ справа от букв,​​Excel​​ =СТРОКА(F8:F18) как формулу​ случае будут выведены​

• ​начинается​ таблицу.​
• ​к заданному номеру.​
• ​ такой синтаксис:​ЮНИСИМВ​
• ​Объединяет текст из нескольких​Выписки​

Синтаксис CHAR (СИМВОЛ)

​искомый_текст​​ является четвертым символом​​ в конец строки​ в качестве данного​

​ помощью функции ДЛСТР)​
​Пример 1. В столбце​

• ​ или на ноутбуке​​можно вставить в​ массива, нажав​ все значения,​ли значение из​В соседнем столбце, используя​=CHAR(B5+64)​CHAR(number)​Возвращает символ Юникод, на​

Ловушки CHAR (СИМВОЛ)

​ диапазонов или строк,​Доход: маржа​​можно использовать подстановочные​​ в слове “принтер”.​​ с помощью простой​​ аргумента, дробная часть​ указано число 100,​ таблицы содержатся даты,​ на клавишах букв.​ формулу, и он​CTRL+SHIFT+ENTER​заканчивающиеся​ диапазона​ функцию​=СИМВОЛ(B5+64)​СИМВОЛ(число)​ который ссылается заданное​ но не добавляет​

Пример 1: Добавляем разрыв строки

​маржа​ знаки: вопросительный знак​Можно также находить слова​​ формулы:​​ усекается.​ которое в данном​ записанные в виде​ Например, 1 –​

​ будет выполнять определенную​
​.​

​на слова дрель​A5:A13​CHAR​​Используя инструмент автозаполнения в​​number​ числовое значение.​ разделитель или аргументы​Здесь “босс”.​

Пример 2: Находим букву в алфавите

​ (​ в других словах.​​В результате каждый идентификатор​​Функция ПСТРБ имеет схожий​ примере гарантированно превышает​ текстовых строк. Записать​ на клавише с​

​ функцию. Читайте о​Функция СТРОКА() вернула номера​​ или дрели.​​с фразы «дрел?».​(СИМВОЛ), отобразите соответствующие​ Excel Вы можете​​(число) – число​​UNICODE​​ IgnoreEmpty.​​Формула​?​ Например, функция​​ получил необходимое ему​​ синтаксис:​ количество знаков в​

​ отдельно в соседних​
​ буквой О, 2​

​ таких символах в​​ строк, указанных в​​ ​Часть формулы ДЛСТР($C$2)=ДЛСТР(Список)​ этим числовым кодам​ написать два числа​​ от 1 до​​Возвращает число (кодовую страницу),​

​СЦЕПИТЬ​Описание​​) и звездочку (​​=ПОИСК(“base”;”database”)​ количество нулей, чтобы​=ПСТРБ(текст;начальная_позиция;число_байтов)​​ изначальной строке.​​ столбцах номер дня,​

​ – на клавише​
​ статье «Подстановочные знаки​

Пример 3: Перечисляем весь алфавит

​ аргументе «ссылка». Т.к.​Для создания списка, содержащего​ определяет:​ символы.​ и быстро создать​ 255, определяющее символ,​ которая соответствует первому​Объединяет несколько текстовых элементов​Результат​*​​возвращает​​ соответствовать правилу для​Она отличается единственным аргументом:​В результате расчетов получим:​

​ месяца и год,​ с буквой Л.​​ в Excel».​​ число выделенных ячеек​ найденные значения, воспользуемся​равна ли длина строки​

​Чтобы показать символы другого​
​ всю последовательность чисел.​

Создаем пользовательский список

​ который Вы хотите​ символу текста.​ в один.​=ПОИСК(“и”;A2;6)​). Вопросительный знак соответствует​5​

1. ​ дальнейшего запыления поля​число_байтов – обязательный аргумент,​Пример 3. В таблице​ выделенные из представленных​
2. ​Чтобы включить эту​Символы, которые часто​ (13) было больше​ формулой массива:​значения из диапазона​ шрифта, такого как​​ К сожалению, это​​ получить. Символ берется​
3. ​ПРОПИСН​​DBCS​​Позиция первого знака “и”​ любому знаку, звездочка —​​, так как слово​​ «ID клиента» при​ принимающий целые числа​ содержатся данные о​​ дат.​​ числовую клавиатуру, нужно​
4. ​ используются, расположены на​​ числа строк диапазона​​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​A5:A13​​Symbol​​ не работает с​
5. ​ из символьной таблицы,​​Преобразует все буквы текста​​Для языков с двухбайтовыми​
6. ​ в строке ячейки​​ любой последовательности знаков.​​ “base” начинается с​​ импорте таблицы в​​ из диапазона от​ сотрудниках в столбцах​
7. ​Вид исходной таблицы данных:​ нажать на клавишу​ клавиатуре. Смотрите в​F8:F18​

Пример 4: Создаём справочную таблицу символов

​ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($I$2;ПРАВСИМВ((Список);ДЛСТР($I$2)));СТРОКА(Список)-СТРОКА($A$4);НД());””);​5 символам?​или​​ буквами. Тем не​​ используемой на Вашем​ в прописные.​ наборами знаков (например,​ A2, начиная с​ Если требуется найти​ пятого символа слова​​ базу данных.​​ 1 до N,​ ФИО и дата​

1. ​Для заполнения номера дня​ «Num Lk» или​ статье «Где на​
2. ​(10), то в​СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список))))​​Знак * (умножить) между​​Wingdings​ менее, Вы можете​ компьютере.​

​ЗНАЧЕН​ катакана) преобразует полуширинные​​ шестого знака.​​ вопросительный знак или​​ “database”. Можно использовать​​​ где N –​​ рождения. Создать столбец,​​ используем следующую формулу​

​ «Num Lock» (вверху​ клавиатуре кнопка» здесь.​
​ трех ячейках формула​
​)​

​ частями формулы представляет​

office-guru.ru

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть2. Подстановочные знаки

​, измените шрифт в​ использовать функцию​Так же, как и​Преобразует текстовый аргумент в​ (однобайтовые) знаки в​7​ звездочку, введите перед​ функции​Формула, которая изображена выше​ число байтов в​ в котором будет​ (использовать в качестве​ в правой части​Но специальные символы,​

​ вернула значения ошибки​Часть формулы ПОИСК($I$2;ПРАВСИМВ((Список);ДЛСТР($I$2))) определяет:​ условие И (значение​​ столбце, содержащем функцию​​CHAR​

Задача

​ для функции​ число.​ текстовой строке в​=ПОИСК(A4;A3)​ ним тильду (​ПОИСК​ на рисунке сначала​

А. Найти значения, которые начинаются с критерия и содержат определенное количество символов

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

​ #Н/Д.​совпадают ли последние 5​ должно начинаться с​

​CHAR​(СИМВОЛ), чтобы создать​​CODE​​Важно:​ полноширинные (двухбайтовые).​​Начальная позиция строки “маржа”​​~​и​

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

​Чтобы вывести номера строк​ символов​ дрел и иметь​(СИМВОЛ).​ список всех букв​(КОДСИМВ), результат​ Вычисляемые результаты формул и​РУБЛЬ​ (искомая строка в​).​

​ПОИСКБ​ ячейки A4 ряд​ возвращаемой подстроке.​
​ в формате «Иванов​
​A2:A10 – диапазон ячеек​
​ любой символ кодом​
​ таблице.​

​ в горизонтальном диапазоне​​значений из диапазона​​ такую же длину,​Урок подготовлен для Вас​​ алфавита.​​CHAR​
​ некоторые функции листа​Преобразует число в текст,​​ ячейке A4) в​​Если​​для определения положения​​ символов из 10-ти​

​Примечания:​ – 27».​ с текстовым представлением​ в ячейку, нажимаем​Таблица символов​ используйте формулу =ТРАНСП(СТРОКА(F8:F18)).​A5:A13​ как и критерий,​ командой сайта office-guru.ru​Используйте тот же приём​(СИМВОЛ) может быть​ Excel могут несколько​ используя денежный формат​ строке “Доход: маржа”​

Б. Найти значения, которые начинаются со слова дрель или дрели и содержат как минимум 6 букв

​искомый_текст​​ символа или текстовой​​ нулей и в​Функция ПСТР вернет пустую​Вид исходной таблицы:​ дат, из которых​ клавишу «Alt», удерживаем​​Excel​​ Не забудьте нажать​с фразой «дрел?».​​ т.е. 5 букв).​​Источник: http://blog.contextures.com/archives/2011/01/09/30-excel-functions-in-30-days-08-char/​

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

​ результате получаем новый​​ строку, если в​​Для возврата строки с​ будут выделены номера​​ её нажатой и​​расположена на закладке​CTRL+SHIFT+ENTER​ Критерию также будут​ Критерию также будут​Перевел: Антон Андронов​

В. Найти значения, у которых слово дрель находится в середине строки

​64​​ от операционной системы.​​ под управлением Windows​СОВПАД​ выполняется поиск — A3).​ значение ошибки #ЗНАЧ!.​​ текстовой строке, а​​ идентификатор. Каждый из​ качестве аргумента начальная_позиция​ фамилией и текущим​ дней;​ вводим цифры кода​

​ «Вставка» в разделе​.​ соответствовать значения заканчивающиеся​
​ соответствовать такие несуразные​
​Автор: Антон Андронов​
​к номеру символа,​
​ Коды для таблицы​

​ с архитектурой x86​​Проверяет идентичность двух текстовых​​8​Если аргумент​​ затем вернуть текст​​ них теперь в​ было передано число,​ возрастом используем следующую​1 – номер начальной​ символа. Отпускаем клавишу​

Г. Найти значения, которые заканчиваются на слово дрель или дрели

​ «Текст» кнопка «Символ».​​Записав в правиле Условного​​ на фразы дрела,​ значения как дрел5,​Найдем текстовые значения, удовлетворяющие​ чтобы получить буквы​​ символов ASCII (от​​ или x86-64 и​ значений.​

​=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;”объем”)​

​начальная_позиция​ с помощью функций​ любом случае имеет​
​ превышающее количество символов​
​ формулу:​
​ позиции символа извлекаемой​
​ «Alt». Символ появился​

​Для примера, как​​ форматирования для диапазона​ дрел6 и т.п.​​ дрелМ и т.п.​​ заданному пользователем критерию.​​ алфавита.​ 32 до 126)​ компьютерах под управлением​НАЙТИ, НАЙТИБ​Заменяет слово “маржа” словом​

​опущен, то он​
​ПСТР​ не менее 10-ти​ в исходной строке.​Функция ПСТР возвращает часть​ подстроки (первый символ​ в ячейке.​ можно использовать символы,​

excel2.ru

Функция СТРОКА() в MS EXCEL

​A5:A18​СОВЕТ:​ (если они содержатся​ Критерии заданы с​=CHAR(A2+64)​

Синтаксис функции

​ в большинстве своем​​ Windows RT с​

​Ищет вхождения одного текстового​​ “объем”, определяя позицию​ полагается равным 1.​и​

• ​ чисел.​Если в качестве аргумента​ строки до символа​ в исходной строке);​Коды символов​ смотрите, как вставить​формулу =НЕЧЁТ(СТРОКА())=СТРОКА() получим​​О поиске текстовых​​ в списке).​ использованием подстановочных знаков.​

• ​=СИМВОЛ(A2+64)​ соответствуют символам на​ архитектурой ARM. Подробнее​ значения в другом​

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

​ слова “маржа” в​Если аргумент​ПСТРБ​Далее применяется функция ЛЕВСИМВ,​ начальная_позиция было передано​ пробела, позиция которого​2 – номер последней​Excel.​ и использовать символ​ выделение цветом каждой​ значений с учетом​Критерий вводится в ячейку​ Поиск будем осуществлять​Если Вы хотите вводить​​ Вашей клавиатуре. Однако,​​ об этих различиях.​

​ (с учетом регистра).​ ячейке A3 и​начальная_позиция​или заменить его​ которая обрезает каждое​ значение 1, а​ определяется функцией НАЙТИ.​

​ позиции символа извлекаемой​Здесь приведены коды​ “Стрелка”, в статье​ второй строки (см.​​ РЕгиСТра читайте в​​E2​ в диапазоне с​ алфавит при помощи​ символы для более​Вчера в марафоне​​ФИКСИРОВАННЫЙ​​ заменяя этот знак​

​не больше 0​ с помощью функций​ исходное значение до​ аргумент число_знаков определен​ Для нахождения возраста​ подстроки.​​ часто используемых символов.​​ «Символ в Excel​ рисунок выше).​ статье Поиск текстовых​и выглядит так:​

​ повторяющимися значениями. При​ автозаполнения, есть возможность​ высоких номеров (от​30 функций Excel за​​Форматирует число и преобразует​​ и последующие пять​

Использование функции для чрезстрочного выделения

​ или больше, чем​ЗАМЕНИТЬ​​ первых 10-ти чисел​​ числом, которое равно​ сотрудника используется функция​Аналогичным способом выделим номера​В Excel можно​

Формирование списка без повторов

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

excel2.ru

Символ в Excel.

​и​ от начала строки.​ или больше общему​ ДОЛЯГОДА, полученное значение​​ месяца и годы​ ​ установить в ячейке​​Коды символов Excel.​ повторяющиеся значения (диапазон​ Часть3. Поиск с​ случае будут выведены​
​ ожидать, что критерию​ ​ алфавита в пользовательский​​ могут различаться.​мы определяли код​ с заданным числом​ “объем.”​просматриваемого текста​ЗАМЕНИТЬБ​ Для этого во​ числу знаков в​ которой усекается до​ для заполнения соответствующих​ ссылку в виде​Каждый символ имеет​A21:A34​ учетом РЕГИСТРА.​ все значения, в​ будет соответствовать несколько​
​ список.​Чтобы добавить разрыв строки​ символа при помощи​ десятичных знаков.​Доход: объем​, возвращается значение ошибки​. Эти функции показаны​ втором аргументе функции​ исходной строке, функция​ ближайшего меньшего целого,​ столбцов с учетом,​ символа на конкретную​ свой код. Его​), то функция СТРОКА()​Функция СТРОКА(), английский вариант​ которые​ значений. Для их​Скопируйте ячейки с буквами​ в ячейке, можно​
​ функции​ ​ЛЕВСИМВ, ЛЕВБ​​=ПСТР(A3;ПОИСК(” “;A3)+1,4)​ #ЗНАЧ!.​ в примере 1​ ЛЕВСИМВ указано число​ ПСТР вернет всю​ чтобы получить число​ что номер месяца​
​ строку в другой​ можно посмотреть в​ может быть использована​ ROW(), вычисляет номер​начинаются​
​ вывода в отдельный​ алфавита и вставьте​ нажать​CODE​
​Возвращают крайние слева знаки​ ​Возвращает первые четыре знака,​​Аргумент​ данной статьи.​ 10.​
​ строку целиком.​ полных лет. Функция​ начинается с 4-го​ таблице на другом​ таблице символов. Нажимаем​ для формирования списка​ строки, определяемой ссылкой:​
​с текста-критерия (со​
​ диапазон удобно использовать​ их в отдельную​Alt+Enter​(КОДСИМВ), а также​ текстового значения.​ которые следуют за​начальная_позиция​Важно:​Если необходимо сделать так​Если аргумент начальная_позиция был​ ТЕКСТ преобразует полученное​ символа в каждой​ листе. Например, есть​ в таблице символов​ без повторов. Формула​ формула =СТРОКА(B5) вернет​ слова дрел) и​ формулы массива.​ строку как значения.​​. Когда Вы объединяете​ использовали её, чтобы​​ДЛСТР, ДЛИНБ​​ первым пробелом в​можно использовать, чтобы​ ​ чтобы нули были​​ указан числом из​ значение в текстовую​
​ строке, а год​ таблица с общими​ на нужный символ​=ЕСЛИ(ПОИСКПОЗ(A21;$A$21:$A$34;0)=СТРОКА()-СТРОКА($A$20);A21;””)​
​ 5.​длиной как минимум​Пусть Исходный список значений​Выберите эти ячейки. Здесь​ текстовые строки в​ разоблачить скрытые символы.​Возвращают количество знаков в​ строке “Доход: маржа”​ пропустить определенное количество​Эти функции могут быть​ автоматически добавлены не​
​ диапазона отрицательных чисел​ строку.​ – с 7-го.​ данными. Нам нужно​
​ и в строке​возвращает значение если​
​СТРОКА​6 символов.​
​ (например, перечень инструментов)​
​ главное не перепутать​ формуле, нужно использовать​ В восьмой день​ текстовой строке.​ (ячейка A3).​ знаков. Допустим, что​ доступны не на​ с правой стороны,​ или 0 (нулем),​Для соединения (конкатенации) полученных​ Используем следующие формулы:​ узнать конкретную информацию​ «Код знака» виден​ оно встречается первый​
​(ссылка)​Для создания списка, содержащего​ находится в диапазоне​ и выбрать ячейки​ символ разрыва строки.​ нашего марафона мы​СТРОЧН​
​марж​ функцию​ всех языках.​ а с левой​ функция ПСТР вернет​ строк используются символы​=ПСТР(A2:A10;4;2)​ по какому-то пункту​ код этого символа.​
​ раз.​ ​Ссылка​​ найденные значения, воспользуемся​A5:A13.​
​ именно со значениями,​=C2&CHAR(10)&C3​ изучим злого близнеца​Преобразует все буквы текста​=ПОИСК(“”””;A5)​ПОИСК​Функция ПОИСКБ отсчитывает по​ (например, так: 0001234567).​ код ошибки #ЗНАЧ!.​ «&». В результате​=ПСТР(A2:A10;7;4)​ (контактные данные по​ Ставим в строке​Кроме цифр и​  — ячейка или интервал​ формулой массива:​См. Файл примера.​ а не с​=C2&СИМВОЛ(10)&C3​ функции​ в строчные.​Позиция первой двойной кавычки​нужно использовать для​ два байта на​ Тогда следует немного​Если аргумент число_знаков задан​
​ вычислений получим:​Вид заполненной таблицы данных:​ человеку, т.д.). Нажимаем​ «из» – «Юникод»,​
​ букв, можно применять​ ячеек, для которых​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​Выведем в отдельный диапазон​ функцией​

excel-office.ru

Пример функция ПСТР для разделения текста на части в Excel

​После того как Вы​CODE​ПСТР, ПСТРБ​ (“) в ячейке​ работы с текстовой​ каждый символ, только​ изменить формулу, а​ отрицательным числом, результатом​

Примеры использования функции ПСТР в Excel

​Функция имеет следующую синтаксическую​Таким образом нам удалось​ на ссылку и​ если не срабатывает,​ в Excel определенные​ определяется номер строки.​ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($E$2;Список)=1;СТРОКА(Список)-СТРОКА($A$4);НД());””);​ значения, которые удовлетворяют​CHAR​ создадите формулу, отформатируйте​(КОДСИМВ) – функцию​Возвращают заданное число знаков​ A5.​ строкой “МДС0093.МужскаяОдежда”. Чтобы​ если языком по​ вместо функции ЛЕВСИМВ​ выполнения функции ПСТР​ запись:​

Как разделить текст на несколько ячеек по столбцам в Excel?

​ разрезать на части​ Excel переходит в​ то – “Кирилица​ символы. Есть​Если ссылка опущена, то​СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список))))​ критерию, причем критерий​(СИМВОЛ).​ ячейку командой​

​CHAR​

​ из строки текста,​5​ найти первое вхождение​ умолчанию является язык​

​ использовать функцию ПРАВСИМВ:​

• ​ будет код ошибки​=ПСТР(текст;начальная_позиция;число_знаков)​ текст в ячейках​ другую таблицу на​ (дес.). Обязательно проверяйте,​
• ​в​ функция СТРОКА() вернет​)​ задан с использованием​
• ​Нажмите кнопку​Wrap Text​(СИМВОЛ). Возможно, функция​

​ начиная с указанной​=ПСТР(A5;ПОИСК(“”””;A5)+1;ПОИСК(“”””;A5;ПОИСК(“”””;A5)+1)-ПОИСК(“”””;A5)-1)​ “М” в описательной​ с поддержкой БДЦС.​Как видено на рисунке​ #ЗНАЧ!.​Описание аргументов:​ столбца A. Удалось​ другом листе на​ что написано и​

​Excel таблица символов​

​ номер строки в​

​Часть формулы ПОИСК($E$2;Список)=1 определяет:​

​ подстановочных знаков (*,​Office​(Перенести текст), чтобы​CHAR​ позиции.​Возвращает из ячейки A5​ части текстовой строки,​ В противном случае​ на этот раз,​

​Очень часто данные набранные​

Как вырезать часть текста ячейки в Excel?

​текст – обязательный для​ отдельно каждую дату​ строку именно этого​ ставьте нужное в​. Эти символы не​ которую введена сама​начинается​ ?). Рассмотрим различные​(Офис) на Ленте​ увидеть перенос строки​

​(СИМВОЛ) не такой​

​ЧЗНАЧ​ только текст, заключенный​

​ задайте для аргумента​

​ функция ПОИСКБ работает​ используя символ амперсант,​ в электронных таблицах​ заполнения аргумент, принимающий​ разделить на несколько​ человека, т.д. Для​ строках «Шрифт» и​ просто написаны как​

​ формула. Т.е. если​ли значение из​ варианты поиска.​

​ Excel, затем выберите​

​ вместо таинственного символа​ уж и злой​Преобразует текст в число​ в двойные кавычки.​начальная_позиция​ так же, как​ мы добавили по​ Excel используются для​ ссылку на ячейку​ ячеек по столбцам:​ ссылки можно использовать​ «Набор» в таблице​ буквы, а выполняют​ формула =СТРОКА() введена​ диапазона​Для удобства написания формул​Excel Options​ между строками.​

​ близнец, просто с​

Как посчитать возраст по дате рождения в Excel?

​ независимо от языкового​босс​значение 8, чтобы​ функция ПОИСК, и​ 10 нулей слева​ заполнения файлов баз​ с текстом или​ день, месяц и​ любые символы. Подробнее​ символа.​

​ определенную функцию.​

​ в ячейку​A5:A13​ создадим Именованный диапазон​(Параметры Excel). Если​

​Быстро! Какая 19-я буква​ противоположным характером.​ стандарта.​Чтобы просмотреть более подробные​ поиск не выполнялся​ отсчитывает по одному​ к исходному значению​ данных. Файлы такого​ текстовую строку, заключенную​ год.​ о такой функции​Набор символов бывает​Что в​B5​

​с фразы «дрел??».​ Список для диапазона​ у Вас Excel​ алфавита? С функцией​

Особенности использования функции ПСТР в Excel

​Итак, посмотрим подробнее инструкцию​PHONETIC​

​ сведения о функции,​

​ в той части​

• ​ байту на каждый​ ячейки A4. После​ типа часто требуют​ в кавычки, из​​ Excel читайте в​ «​Excel означает символ​, то результат будет​ Критерию также будут​
• ​A5:A13​ 2010, то выберите​CHAR​ к функции​Извлекает фонетические (фуригана) знаки​ щелкните ее название​ текста, которая является​ символ.​ чего обрезали каждый​ соблюдения правил заполнения.​ которой будет извлечена​Пример 2. В столбце​ статье “Гиперссылка в​Надстрочный/подстрочный​. Например, если начинаем​ 5.​
• ​ соответствовать значения начинающиеся​.​Options​(СИМВОЛ) Вы выясните​CHAR​ из текстовой строки.​ в первом столбце.​ серийным номером (в​К языкам, поддерживающим БДЦС,​ идентификатор оставляя только​ Например, чтобы определенные​ подстрока определенной длины​ таблицы хранятся текстовые​ Excel на другой​». Это значит, что​ вводить в ячейку​В аргументе «ссылка» нельзя​ с фраз дрелью,​Перечень найденных значений будем​(Настройки).​ это без перечисления​

​(СИМВОЛ) и примеры​ПРОПНАЧ​

​Примечание:​

​ данном случае —​

• ​ относятся японский, китайский​ лишь по 10​ поля данных имели​ начиная с указанной​ записи с наименованием​ лист”.​ символ пишется верху​ данные со знака​ указывать несколько областей,​

​ дрел23 и т.п.​

1. ​ помещать в отдельный​В списке категорий нажмите​ всего алфавита в​ ее использования, а​Преобразует первую букву в​ Маркер версии обозначает версию​
2. ​ “МДС0093”). Функция​ (упрощенное письмо), китайский​ числовых знаков справой​ определенную длину количества​ позиции первого символа;​ и маркой товаров.​О других сочетаниях​ цифры, слова (например,​ «равно» (=), то​ т.е. запись =СТРОКА(A1:A2;B2:B3)​
3. ​Критерий вводится в ячейку​ столбец.​Popular​ уме.​ если у Вас​ каждом слове текста​
4. ​ Excel, в которой​ПОИСК​ (традиционное письмо) и​ стороны номеров. Мы​ символов. Поэтому часто​

exceltable.com

Пример формулы для добавления символов в строке ячейки Excel

​начальная_позиция – обязательный аргумент,​ Разделить имеющиеся строки​ клавиш читайте в​ градус)​ этот знак говорит​ не допустима.​G2​Найдем все названия инструментов,​(Основные). Если Excel​Первым делом, нужно при​ есть, что добавить​ в прописную.​ она впервые появилась.​начинает поиск с​ корейский.​ добавили недостающие символы​ техника заполнения полей​ принимающий целые числа​ на подстроки с​

Автоматическое добавление количества символов в ячейке Excel

​ статье «Горячие клавиши​. Или внизу​ Excel, что вводится​Так как функция СТРОКА()​и выглядит так:​ которые​ 2010, то​ помощи функции​ к инструкции или​ЗАМЕНИТЬ, ЗАМЕНИТЬБ​ В более ранних​ восьмого символа, находит​ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])​ сначала строки необходимого​ данных числовыми значениями​ из диапазона от​ наименованием и маркой​ Excel» тут.​ цифры, числа -​ формула, по которой​ в качестве аргумента​ «?дрель?». В этом​

​начинаются​Advanced​CODE​ примерам – пишите​Заменяют знаки в тексте.​ версиях эта функция​

​ знак, указанный в​ПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])​ нам количества. Так​ требует ввода дополнительных​ 1 до N,​ соответственно и записать​Как установить проверку​Найти в таблице​

​ нужно посчитать. То​

​ позволяет ввести ссылку​ случае будут выведены​с фразы дрел​(Дополнительно).​(КОДСИМВ) выяснить код​ в комментариях.​ПОВТОР​ отсутствует. Например, маркер​ аргументе​Аргументы функций ПОИСК и​ работает функция ПРАВСИМВ,​ нулей, чтобы во​

​ где N –​ полученные значения в​ вводимых данных в​ символ можно, введя​ же самое со​ на диапазон ячеек,​ все значения,​ и​Найдите и нажмите кнопку​

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

​ Excel, смотрите в​ код в строку​ знаками «Сложение» и​ то следует ожидать,​содержащие​длина строки​Edit Custom Lists​ –​CHAR​ раз.​ что данная функция​, в следующей позиции,​Искомый_текст​ предыдущей функции ЛЕВСИМВ.​ одинаковое число символов​ которой требуется извлечь​Вид таблицы данных:​ статье «Проверка данных​

exceltable.com

​ «Код знака».​

• Что делает функция ПОИСК?
• Синтаксис
• Форматирование
• Поиск символа в ячейке
• Извлечь первое слово
• Другие примеры использования
• Функция ПОИСК в формуле массива

Что делает функция ПОИСК?

Эта функция аналогична функции НАЙТИ и так же ищет подстроку в строке. Когда искомое найдено, отображается его позиция в тексте в виде числа.

Отличие от функции НАЙТИ в том, что ПОИСК не принимает в расчет регистр текста. Как искомого, так и того, в котором мы ищем. Также она поддерживает подстановочные операторы.

У обеих функций есть процедура-аналог Найти и Заменить – как у процедуры, у нее есть свои преимущества и недостатки.

Синтаксис

=ПОИСК(ИскомыйТекст;СтрокаВКоторойИщем;[СтартоваяПозиция])

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

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

Если искомое не найдено в тексте, функция возвращает ошибку #ЗНАЧ.

Форматирование

При поиске дат функция ПОИСК, как и все текстовые функции, воспринимает их как числа, поэтому для корректного поиска может понадобиться функция ТЕКСТ.

При этом логические значения ИСТИНА и ЛОЖЬ конвертируются в текст, соответствующий их написанию.

Поиск символа в ячейке

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

Логика проста – если поиск позиции символа не возвращает ошибку, значит, символ в ячейке присутствует:

=НЕ(ЕОШ(ПОИСК(паттерн;текст)))

Извлечь первое слово

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

Таблица выше была использована для извлечения имени из строки с именем и фамилией.

1. ПОИСК возвращает позицию пробела между именем и фамилией.
2. Длина имени вычисляется как позиция пробела минус 1.
3. Функция ЛЕВСИМВ извлекает имя на основе его длины.

Другие примеры использования

Найти первую цифру в ячейке:

=МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1};A1);1000))

Найти первую цифру в ячейке и вернуть все, что перед ней:

=ЛЕВСИМВ(A1,МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1};A1);1000))-1)

Узнать, содержит ли ячейка латиницу. Формула вернет «ИСТИНА» или «ЛОЖЬ»:

=СЧЁТ(ПОИСК({"a":"b":"c":"d":"e":"f":"g":"h":"i":"j":"k":"l":"m":"n":"o":"p":"q":"r":"s":"t":"u":"v":"w":"x":"y":"z"};A1))>0

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

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

Функция ПОИСК в формуле массива

Примеры выше, где буквы перечислены явно в строковом массиве, занимает довольно много места. Буквы при этом идут подряд, что наводит на мысль, что их можно как-то иначе выразить как диапазон.

И действительно, это возможно с помощью комбинации с функциями СТРОКА и ПОИСК:

{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1))>0}

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

Что происходит в этой формуле?

• Функция СТРОКА с численным аргументом “65:90” возвращает массив чисел с 65 по 90 включительно. Как раз в этом диапазоне в таблице ASCII находятся все символы латиницы;
• Функция СИМВОЛ возвращает для каждого числового значения в этом массиве его символ, таким образом создавая массив латинских символов;
• Функция ПОИСК производит поиск каждого из этих символов в строке и возвращает либо число, либо ошибку, таким образом создавая массив чисел и ошибок
• Функция СЧЁТ считает числовые значения в полученном массиве. Если результат больше нуля, значит, хотя бы один символ латиницы был найден. Если нет (все поиски вернули ошибку), значит, не был

Аналогичная формула для кириллицы:

{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1))>0}

Подробнее о поиске и извлечении кириллицы и латиницы в Excel можно почитать тут:

Найти латиницу или кириллицу в тексте

Есть еще множество комбинаций функции ПОИСК с другими функциями Excel, смотрите разделы:
Функция ИЛИ
Функция И
Функция ЗНАЧЕН
Удалить первое слово в ячейке Excel

---

Смотрите также по теме:

ОБНАРУЖИТЬ (функционал надстройки !SEMTools)

Регулярные выражения в Excel

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

Найти слова в ячейках

Найти числа в текстовых ячейках

Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Основная проблема поиска латинских букв, что их в два с половиной раза больше, чем цифр. Следовательно, нужно задавать программе цикл, состоящий из 26 итераций, что может быть довольно напряжно. Но если использовать формулу массива, состоящую из приведенных выше функций СЧЕТ и ПОИСК, то эта затея уже и не кажется настолько сложной: =СЧЁТ(ПОИСК({«a»:»b»:»c»:»d»:»e»:»f»:»g»:»h»:»i»:»j»:»k»:»l»:»m»:»n»:»o»:»p»:»q»:»r»:»s»:»t»:»u»:»v»:»w»:»x»:»y»:»z»};A1))>0. Эта формула вполне подходит для большинства ситуаций. Например, если нет возможности установить соответствующие макросы, которые бы смогли делать это проще и быстрее.

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

Функция ПОИСК не дает возможности учитывать регистр при поиске символов. Чтобы это сделать, надо воспользоваться оператором НАЙТИ, который выполняет те же операции, имеет такие же самые аргументы, только уже учитывает регистр. Еще один способ – сделать описанную выше формулу формулой массива. В таком случае она будет иметь следующий вид:{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1))>0}.

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

Если же нужно найти кириллицу, последовательность действий аналогичная, только в качестве диапазона поиска нужно задавать всю последовательность кириллических символов. =СЧЁТ(ПОИСК({«а»:»б»:»в»:»г»:»д»:»е»:»ё»:»ж»:»з»:»и»:»й»:»к»:»л»:»м»:»н»:»о»:»п»:»р»:»с»:»т»:»у»:»ф»:»х»:»ц»:»ч»:»ш»:»щ»:»ъ»:»ы»:»ь»:»э»:»ю»:»я»};A1))>0. Также можно юзать функцию СИМВОЛ, чтобы сделать это. {=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1))>0}

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

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

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

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

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

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

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

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

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

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

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

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

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

Первая регулярная функция – REGEXMATCH, которая умеет проверять, похож ли этот паттерн на тот, который был в другой ячейке. Синтаксис: =REGEXMATCH(«текст»;»RegEx-паттерн для поиска»). Эта функция возвращает одно из двух значений: истинное или ложное. Что именно – зависит от того, действительно ли наблюдается соответствие или нет. Вторая функция – =REGEXEXTRACT(«текст»;»RegEx-паттерн для поиска») Она позволяет извлечь нужные символы из строки.

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

Ну и наконец, нужно воспользоваться этой формулой для того, чтобы заменить текст: =REGEXREPLACE(«текст»;»RegEx-паттерн для поиска»;»текст, которым заменяем найденное»).

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

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

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

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

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

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

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

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

Функции ЛЕВСИМВ, ПРАВСИМВ и ПСТР прекрасно применяются для разделения строк на слова или фрагменты текстов, но при условии, что вам заранее известны позиции символов с которых будет разделение. Что же делать если заранее вам не известно где в текстовой строке находится тот символ, начиная с которого нужно вырезать фрагмент текста?

Пример формулы НАЙТИ, ДЛСТР и ПРАВСИМВ в Excel

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

• PTR-422
• CORPO-6755
• SVCCALL-56532

Функция ЛЕВСИМВ нам сразу не подходит, так как необходимо получить последнюю часть каждого кода. Функция ПРАВСИМВ так же не справиться с данной задачей, ведь в ее аргументах следует указать точное количество символов возвращаемого текста из всех разных кодов разной длины. Если в аргументе будет указано фиксированное числовое значение, то для некоторых кодов сработает, а для большинства будет слишком много или мало количество символов, возвращаемых функцией ПРАВСИМВ.

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

Для реализации данной задачи следует использовать формулу с комбинацией функций ПРАВСИМВ, ДЛСТР и НАЙТИ:

Благодаря функции НАЙТИ можно автоматически определять позицию в текстовой строке для указанного символа в ее аргументах. А после использовать номер позиции в следующих операциях, например, при автоматической генерации значений для второго аргумента функции ПРАВСИМВ. Реализуется генерация путем определения необходимого числа при вычитании от длины строки, которую возвращает функция ДЛСТР номера позиции символа – «-».



Пример использования НАЙТИ и ПСТР в формуле Excel

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

Как видно на рисунке формула сначала ищет номер позиции для символа с помощь функции НАЙТИ. А после найденный номер позиции использует в своих аргументах функция ПСТР.

Функция НАЙТИ требует заполнить минимум 2 из 3-х аргументов:

1. Искомый_текст – здесь необходимо указать текст, который следует найти и получить его порядковый номер (позицию) в исходной текстовой строке.
2. Просматриваемый_текст – тут указываем ссылку на ячейку с исходной строкой, которая содержит искомый символ или текст.
3. Нач_позиция – это не обязательный аргумент. Здесь можно указать номер позиции символа в строке, с которого следует начинать поиск. Если строка содержит более одного найденного искомого символа, то с помощью данного необязательного аргумента можно указать номер символа с которого будет просматриваться остальная часть строки. Если он не указан в этом аргументе, то по умолчанию он равен = 1, то есть с первого, а значит целая строка.

На пример, в примере функция находит первый дефис в строке «PWR-16-Small». В результате своего вычисления она по умолчанию возвращает число 4. Так как первый дефис в и сходной строке находится на четвертой позиции.

Динамические формулы с использованием функции НАЙТИ

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

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

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

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

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

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

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

Здесь мы использовали автоматический поиск первого дефиса. Номер позиции послужил третьим необязательным опциональным аргументом функции НАЙТИ для автоматического поиска каждого второго дефиса в каждом коде товара. Далее используя функцию ДЛСТР, мы определяем длину исходной строки и вычитаем от нее число позиции второго символа. Другими словами, от длины кода вычитаем количество символов до второго дефиса (включительно с ним, о чем свидетельствует сложение +1). Таким образом мы динамически определяем второй аргумент для функции ПРАВСИМВ чтобы вырезать разной величины фрагмент текста из строк. К том уже все строки с разной длиной, да еще и в разном месте находится второй дефис. Но умная формула справилась полностью в автоматическом режиме.