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

Microsoft Excel

трюки • приёмы • решения

Как в Excel вводить специальные символы

В Excel есть окно Символ, которое применяется для поиска и вставки специальных символов в ячейку (рис. 66.1). Вы можете открыть это окно, выбрав команду Вставка ► Символы ► Символ.

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

Рис. 66.1. Символы из категории технические знаки шрифта Arial Unicode MS

Если вы вставили символ из определенного шрифта, то Excel продолжит отображать тот же символ независимо оттого, какой шрифт применился к ячейке. Для большого набора символов используйте шрифт Arial Unicode MS.

Если вы используете какой-либо символ часто, то можете захотеть сделать его более доступным, например это может быть КНС. В Excel это выполняется с помощью функции Автозамена. Исполнив следующие инструкции, вы сделаете нужный вам символ (для нашего примера он выбран на рис. 66.1) легкодоступным.

1. Выберите пустую ячейку.
2. Выполните команду Вставка ► Символы ► Символ и используйте диалоговое окно. Символ для поиска символов, которые вы хотите использовать. В нашем примере код символа равен 2318, а сам символ относится к категории технические знаки шрифта Arial Unicode MS.
3. Вставьте этот символ в ячейку, нажав кнопку Вставить.
4. Нажмите Закрыть, чтобы закрыть диалоговое окно Символ.
5. Нажмите Ctrl+C, чтобы скопировать символ в активной ячейке.
6. Выберите Файл ► Параметры, чтобы открыть окно Параметры Excel, перейдите в раздел Правописание, а затем нажмите кнопку Параметры автозамены для вызова диалогового окна Автозамена (или просто нажмите Alt+TA).
7. В окне Автозамена выберите одноименную вкладку.
8. В поле заменять введите последовательность символов, например (р).
9. Перейдите к полю на и нажмите Ctrl+V, чтобы вставить специальный символ.
10. Нажмите 0К для закрытия окна Автозамена.

После выполнения этих шагов Excel будет заменять символ, когда вы введете (р). Выбирая строку замены, укажите последовательность символов, которые вы обычно не печатаете. В противном случае вы можете обнаружить, что Excel делает замену там, где она вам не нужна. Помните, что всегда можете нажать Ctrl+Z, чтобы отменить автозамену. Для получения более подробной информации об использовании автозамены см. статью «Настройка и совместное использование автозамены в Excel».

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

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

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

Описание

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

Замечание

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

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

Если значение аргумента искомый_текст не найдено, #VALUE! возвращено значение ошибки.

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

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

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

Примеры

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

Как заменить/удалить/найти звездочку?

Наверное кто-то уже сталкивался с подобной ситуацией — на листе в ячейках записаны слова/предложения, в которых имеется знак — * — звездочка. Иногда его нужно либо найти, либо заменить на другой символ, либо просто удалить. Например, строку вида «496*120*45» надо преобразовать в такой: «496x120x45» . И вот тут как раз возникает проблема: символ звездочки(как и знак вопроса) является служебным символом — он заменяет собой группу любых символов(а знак вопроса — один любой символ). И при попытке заменить только звездочку Excel заменят ВСЕ данные в ячейке. Сейчас пока разговор про стандартную замену — ( Excel 2003 : Правка —Заменить; Excel 2007-2010 : вкладка Главная —Найти и выделить —Заменить) — Ctrl+H. Неужели ничего нельзя сделать? Можно. Разработчики предусмотрели такой вариант. Просто перед служебным символом необходимо поставить другой служебный знак, который сообщает Excel, что следующий за ним символ не выполняет свои служебные функции, а выступает в роли обычного символа. Знак этот называется Тильда. Вот такой —

. На обычной клавиатуре этот знак обычно расположен на кнопке вместе с буквой Ё.

Таким образом в поле Найти поискового окошка должен получиться такой текст — «

* «(в поле Заменить на указывается символ/ы, которым требуется заменить звездочку. Если звездочку надо удалить — поле оставляем пустым). И не стоит забывать про дополнительные параметры замены(они раскрываются при нажатии кнопки «Параметры» поискового окна). А именно — необходимо поставить/снять галочку у параметра Ячейка целиком. В рассматриваемом случае эту галочку необходимо снять.

Если же надо найти саму Тильду, то тут все также — просто указываете две тильды:

По тому же принципу этот символ используется и в формулах, которые работают с служебными символами (ВПР, СЧЁТЕСЛИ, СУММЕЛИ и т.п.)

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

Есть аналогичная ей функция — ПОИСК. В этой функции обязательно применять тот же принцип, что и во всех вышеперечисленных — т.е. явно указывать Excel, что мы ищем спец.символ:
=ПОИСК(«

И к слову сказать, символы звездочки и вопр.знака так же используются формулами ВПР (VLOOKUP) , ГПР (HLOOKUP) и ПОИСКПОЗ (MATCH) . При этом самый неприятный момент здесь заключается в том, что звездочка, вопросительный знак или сама тильда могут быть внутри текста в искомом массиве данных:

и как видно в этом случае данные с тильдой не могут быть найдены. Чтобы найти их через ВПР(и прочие схожие функции) необходимо заменить тильду на две. Я бы советовал делать это так(на примере формул из таблицы выше):
=ВПР(ПОДСТАВИТЬ( F2 ;»

«); $A$2:$C$7 ;3;0)
=VLOOKUP(SUBSTITUTE(F2,»

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

почему ВПР так поступает лучше прочитать здесь: Как найти значение в другой таблице или сила ВПР. А в этой статье я лишь приведу формулу, как избежать таких проблем. Так же как и с тильдой используем функцию ПОДСТАВИТЬ (SUBSTITUTE) , но теперь подставляем тильду к звездочке:
=ВПР(ПОДСТАВИТЬ( D2 ;»*»;»

Статья помогла? Поделись ссылкой с друзьями!

Примеры использования функции НАЙТИ в Excel формулах

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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