Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.
Замена или удаление неразрывных пробелов
При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:
Подсчет количества слов в ячейке
Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:
Если предполагается, что в ячейке могут находиться и лишние пробелы, то формула будет чуть посложнее, но идея — та же.
Извлечение первых двух слов
Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:
У нее простая логика:
- заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
- ищем позицию символа # функцией НАЙТИ (FIND)
- вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)
Ссылки по теме
- Зачистка текста от лишних пробелов, непечатаемых символов и т.д.
- Как подсчитать количество слов в ячейке
- Преобразование чисел-как-текст в полноценные числа
Замена по списку
В Excel нет стандартного инструмента для массовой замены по списку. Поэтому я дописал этот маленький, но нужный инструмент и включил его в пакет SEO-Excel. Его интерфейс интуитивно понятен, но будьте осторожны! Он перезапишет вам лист поверх старых данных и отменить это через ctrl+z будет нельзя. У инструмента 3 режима работы :
Плюс, нужно указать где вы хотите произвести замену: в выделенных ячейках (активный диапазон), или в столбце целиком. На гифке ниже это и демонстрируется.
GIF
Замена целых слов или замена подстрок?
Тут все очень просто. Представим, что мы хотим заменить слово «ключ» на слово «замок».
Если выбран режим Целые слова то ячейка Excel с содержанием «ключница» после замены останется без изменений.
Если выбран режим Подстрока то «ключница» превратится в «замокница», что согласитесь, может не совпасть с вашими ожиданиями, поэтому и существует режим Целые слова. Однако, иногда быстрее заменить «дешев» на «недорог», чем выискивать все словоформы: «дешевый, дешевую, дешевые, дешево, дешевая». И для замены «дешев» на «недорог» нужно использовать режим Подстрока.
GIF
Замена по списку регулярных выражений
В SEO-Excel уже есть инструмент который использует регулярные выражение, он называется РегВыр. Он вытаскивает из строки подстроку которая соответствует паттерну регулярного выражения. В отличие от него, Замена по регулярному выражению позволяет найти подстроку которая соответствует паттерну и заменить на то что вам необходимо. Чаще всего необходимо просто удалить, то есть заменить на «ничего».
На гиф выше демонстрируется 2 примера:
1) С помощью регулярного выражение [A-Za-z] производится поиск английских слов и затем удаление их из строки
2) С помощью регулярного выражения \d.*\d производится поиск цифр в строке и затем их удаление. Причем вся соль в том что цифра может быть любая и в любом месте строки.
Примеры работы с текстовой функцией ЗАМЕНИТЬ в Excel
Функция ЗАМЕНИТЬ, входит в состав текстовых функций MS Excel и предназначена для замены конкретной области текстовой строки, в которой находится исходный текст на указанную строку текста (новый текст).
Как работает функция ЗАМЕНИТЬ в Excel?
С целью детального изучения работы данной функции рассмотрим один из простейших примеров. Предположим у нас имеется несколько слов в разных столбцах, необходимо получить новые слова используя исходные. Для данного примера помимо основной нашей функции ЗАМЕНИТЬ используем также функцию ПРАВСИМВ – данная функция служит для возврата определенного числа знаков от конца строки текста. То есть, например, у нас есть два слова: молоко и каток, в результате мы должны получить слово молоток.
Функция заменить в Excel и примеры ее использования
- Создадим на листе рабочей книги табличного процессора Excel табличку со словами, как показано на рисунке:
- Далее на листе рабочей книги подготовим область для размещения нашего результата – полученного слова «молоток», как показано ниже на рисунке. Установим курсор в ячейке А6 и вызовем функцию ЗАМЕНИТЬ:
- Заполняем функцию аргументами, которые изображены на рисунке:
Выбор данных параметров поясним так: в качестве старого текста выбрали ячейку А2, в качестве нач_поз установили число 5, так как именно с пятой позиции слова «Молоко» мы символы не берем для нашего итогового слова, число_знаков установили равным 2, так как именно это число не учитывается в новом слове, в качестве нового текста установили функцию ПРАВСИМВ с параметрами ячейки А3 и взятием последних двух символов «ок».
Далее нажимаем на кнопку «ОК» и получаем результат:
Как заменить часть текста в ячейке Excel?
Пример 2. Рассмотрим еще один небольшой пример. Предположим у нас в ячейках табличного процессора Excel имеются столбцы с мужскими фамилиями и именами сотрудников некоторой фирмы. Необходимо заменить их буквы в определенных местах так, чтобы преобразовать их в женские.
- Создадим на листе рабочей книги Excel табличку со фамилией и именем, как показано на рисунке:
- Далее на этом же листе рабочей книги подготовим область для размещения нашего результата – измененных фамилий и имен. Заполним ячейки двумя типами формул как показано на рисунке:
Обратите внимание! Во второй формуле мы используем оператор «&» для добавления символа «а» к мужской фамилии, чтобы преобразовать ее в женскую. Для решения данной задачи можно было бы использовать функцию =СЦЕПИТЬ(B3;»а») вместо формулы =B3&»а» – результат идентичный. Но сегодня настоятельно рекомендуется отказываться от данной функции так как она имеет свои ограничения и более требовательна к ресурсам в сравнении с простым и удобным оператором амперсанд.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Замена по списку
Команда поможет справиться с задачей по массовой замене слов. Предположим у нас имеется список остатков товара на складах, который обновляется и выгружается в Excel ежедневно: 
Список может быть на несколько тысяч строк, естественно. Однако в этом списке вместо наименования товара записан его артикул. Однако для дальнейшей работы с остатками необходим не артикул, а то наименование, которое имеется в остальных документах и в базе товара, т.к. этот список в последствии должен выгружаться на сайт для актуализации остатков товара в интернет-магазине, которое будет отражено клиентам. Чтобы получился список следующего вида: 
Для этого выгружается отдельный список соответствия товара артикулу: 
В данном случае стандартная замена данных(Ctrl+H) больше похожа на испытание. Это сколько раз надо сделать замену, чтобы заменить каждый артикул? Наименований товаров могут быть сотни, а то и тысячи. Можно также прибегнуть к различным хитростям вроде вытягивания последнего слова(чтобы получить артикул), но ведь может быть и так, что артикул расположен где-то внутри ячейки, а не последним словом(например, » Аккумулятор для ноутбуков 25011445, Б\У » или еще хуже — несколько разных артикулов в одной ячейке: » Аккумулятор для ноутбуков 25011445, 25011446, 25011447, 25011448 «).
Вот здесь в самый раз пригодится команда Замена по списку. Она на основании списка соответствия может заменить все найденные значения артикулов на наименования за пару минут и без особых усилий. При этом замену можно сделать не только в отдельных ячейках, но и на всем листе, во всей книге или во всех открытых книгах. 
Список соответствий находится в ячейках( [Замена.xls]Соответствия!A2:B8 )- диапазон из двух столбцов, содержащий значения для поиска и замены. Не должен содержать заголовков, иначе текст заголовков будет воспринят как значения для поиска и замены.
-
Искомые значения в столбце: указывается номер столбца в списке соответствий, в котором расположены значения для замены. На примере артикулов и наименований: нам необходимо заменить артикул на наименование. Следовательно в поле необходимо указать число 1, т.к. именно в первом столбце перечень артикулов для замены на наименования товара. А значения для замены будут взяты из второго столбца. Если же необходимо произвести обратную процедуру — заменить наименования на артикулы — то в поле необходимо указать число 2 и в этом случае наименования будут заменены артикулами.
Произвести замену:
в этом блоке можно выбрать где конкретно производить замену
- в указанном диапазоне — указывается любой диапазон на любой листе любой открытой книги. В этом случае замена значений будет произведена исключительно в ячейках указанного диапазона. Все остальные ячейки и листы будут не тронуты. Указывать необходимо более одной ячейки, иначе замена будет произведена по всему листу.
- во всех ячейках листа — в этом случае необходимо выбрать из списка лист, на котором производить замену. По умолчанию в списке отображается активный лист активной книги
- на всех листах книги — выбирается книга из списка всех открытых книг. После выполнения команды замена будет произведена во всех ячейках всех листов выбранной книги
- во всех листах активной книги, в диапазоне — указывается диапазон ячеек. Но в этом случае замена будет произведена во всех ячейках указанного диапазона во всех листах открытой книги. Для чего это нужно? Допустим, есть много листов в книге с различными шаблонными таблицами. И в определенном диапазоне этой каждой таблицы надо заменить данные по списку. При этом в других ячейках таблиц так же могут содержаться данные, но заменять их не требуется.
- на всех листах всех открытых книг — замена будет произведена во всех ячейках всех листов всех открытых книг.
Производить замену по части ячейки — если установлено, то каждое из искомых значений списка будет просматриваться по части ячейки. Например, если в ячейке текст » Аккумулятор для ноутбуков 25011445 «, то в данном случае артикул является частью другого текста ячейки. И если галочку не установить — то значение артикула » 25011445 » не будет заменено, т.к. к сравнению будет принят весь текст ячейки — » Аккумулятор для ноутбуков 25011445 «.
Для чего это нужно: логично, что в большинстве случаев замену необходимо производить именно по части текста ячейки, но бывают случаи, когда необходимо заменять именно весь текст. Например, если в ячейке содержится только наименование товара(» Аккумулятор для ноутбуков Samsung R590-JS03 «) и его надо заменить на конкретный артикул. Если сделать по части ячейки, то в этом случае может получиться так, что отдельно будет заменена только часть текста » Аккумулятор для ноутбуков «, а остальное уже не попадет под замену. Такая замена будет некорректна с точки зрения требуемого результата.
Учитывать регистр букв — если установлен, то строчные и заглавные буквы в словах будут различаться. Иногда требуется, чтобы заменить, скажем, только аббревиатуры, но не трогать похожие совпадения внутри текста, написанные строчными символами.
Легко догадаться, что команду можно использовать в любой ситуации как упрощенный словарь-переводчик. В одном столбце английские слова, в другом русские; в одном число — в другом его пропись; в одном код цвета — в другом его наименование; и т.д.
Примечание: Замена производится только в не защищенных ячейках. Если какие-либо листы защищены и произвести замену в них невозможно, то программа пропустит такие листы, но после работы будет показано сообщение с перечислением имен листов, в которых замену произвести не удалось. Если листов более 15-ти, то имена будут выведены только для первых 15-ти листов.
Расскажи друзьям, если статья оказалась полезной:
Видеоинструкции по использованию надстройки MulTEx