Как заменить цифры в excel на цифры

Обновлено: 07.01.2025

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

Функция является в некоторой степени аналогом процедуры Найти и Заменить в Excel, с некоторыми отличиями:

Что очевидно и является базовым отличием, она является функцией, т.е. пересчитывается автоматически, в отличие от одноразового характера процедур, и выводит результат в другую ячейку, в то время, как процедура меняет результат на месте;
Функция ПОДСТАВИТЬ всегда чувствительна к регистру. В процедуре «найти и заменить» чувствительность к регистру опциональна;
Функция не поддерживает подстановочные символы («*» и «?»);
Зато позволяет осуществлять массовую замену нескольких (до 64 в последней версии Excel) значений одновременно;
Еще одно преимущество функции в том, что она позволяет заменить не все вхождения, а определенное по порядку в строке, чего не позволяет сделать процедура.

Синтаксис

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

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

Характерные особенности функции ПОДСТАВИТЬ:

Пустая ячейка воспринимается как строка нулевой длины («»).
Логические значения конвертируются в текст с сохранением регистра.
Все вариации числовых форматов (дата, время, дата-время, проценты, финансовый формат) конвертируются в соответствующее им натуральное число в текстовом формате, поэтому для сохранения исходного форматирования может понадобиться функция ТЕКСТ.

Примеры

Функция ПОДСТАВИТЬ необычайно популярна и может использоваться:

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

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

ПОДСТАВИТЬ с несколькими условиями (транслитерация)

Заменить кириллицу на транслит автоматически с помощью формулы? Можно! Правда, длина формулы настолько велика, что ее лучше сохранить где-нибудь, а не составлять самостоятельно. Потому что это формула с 64 условиями (уровнями вложенности). Подробнее — по ссылке в начале этого абзаца.

ПОДСТАВИТЬ, чтобы посчитать слова

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

Сколько слов в ячейке? На 1 больше, чем пробелов между ними. Это будет справедливо всегда, если разделителем слов считается только пробел, и если пробелов между каждыми двумя словами не более 1. Чтобы обезопасить себя от лишних, можно воспользоваться функцией СЖПРОБЕЛЫ.

Как посчитать пробелы в строке? Как и любой символ, можно посчитать их, подсчитав длину строки с ними и без них, и вычислив разницу. Как получить строку без них? Конечно, с помощью функции ПОДСТАВИТЬ.

Так будет выглядеть формула для ячейки A1, учитывающая все эти нюансы. ДЛСТР измеряет длину строк:

У формулы есть побочный эффект — для пустой ячейки она возвращает 1, поэтому, если пустые ячейки могут присутствовать, формулу выше придется «обернуть» выражением с Excel функцией ЕСЛИ:

Заменить пробелы на переносы строк

Часто возникает задача заменить пробелы в строке на переносы строк, и наоборот. Здесь будет полезна комбинация функций ПОДСТАВИТЬ и СИМВОЛ. Так будет выглядеть формула, заменяющая все пробелы в строке на переносы строк.

Как видно из формулы, код символа переноса строки — 10.

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

Отображение переносов строк в Excel

ПОДСТАВИТЬ, чтобы посчитать встречаемость в тексте

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

Первая (ПОДСТАВИТЬ) позволяет создать строку без указанного фрагмента, заменяя его на пустоту.
Вторая (ДЛСТР) — измерить длину результата.
Если вычесть из исходной длины полученную, и разделить на длину этого фрагмента, можно получить его встречаемость.
Если заведомо известно, что это один символ, процесс деления можно опустить, т.к. результат от деления на 1 не изменится.

Так выглядит формула, считающая в ячейке A1 количество 1-символьного фрагмента из ячейки B1:

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

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

ПОДСТАВИТЬ для подсчета цифр в тексте

Представим, что наша задача — провалидировать список номеров телефонов на количество цифр в нем. Корректным будем считать номер, в котором 11 цифр.

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

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

Комбинация функций ПОДСТАВИТЬ и ДЛСТР в формуле массива с функцией СУММ позволит решить данную задачу:

Подсчитываем количество цифр в строках со смешанным содержимым. В столбце B формулы, в столбце С — их текст.

Поиск и замена данных – одна из часто применяемых операций в Excel. Используют даже новички. На ленте есть большая кнопка.

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

Далее вызываем Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F. Откроется диалоговое окно под названием Найти и заменить.

В единственном поле указывается информация (комбинация символов), которую требуется найти. Если не использовать подстановочные символы или т.н. джокеры (см. ниже), то Excel будет искать строгое совпадение заданных символов. Для вывода результатов поиска предлагается два варианта: выводить все результаты сразу – кнопка Найти все; либо выводить по одному найденному значению – кнопка Найти далее.

После запуска поиска программа Excel быстро-быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия искомой комбинации символов. Если такая комбинация обнаружена, то в случае нажатия кнопки Найти все Excel вываливает все найденные ячейки.

Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.

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

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

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

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

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

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

Продвинутый поиск

Мало, кто обращается к кнопке Параметры в диалоговом окне Найти и заменить. А зря. В ней скрыто много полезностей, которые помогают решить проблемы поиска. После нажатия кнопки Параметры добавляются дополнительные поля, которые еще больше углубляют и расширяют условия поиска.

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

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

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

Поиск формата – это хорошо, но чаще искать приходится конкретные значения. И тут Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.

Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.

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

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

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.

Поиск и замена данных

Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.

Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.

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

По аналогии с простым поиском, менять можно и формат.

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

Жмем Ok. Все переносы строк заменились пробелами.

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

Теперь вы знаете, как в эксель сделать поиск по столбцу, строке, любому диапазону, листу или даже книге.

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

Поиск

Чтобы найти что-то, нажмите CTRL+Fили перейдите на главная > редактирования> Найти & Выберите >Найти.

Примечание: В следующем примере мы нажали кнопку Параметры >>, чтобы отдемонстрировать все диалоговое окно Найти. По умолчанию она будет отображаться со скрытыми параметрами.

В поле Найти что: введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле Найти: и выберите последний элемент поиска из списка.

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(

Используйте знак вопроса (?), чтобы найти любой отдельный символ. Например, если найти "м?т" и "множество", то будут отспросюмы.

Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д" и "начало", они будут отозныны.

), за которой следует ?, *или

, чтобы найти вопросии, звездочки или другие символы тильды, например fy91

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

В: Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или По столбцам.

Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите формулы, значения,заметки или примечания.

Примечание: Формулы,значения,заметки и примечания доступны только на вкладке Найти. На вкладке "Заменить" доступны только формулы.

Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

Совпадение всего содержимого ячеек. Проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти: .

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

Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с кнопкой Формат, выберите пункт Выбрать формат из ячейки, а затем щелкните ячейку с форматированием, которое требуется найти.

Заменить

Чтобы заменить текст или числа, нажмите CTRL+Hили перейдите на главная > редактирования> Найти & Выберите > Заменить.

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(

), за которой следует ?, *или

, чтобы найти вопросии, звездочки или другие символы тильды, например fy91

В поле Заменить на: введите текст или числа, которые нужно использовать для замены текста поиска.

Нажмите Заменить все или Заменить.

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

В: Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или По столбцам.

Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

Существует два разных способа поиска или замены текста или чисел на компьютере Mac. Первый из них — использование диалогового & Заменить. Второй вариант — использование панели поиска на ленте.

Диалоговое & поиска

Поиск и параметры

Поиск текста или чисел с помощью диалогового & поиска

Чтобы найти что-то, нажмите CTRL+Fили перейдите на главная > Найти & Выберите >Найти.

В поле Найти: введите текст или числа, которые нужно найти.

При необходимости вы можете определить поиск.

В: Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или По столбцам.

Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(

), за которой следует ?, *или

, чтобы найти вопросии, звездочки или другие символы тильды, например fy91

Замена текста или чисел с помощью диалогового & поиска

Чтобы заменить текст или числа, нажмите CTRL+Hили перейдите на главная > Найти & Выберите >Заменить.

В поле Найти введите текст или числа, которые нужно найти.

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

При необходимости вы можете определить поиск.

В: Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или По столбцам.

Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(

), за которой следует ?, *или

, чтобы найти вопросии, звездочки или другие символы тильды, например fy91

Щелкните любую ячейку, чтобы найти весь лист, или выберите определенный диапазон ячеек для поиска.

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

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(

Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти "м?т", то будут "ед" и "множество".

), за которой следуют ?, *или

, чтобы найти вопросии, звездочки или другие символы тильды, например fy91

Нажмите клавишу RETURN.

Чтобы найти следующий экземпляр ищите элемент, щелкните поле поиска и нажмите кнопку RETURN илив диалоговом окне Найти нажмите кнопку Найти далее.

Совет: Вы можете отменить поиск, нажав клавишу ESC.

Чтобы задать дополнительные параметры поиска, щелкните увеличительное стекло и выберите поиск на листе или Поиск в книге. Вы также можете выбрать параметр "Дополнительные параметры", чтобы запустить диалоговое окно Найти.

Щелкните любую ячейку, чтобы найти весь лист, или выберите определенный диапазон ячеек для поиска.

Нажмите +F, чтобы развернуть область поиска, а затем в поле поиска введите текст или число, которое вы хотите найти.

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(

), за которой следуют ?, *или

, чтобы найти вопросии, звездочки или другие символы тильды, например fy91

В поле поиска щелкните значок лупы и выберите команду Заменить.

В поле Заменить на введите замещающие символы.

Чтобы заменить символы в поле Найти пустыми, оставьте поле Заменить на пустым.

Чтобы заменить выделенное вхождение, нажмите кнопку Заменить.

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

Совет: Отмените поиск, просто нажав клавишу ESC.

Поиск

Чтобы найти что-то, нажмите CTRL+Fили перейдите на главная > редактирования> Найти & Выберите >Найти.

Примечание: В следующем примере мы нажали кнопку Параметры >, чтобы отдемонстрировать все диалоговое окно Найти. По умолчанию он будет отображаться со скрытыми параметрами поиска.

В поле Найти: введите текст или числа, которые нужно найти.

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(

), за которой следуют ?, *или

, чтобы найти вопросии, звездочки или другие символы тильды, например fy91

Нажмите > параметры поиска, чтобы при необходимости определить поиск.

В: Чтобы найти данные в пределах определенного выделения, выберите Выделение. Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

Направление: Вы можете выбрать поиск вниз (по умолчанию) или ВВЕРХ.

Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

Совпадение со всем содержимым ячеек: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти.

Заменить

В поле Найти: введите текст или числа, которые нужно найти.

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(

), за которой следуют ?, *или

, чтобы найти вопросии, звездочки или другие символы тильды, например fy91

В поле Заменить на: введите текст или числа, которые нужно использовать для замены текста поиска.

Нажмите > параметры поиска, чтобы при необходимости определить поиск.

Направление: Вы можете выбрать поиск вниз (по умолчанию) или ВВЕРХ.

Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Горячие клавиши

Сочетания клавиш ниже заметно ускорят работу с инструментом:

Для запуска диалогового окна поиска — Ctrl + F
Для запуска окна поиска и замены — Ctrl + H
Для выделения всех найденных ячеек (после нажатия кнопки «найти все» — Ctrl + A
Для очистки всех найденных ячеек — Ctrl + Delete
Для ввода одних и тех же данных во все найденные ячейки — Ввод текста, Ctrl + Enter

Смотрите gif-примеры: здесь мы производим поиск ячеек с дальнейшим их редактированием. В отличие от замены, редактирование найденных ячеек позволяет быстро менять их содержимое целиком.

Находим все пустые ячейки в диапазоне и заполняем их нулями или одним значением с помощью горячих клавиш

Процедура «Найти и заменить» не работает

Я сам когда-то неоднократно впадал в ступор в подобных ситуациях. Уверен и видишь своими глазами, что искомый паттерн в данных есть, но Excel при выполнении процедуры поиска сообщает:

Не удалось ничего найти по вашему запросу

Мы не нашли ничего, что нужно было заменить

Статус опций «Учитывать регистр» и «Ячейка целиком» виден после нажатия кнопки «Параметры».

Подстановочные знаки, или как найти «звездочку»

Сухая официальная справка по Excel сообщает, что можно использовать подстановочные символы «*» и «?». Что они означают несколько символов, включая их отсутствие, и один любой символ. И что их можно использовать для соответствующих процедур поиска.

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

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

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

Так а как найти звездочку?

Действительно, забыл. Чтобы найти «звездочку», нужно в окошке поиска ставить перед ней знак

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

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

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

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

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

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

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

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

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

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

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

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

Файл доступен по ссылке, но можно и не скачивать его, а просто скопировать текст формулы ниже и вставить ее в любую ячейку, кроме диапазона A1:B64. Формула заменяет в ячейке C1 значения в столбце A стоящими напротив в столбце B.

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

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

С помощью функции «ПОДСТАВИТЬ«

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

С помощью регулярных выражений

Важно: регулярные выражения не поставляются в Excel «из коробки», но формулы ниже доступны бесплатно, если установить надстройку !SEMTools

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

Массовая замена символов регулярными выражениями при установленной надстройке !SEMTools

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

Массовая замена в !SEMTools

Надстройка для Excel !SEMTools позволяет в пару кликов производить замены на всех уровнях:

символов и их сочетаний
паттернов регулярных выражений
слов!
целых ячеек (В некоторой степени аналог ВПР)

Меню инструментов массовой замены в !SEMTools

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

Пример: замена символов по вхождению

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

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

Массовая замена символов по вхождению на примере Leet Language (некоторые английские буквы заменяются на похожие цифры)

Пример: замена списка слов на другой список слов

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

С версии !SEMTools 9.18.18 появилась опция — при замене списка слов не учитывать пунктуацию в исходных предложениях, а регистр слов теперь сохраняется:

Инструменты находятся в группе макросов «ИЗМЕНИТЬ» в отдельном меню и для удобства продублированы в меню «Изменить символы«, «Изменить слова» и «Изменить ячейки«.

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