Excel найти и заменить

Замена нескольких значений на несколько

Эта задача более сложная, чем замена на одно значение. Как ни странно, функция «ЗАМЕНИТЬ» здесь не подходит — она требует явного указания позиции заменяемого текста. Зато может помочь функция «ПОДСТАВИТЬ».

Массовая замена с помощью функции «ПОДСТАВИТЬ»

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

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

Но у решения есть и свои недостатки:

• Функция ПОДСТАВИТЬ регистрозависимая, что заставляет при замене одного символа использовать два его варианта — в верхнем и нижнем регистрах. Хотя, в некоторых случаях, как пример на картинке выше, это и преимущество.
• максимум 64 замены — хоть и много, но все же ограничение.
• формально процедура замены таким способом будет происходить массово и моментально, однако, длительность написания таких формул сводит на нет это преимущество. За исключением случаев, когда они будут использоваться многократно.

Файл-шаблон с формулой множественной замены

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

Формула в файле-шаблоне для множественной замены на примере транслитерации

А вот и она сама (тройной клик по любой части текста = выделить всю формулу). Обращается к ячейке D1, делая 64 замены по правилам, указанным в ячейках A1-B64. При этом в столбцах можно удалять значения — это не нарушит ее работу.

Как работает инструмент замены символов в Excel

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

Рассмотрим оба варианта.

Вариант 1: Поиск и замена

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

1. Во вкладке с инструментами “Главная” нажмите по инструменту “Заменить”. Он находится в группе инструментов “Найти и выделить”.

Запустится окошко “Найти и заменить”. Там переключитесь во вкладку “Заменить”. Введите в поле “Найти” нужный символ или их комбинацию. В поле “Заменить на” укажите символы или комбинацию, на которые нужно выполнить замену. Нажмите кнопку “Найти далее”. Можете также нажать кнопку “Заменить все” в том случае, если точно уверены, что не сделаете лишних замен.</li>Если вы нажали “Найти далее” у вас будет показан первый же найденный результат в документе. Если его требуется заменить, то нажмите соответствующую кнопку, если же нет, то жмите “Найти далее” для перехода к следующему результату.</li>

Проделывайте эти действия, пока не замените все, что необходимо.</li></ol>

Также этот вариант можно реализовать немного иначе:

1. Введите искомую комбинацию символов и комбинацию для замены. Нажмите “Найти все”.
2. Все найденные символы будут отображены в виде перечня в нижней части окошка. Там будет указана на каком листе они встречаются и в какой ячейке. Выделите ту комбинацию, которую нужно заменить. Нажмите кнопку “Заменить” для замены на ранее указанную комбинацию.

</ol>

Вариант 2: Автоматическая замена

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

1. Вызовите окошко поиска и замены символов по аналогии с предыдущей инструкцией.
2. Пропишите в поля “Найти” и “Заменить на” соответствующие значения.
3. Нажмите кнопку “Заменить все”.
4. Обычно процедура выполняется моментально, но если в документе много заменяемых элементов, то процесс может занять пару секунд. По завершении вы получите оповещение об успешной замене.

Настройка дополнительных параметров замены

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

1. Находясь в окошке поиска и замены нажмите по кнопке “Параметры” для перехода к расширенным настройкам.
2. У вас появятся дополнительные настраиваемые параметры, которые должны учитываться при поиске и замене. Например, можно учитывать регистр и/или проводить замены всех символов на ячейке целиком, а не только тех, что были указаны в “Найти”. Для этого установите галочки напротив соответствующих параметров.
3. Дополнительно можно задать формат искомых символов. Например, сделать так, чтобы программа искала совпадения только в ячейках с денежным форматом, а остальные игнорировала. Для указания формата нажмите по соответствующей кнопки напротив строки ввода.

Также можно настроить форматирование ячеек после замены. Для этого тоже нажмите на кнопку “Формат”, но только на ту, что расположена напротив строки “Заменить на”.</li>Настройки формата для изменяемых ячеек можно экспортировать из уже имеющихся. Для этого кликните правой кнопкой мыши по кнопке “Формат” и выберите в контекстном меню вариант “Выбрать формат из ячейки”.</li>Укажите ячейку, из которой будут выбраны настройки форматирования.</li></ol>

Выполнить автоматическую замену символов в табличном редакторе Excel совершенно несложно. Это можно сделать двумя представленными способами, а если предложенного функционала недостаточно, то его можно немного расширить, нажав по кнопке “Параметры”.

• https://exceltable.com/funkcii-excel/primer-funkcii-zamenit
• https://ru.extendoffice.com/excel/functions/excel-substitute-function.html
• https://office-guru.ru/excel/poisk-i-zamena-v-excel-15.html
• https://www.planetaexcel.ru/techniques/7/2775/
• https://public-pc.com/instrument-zameny-simvolov-v-tablichnom-redaktore-excel/

Функции замены в Экселе: ПОДСТАВИТЬ, ЗАМЕНА

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

1. Функция ПОДСТАВИТЬ(Исходный текст; Что искать; На что заменить;).

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

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

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

Примеры применения функции ПОДСТАВИТЬ

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

1. Функция ЗАМЕНИТЬ(Исходный текст; Начальная позиция; Количество знаков; Новый текст)

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

• Исходный текст – строка или ссылка, в которой нужно произвести замену
• Начальная позиция – порядковый номер первого символа для замены
• Количество знаков – количество символов, которые нужно заменить, начиная с начальной позиции
• Новый текст – строка, которую нужно вставить вместо исходной

Например, запишем формулу: =ЗАМЕНИТЬ(A1;79;4;«2016»)

Она заменит в тексте ячейки А1 символы №79-82 (4 шт) на строку «2016».

Функция ЗАМЕНИТЬ в Эксель

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

В следующем посте мы рассмотрим преобразование числа в текст и наоборот. А пока жду ваших вопросов и комментариев!

Инструменты поиска

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

В первую очередь необходимо указать место, которое можно задать в категории «Искать», в зависимости от указанного диапазона поиск может производиться как на определённом листе, так и во всей книге.

Кроме всего прочего, в разделе «Просматривать» можно указать инструменту приложения производить просмотр документа исключительно по строкам или столбцам.

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

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

Юзер должен быть готовым к тому, что посредством поиска будет просмотрен весь указанный диапазон ячеек, программа последовательно найдёт и выделит каждое совпадение. Просмотреть всё найденное можно поэтапно, передвигаясь по ячейкам с нужным содержимым за один клик. «Найти всё» позволит отобразить в документе список, в котором содержатся адреса абсолютно всех совпадений.

Замена содержимого ячейки в Excel

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

1. На вкладке Главная нажмите команду Найти и выделить, а затем из раскрывающегося списка выберите пункт Заменить.
2. Появится диалоговое окно Найти и заменить. Введите текст, который Вы ищете в поле Найти.
3. Введите текст, на который требуется заменить найденный, в поле Заменить на. А затем нажмите Найти далее.
4. Если значение будет найдено, то содержащая его ячейка будет выделена.
5. Посмотрите на текст и убедитесь, что Вы согласны заменить его.
6. Если согласны, тогда выберите одну из опций замены:
   • Заменить: исправляет по одному значению зараз.
   • Заменить все: исправляет все варианты искомого текста в книге. В нашем примере мы воспользуемся этой опцией для экономии времени.
7. Появится диалоговое окно, подтверждающее количество замен, которые будут сделаны. Нажмите ОК для продолжения.
8. Содержимое ячеек будет заменено.
9. Закончив, нажмите Закрыть, чтобы выйти из диалогового окна Найти и заменить.

7696013.04.2017 Скачать пример

Замена одного текста на другой внутри заданной текстовой строки — весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE). Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Её синтаксис таков:

=ПОДСТАВИТЬ(Ячейка; Старый_текст; Новый_текст; Номер_вхождения)

где

• Ячейка — ячейка с текстом, где производится замена
• Старый_текст— текст, который надо найти и заменить
• Новый_текст— текст, на который заменяем
• Номер_вхождения— необязательный аргумент, задающий номер вхождения старого текста на замену

Обратите внимание, что:

• Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 — обе «Маши» заменены на «Олю»).
• Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая «Маша» заменена на «Олю»).
• Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. «маша» написана с маленькой буквы)

Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl+H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM). Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

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

Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:

Если предполагается, что в ячейке могут находиться и лишние пробелы, то формула будет чуть посложнее, но идея — та же.

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

Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:

У нее простая логика:

1. заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
2. ищем позицию символа # функцией НАЙТИ (FIND)
3. вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)

Ссылки по теме

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

Поиск нестрогого соответствия символов

Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах. Под джокерами могут скрываться различные символы, и Excel видит лишь их относительное расположение в поисковой фразе. Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Есть еще один джокер – знак «?». Под ним может скрываться только один неизвестный символ. К примеру, указав для поиска критерий 1?6, Excel найдет все ячейки содержащие последовательность 106, 116, 126, 136 и т.д. А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).

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

Как найти и заменить цвет заливки в Excel?

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

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

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

1. Выберите ячейки диапазона, которые вы хотите найти, и замените цвет заливки.

2, Нажмите Ctrl + H для открытия Найти и заменить диалоговое окно, см. снимок экрана:

3. Затем нажмите Опции >> кнопку, чтобы развернуть Найти и заменить диалоговое окно, в развернутом диалоговом окне щелкните Формат Кроме того, Найти то, что раздел и выберите Выбрать формат из ячейки из выпадающего списка см. снимок экрана:

4. Затем щелкните, чтобы выбрать одну ячейку, содержащую цвет, который вы хотите заменить, после выбора цвета ячейки, затем щелкните Формат Кроме того, Заменить , и выберите Формат из выпадающего списка, см. снимок экрана:

5. В выскочившем Заменить формат диалоговом окне выберите один понравившийся цвет под Заполнять вкладку, см. снимок экрана:

6. Затем нажмите OK вернуться к Найти и заменить диалоговое окно, нажмите Заменить все кнопку, чтобы заменить цвет сразу, и появится окно с подсказкой, чтобы напомнить вам количество замен, см. снимок экрана:

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

Найти и заменить цвет заливки с помощью Kutools for Excel

Если вас интересуют другие решения для этой задачи, я могу представить вам удобный инструмент — Kutools for Excel, С его Выбрать ячейки с форматом Утилита, вы можете быстро выделить все ячейки с одинаковым форматированием с определенной ячейкой.

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней.

Перейти к загрузкеБесплатная пробная версия 30 днейпокупкаPayPal / MyCommerce

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон данных, который вы хотите использовать.

2. Нажмите Kutools > Выберите > Выбрать ячейки с форматом, см. снимок экрана:

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

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

5. Затем нажмите OK, все атрибуты выбранной ячейки были перечислены в диалоговом окне, и только отметьте Цвет фона под Заполнять вариант, см. снимок экрана:

6, Затем нажмите Ok, сразу же выбираются все ячейки, заполненные этим цветом, затем щелкните Главная > Цвет заливкии выберите один цвет, который вы хотите, и цвет выбранных ячеек был заменен, как показано на следующем снимке экрана:

Демо: найти и заменить цвет заливки с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Инструмент найти и заменить в Excel и Word

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

Для начала, разберемся как вызвать эту функцию и как ее настроить.

Найти и заменить в Excel. Настройка

Для начала выделите диапазон ячеек, с которым нужно поработать (если выделена одна ячейка, Excel проводит поиск во всем листе). Перейдите по вкладке Главная — группа Редактирование, выберите Найти и выделить — нажмите Найти (или нажмите сочетание клавиш Ctrl + F), заменить доступно по горячим клавишам Ctrl + H .

Введите тест для поиска, например Иван (в поле Найти:) — т.е. вы хотите найти слово Иван во всех ячейках

Кнопка Найти далее будет находится по одной ячейки за каждое нажатие. Если вы нажмете Найти все, Excel отобразит список адресов всех ячеек.

Найти и заменить. Нюансы

Соответственно, чтобы заменить ячейки в поле Замена: нужно ввести, то на что заменяете. Т.е. если вы заменяете точку на запятую, в поле Найти — ставим точку, а в поле Заменить — на запятую

Иногда нужно найти неточные данные. Предположим, нужно найти Иван Иванов, но точное отчество вы не помните. При поиске такого имени можно использовать символы подстановки.

• ? — заменяет любой 1 символ;
• * — заменяет любые символы.

В итоге, введите *Иван*Иванов в поле Найти, а затем нажмите кнопку Найти все. В результатах поиска выпадут все значения, где встречается Иван_какие-то символы_ Иванов.

Можно использовать эти возможности и для поиска чисел. Если в строке поиска записать 7*, в результате отобразятся все ячейки, содержащие значения, начинающееся с 7. Если ввести 3?5*, то получите все четырехзначные и более записи, которые начинаются с 3, второй символ будет любой, а после 5 может любое количество символов.

Чтобы найти саму звездочку (*) или вопросительный знак (?) поставьте перед ними тильду (

?. Как искать саму тильду? Ставьте две тильды

Если что-то найдено неправильно, проверьте настройки:

• Флажок Учитывать регистр — значит поиск учитывает регистр больших и маленьких букв, т.е. если написать «иван» с маленькой буквы, то поиск не даст результаты в нашем случае.
• Флажок Ячейка целиком — т.е. в ячейки нет других символов кроме тех, которые вы ищите.

Как найти и заменить в Excel формулой

Для альтернативного поиска ячеек существует функции Найти (FIND) и Поиск (SEARCH). Обе функции возвращают номер символа, с которого начинается первое слово искомой строки. Эти две функции работают почти одинаково, но функция НАЙТИ учитывает регистр букв, а функция ПОИСК допускает использование символов шаблона

Если вы ищете имя Иван (искомый_текст), в тексте Петров Иван (просматриваемый_текст), нач_позиция можно не заполнять. Excel вернет номер символа в строке просматриваемый_текст, т.е. в нашем примере это будет число 8.

Как заменить звездочку «*» в Excel?

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

*» (явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:

Аналогичная ситуация и при замене или удалении вопросительного знака и тильды. Производя замену

?» (для тильды —

») мы также без проблем сможем заменить или удалить спецсимвол.

Синтаксис и параметры метода

Синтаксис

Синтаксис при замене подстроки и присвоении переменной возвращаемого значения типа Boolean:

Синтаксис при замене подстроки без присвоения переменной возвращаемого значения:

• variable – переменная (тип данных – Boolean);
• expression – выражение, возвращающее объект Range.

Параметры

Параметр	Описание
What	Искомая подстрока или шаблон*, по которому ищется подстрока в диапазоне ячеек. Обязательный параметр.
Replacement	Подстрока, заменяющая искомую подстроку. Обязательный параметр.
LookAt	Указывает правило поиска по полному или частичному вхождению искомой подстроки в текст ячейки:1 (xlWhole) – поиск полного вхождения искомого текста;2 (xlPart) – поиск частичного вхождения искомого текста. Необязательный параметр.
SearchOrder	Задает построчный или постолбцовый поиск:1 (xlByRows) – построчный поиск;2 (xlByColumns) – постолбцовый поиск. Необязательный параметр.
MatchCase	Поиск с учетом или без учета регистра:0 (False) – поиск без учета регистра;1 (True) – поиск с учетом регистра. Необязательный параметр.
MatchByte	Способы сравнения двухбайтовых символов:0 (False) – двухбайтовые символы сопоставляются с однобайтовыми эквивалентами;1 (True) – двухбайтовые символы сопоставляются только с двухбайтовым символами. Необязательный параметр.
SearchFormat	Формат поиска. Необязательный параметр.
ReplaceFormat	Формат замены. Необязательный параметр.

* Смотрите знаки подстановки для шаблонов, которые можно использовать в параметре What.

Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

Замена одного текста на другой внутри заданной текстовой строки – весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE) . Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Её синтаксис таков:

=ПОДСТАВИТЬ( Ячейка ; Старый_текст ; Новый_текст ; Номер_вхождения )

• Ячейка – ячейка с текстом, где производится замена
• Старый_текст – текст, который надо найти и заменить
• Новый_текст – текст, на который заменяем
• Номер_вхождения – необязательный аргумент, задающий номер вхождения старого текста на замену

Обратите внимание, что:

• Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 – обе “Маши” заменены на “Олю”).
• Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая “Маша” заменена на “Олю”).
• Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. “маша” написана с маленькой буквы)

Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом – спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами – заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

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

Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:

Если предполагается, что в ячейке могут находиться и лишние пробелы, то формула будет чуть посложнее, но идея – та же.

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

Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:

У нее простая логика:

1. заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
2. ищем позицию символа # функцией НАЙТИ (FIND)
3. вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)