Как работает функция подставить в excel

Примеры функции ПОДСТАВИТЬ для замены текста в ячейке Excel

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

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

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

Для замены и подстановки используем рассматриваемую формулу в качестве массива. Вначале выделим диапазон ячеек C2:C9, затем введем формулу через комбинацию Ctrl+Shift+Enter:

Функция ЧЗНАЧ выполняет преобразование полученных текстовых строк к числовым значениям. Описание аргументов функции ПОДСТАВИТЬ:

• B2:B9 – диапазон ячеек, в которых требуется выполнить замену части строки;
• “NaN” – фрагмент текста, который будет заменен;
• 0 – фрагмент, который будет вставлен на место заменяемого фрагмента.

Для подстановки значений во всех ячейках необходимо нажать Ctrl+Shift+Enter, чтобы функция была выполнена в массиве. Результат вычислений:

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

Автозамена значения в текстовых ячейках с помощью функции ПОДСТАВИТЬ

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

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

Примечание: в данном примере ПОДСТАВИТЬ также используется в массиве Ctrl+Shift+Enter.

В результате получим:

Формула с макросом регулярного выражения и функция ПОДСТАВИТЬ

Пример 3. При составлении таблицы из предыдущего примера была допущена ошибка: все номера домов на улице Никольская должны быть записаны как «№№-Н», где №№ — номер дома. Как быстро исправить ошибку?

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

1. Открыть редактор макросов (Ctrl+F11).
2. Вставить исходный код функции (приведен ниже).
3. Выполнить данный макрос и закрыть редактор кода.

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

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

Для решения задачи данного Примера 3 используем следующую запись:

1. Функция ЕСЛИОШИБКА используется для возврата исходной строки текста (B2), поскольку результатом выполнения функции RegExpExtract(B2;»Никольская») будет код ошибки #ЗНАЧ!, если ей не удалось найти хотя бы одно вхождение подстроки «Никольская» в строке B2.
2. Если результат выполнения сравнения значений RegExpExtract(B2;»Никольская»)=»Никольская» является ИСТИНА, будет выполнена функция ПОДСТАВИТЬ(B2;RegExpExtract(B2;»\d+»);RegExpExtract(B2;»\d+»)&»-Н»), где:

• a. B2 – исходный текст, содержащий полный адрес;
• b. RegExpExtract(B2;»\d+») – формула, выделяющая номер дома из строки с полным адресом;
• c. RegExpExtract(B2;»\d+»)&»-Н» – новый номер, содержащий исходное значение и символы «-Н».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Подставить в excel

​ применения не придумал. ​ аналогии с моей​ .а вцелом сгласен​ стар_текст, функция ПОДСТАВИТЬ​ ей не удалось​​ неразрывного пробела, это​Сервис​ с помощью машинного​ ПСТРБ описаны ниже.​ формулу добавили «1»​

Синтаксис

​В этой статье описаны​ при открытии выпадающего​

​пример:​​ instr(ArCell(n),»:»)=0 ‘+3) (. ​ в этот раздел.​ikki​ формулой кучу ПОДСТАВИТЬ​

​ с​​ вернет строку текст​

​ найти хотя бы​​Пример 2. Провайдер домашнего​ может формула​

​выберите команду​​ перевода, см. Отказ​Текст​ — значит заменить​ использовать функцию «ПОДСТАВИТЬ».​ синтаксис формулы и​ списка и выбора​(1-$C$21)/$C$22+($C$21*(СУММ(ЕСЛИ((C$2>0);($C​ and instr(ArCell(n), «!»)=0)​Попробуйте этот макрос​: а можно посмотреть​Klava123​

Пример

​RAN​ без изменений.​ одно вхождение подстроки​ интернета хранит данные​=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A1;СИМВОЛ(160);СИМВОЛ(32)))​Надстройки​ от ответственности. Используйте​ Обязательный. Текстовая строка, содержащая​ первое слово «снег».​ Эта функция меняет​ использование​ в нем нужного​26​

​ If (ArCell(n) Like​

​Если число вхождений заменяемого​

​ «Никольская» в строке​

​ о своих абонентах​

​Формула заменит все​, затем выберите поле​

​ английский вариант этой​

​ символы, которые требуется​

​ Получилось так.​ старый текст на​ПОДСТАВИТЬ​

Функция «ПОДСТАВИТЬ» в Excel.

​Цитата​ фрагмента в обрабатываемой​ B2.​ в таблице Excel.​ знаки неразрывного пробела​Создание подстановки​ статьи, который находится​ извлечь.​
​Шестой вариант.​ новый.​​в Microsoft Excel.​В принципе, можно​26​ _ And ArCell(n)​(1 level -​​Buchhalter​Buchhalter​RAN, 18.06.2016 в​ строке меньше, чем​Если результат выполнения сравнения​ Предположим, улица Садовая​
​ (код символа 160)​
​, после чего щелкните​ здесь, в качестве​ ​Начальная_позиция​
​Как удалить пробелы в​Первый вариант.​Подставляет значение аргумента «нов_текст»​ было бы копировать​/$S$3);0);ЕСЛИ((C$4>0);($E​
​ Like «*[A-Z]$#» =​ замена только на​: У меня громадные​
​: Предположим A1=8, B1=4,​ 09:43, в сообщении​ числовое значение, переданное​
​ значений RegExpExtract(B2;»Никольская»)=»Никольская» является​ была переименована в​ знаком пробела (код​ кнопку​ справочного материала.​ Обязательный. Позиция первого знака,​
​Excel.​Как заменить текст в​
​ вместо значения аргумента​ не только данные,​2​ False) _ Or​ 1 уровень выше​ логические формулы. ЕСЛИ. ​ C1=6​ № 2200?’200px’:»+(this.scrollHeight+5)+’px’);»>Это самая​
​ в качестве аргумент​ ИСТИНА, будет выполнена​
​ Никольскую. Необходимо быстро​
​ символа 32) с​OK​ ​Важная информация для пользователей​
​ извлекаемого из текста.​У нас такая​ ячейке​ «стар_текст» в текстовой​ но и диапазон​6/$S$4);0)​ InStr(ArCell(n), «:») <>​ из влияющей ячейки).​ ЕСЛИ нет, то​
​Пример формулы скажем​
​ верхняя тема.​ [номер_вхождения], функция ПОДСТАВИТЬ​ ​ функция ПОДСТАВИТЬ(B2;RegExpExtract(B2;»\d+»);RegExpExtract(B2;»\d+»)&»-Н»), где:​
​ произвести замену названия​ помощью функции ПОДСТАВИТЬ(),​.​ Office 2003.​ Первый знак в​
​ таблица. Нам нужно​ ​Excel.​
​ строке. Функция ПОДСТАВИТЬ​ фамилий (на случай​нужно заменить число​ 0 _ Or​(All — перебор,​ следующая формула ЕСЛИ​ в ячейке Д1​
​меняйте название темы​
​ вернет текстовую строку​a. B2 – исходный​ ​ улицы в строке​
​ а затем удаление​Щелкните ячейку в диапазоне.​ Если вы хотите​ тексте имеет начальную​ убрать пробелы в​Нам нужно заменить​ используется, когда нужно​
​ их изменения, перестановки).​
​ 26 на 37,​ InStr(ArCell(n), «!») <>​ ​ пока не закончатся​
​ и так далее.​ — «=(A1+B1)/C1″​ и прикладывайте пример​ в исходном виде.​ текст, содержащий полный​
​ данных об адресе​
​ начального пробела и​В меню​ и дальше получать​ позицию 1 и​ ячейкач А14 и​ ​ одно слово (несколько​
​ заменить определенный текст​ ​ Тогда было бы​
​ во всей строке​ 0 Then ArCell(n)​ замены формул).​ По-сути формулы перебирают​Как сделать чтобы​Klava123​ Например, аргументы функции​
​ адрес;​ проживания каждого клиента.​ повторяющихся промежуточных пробелов​Сервис​ обновления системы безопасности​ так далее.​
​ А15. В ячейке​ слов) на другое.​ в текстовой строке;​ точное соответствие ФИО​ формулы.​ = vbNullString Next​
​(Можно выделять сразу​ все возможные комбинации​ в Д1 автоматически​: Я в сообщении​ («а 1 а​b. RegExpExtract(B2;»\d+») – формула,​Таблица данных:​ из строки в​

Функции ПСТР, ПСТРБ в Excel

​выберите команду​ для Office, вам​Число_знаков​​ В14 пишем такую​​ У нас такое​​ функция ЗАМЕНИТЬ используется,​​ сотрудника и данных.​

Описание

​В первом посте​ ‘=== начало замен​ несколько ячеек (диапазон)​ сопоставляя значения. В​

​ скомпилировалась формула «=(8+4)/6″​ написала, какие есть​ 2 а 3»;«а»;«б»;4)​ выделяющая номер дома​Для выполнения заданного условия​ ячейке​

​Подстановка​​ необходима версия Office 2003​

​ Обязательный. Указывает, сколько знаков​ формулу. =ПОДСТАВИТЬ(A14;» «;»»)​ предложение в ячейке.​

​ когда нужно заменить​остается тогда только​ своем разобрался, решил​ === pos =​Особо не проверял.​ случае, когда выпадает​С уважением,​ условия, мне нужно​ вернут строку «а​

​ из строки с​ используем формулу:​А1​.​ с пакетом обновления 3​ должна вернуть функция​

​Первые кавычки в​Нам нужно заменить слово​ любой текст начиная​ реализация п.1.​ задачу через Сtrl+H​ 1 ‘позиция поиска​Пока еще не​ ИСТИНА, там происходит​Виктор Сергеевич​ их соединить. С​ 1 а 2​ полным адресом;​

​Примечание: в данном примере​.​Следуйте инструкциям мастера.​ (SP3). Поддержка Office 2003​ ПСТР.​

Синтаксис

​ формуле написали с​

​ с определенной позиции.​Serge_007​

​ заменить.​​ For n =​ успел:​ простой подсчет данных​

​murcha86​​ помощью функции «подставить».​ а 3».​c. RegExpExtract(B2;»\d+»)&»-Н» – новый​ ПОДСТАВИТЬ также используется​Кроме того, при вводе​К началу страницы​

​ прекращается 8 апреля​​Число_байтов​ пробелом, вторые –​ «снежная крупа».​

​ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения])​​: Не оптимизируя (см.​и еще как​ 0 To UBound(ArCell)​1. Реализовать чтение​

Замечания

​ с использованием простых​: Для чего. ​ Кто-нибудь может написать​Рассматриваемая функция чувствительная к​ номер, содержащий исходное​

​ в массиве Ctrl+Shift+Enter.​ в программе MS​Мастер больше не учитываются​ 2014 г. Если вы​ Обязательный. Указывает, сколько знаков​ без пробела. Копируем​В ячейке В9​Аргументы функции ПОДСТАВИТЬ описаны​

​ вложение)​ добавить часть формулы​ If Len(ArCell(n)) <>​ ячейки, которая ссылается​

​ математических функций.​Код =СЦЕПИТЬ(«=(«;A1;»+»;B1;»)/»;C1)​ саму формулу. По​ регистру, то есть​

​ значение и символы​В результате получим:​ WORD чисел с​ в Excel 2010.​

Пример

​ продолжаете работать с​ должна вернуть функция​ формулу по столбцу.​ пишем такую формулу.​ ниже.​карандаш​ в выбранную область​ 0 Then If​ на другой лист.​Каждая формула ЕСЛИ​Buchhalter​ поводу функции «Если»,​ строки «Слово» и​

​Пример 3. При составлении​

​ разделителями разряда, MS​

​ Эта функция был​

​ Office 2003 после прекращения​

​ ПСТРБ (в пересчете​

​ Получилось так.​ =ПОДСТАВИТЬ(A9;»снег»;»снежная крупа»)​Текст​: спасибо​

​ ячеек содержащих ф-лу​

​ Range(ArCell(n)).HasFormula Then pos​

​2. Не знаю,​ отличается от предшествующей,​: Не подумал, что​ мне не совсем​ «слово» не являются​Результат вычислений:​ таблицы из предыдущего​ WORD автоматически заменяет​ заменен мастером функций​ поддержки, то для​ на байты).​Как убрать лишние​

​Этой формулой мы​

​ Обязательный. Текст или ссылка​

​почти работает​например​ = InStr(pos, Original,​ как выглядит синтаксис​

Создание формулы подстановки с помощью мастера подстановок

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

​Если значение «начальная_позиция» больше,​ пробелы в Excel,​​ говорим – заменить​ на ячейку, содержащую​при копировании листа​к данной ф-ле​ ArCell(n)) If Mid$(Original,​ слияния формул в​ значения из разных​ функцию. Мне казалось,​ ее должна применить.​Для решения аналогичных задач​ громоздким, однако он​ ошибка: все номера​ разряды, на символ​ ссылки и поиска​ обновлений системы безопасности​ чем длина текста,​ смотрите в статье​ все слова «снег»​ текст, в котором​ «март» получился лист​ ЕСЛИ((C$3>0);($D26/$S$3);0) нужно добавить​

​ pos + 1,​ диапазоне. Доделаю, если​ ячеек, многие такие​ что она работает​Подскажите, пожалуйста, как​ по замене части​ весьма удобен для​ домов на улице​ неразрывного пробела. После​ (Справка).​ вам необходимо выполнить​ то функция ПСТР​ «Как удалить лишние​ в ячейке А9​ подставляются знаки.​ «март (2)»​

​ 785/5644+654(8+5).​ 1) <> «:»​ объясните мне на​ формулы различаются между​ только с текстом.​ все это прописать.​

Использование мастера подстановок в Excel 2007

​ символов текстовой строки​

​ работы с таблицами,​​ Никольская должны быть​​ копирования таких чисел​​Формулы, созданные с помощью​​ обновление до последней​​ возвращает строку «»​​ пробелы в Excel»​​ на слова «снежная​Стар_текст​переименовал его в​Казанский​

​ Then ‘если найден​​ таком примере:​​ собой радикально.​ ​Мне нужно минимизировать​​китин​​ можно использовать функцию​​ содержащими большое количество​​ записаны как «№№-Н»,​

​ в EXCEL через​​ этого мастера, будут​​ версии Office, например​​ (пустую строку).​​ тут.​ крупа».​​ Обязательный. Заменяемый текст.​​ «апрель»​

​: Buchhalter,если хотите получить​​ не диапазон If​​Итак, например, есть​Так вот, для​​ количество используемых ячеек​​: у вас одну​​ ЗАМЕНИТЬ. Однако, в​​ данных.​

​ Буфер обмена они вставляются​

Использование мастера подстановок в Excel 2003

​ действовать в Excel​​ Office 365 или Office 2013.​​Если значение «начальная_позиция» меньше,​​Есть в Excel​​Нажимаем «ОК». Получилось​​Нов_текст​​в списке имен​ реальную помощь,составьте небольшой​​ Mid$(Original, IIf(pos <>​​ зависимая ячейка:​

​ создания такого типа​

​ за счет создания​​ тему за неправильное​​ отличие от функции​​Функция ПОДСТАВИТЬ имеет следующую​​ номер дома. Как​

Что произошло с мастером подстановок в Excel 2010?

​ Дополнительные сведения см.​ чем длина текста,​ еще одна функция​ так.​ Обязательный. Текст, на который​ на «Лист1» ничего​ пример с аналогичными​

​ 1, pos -​=СУММПРОИЗВ(B4:C4*B9:C9) . где​ формул приходится каждую​ более сложных формул​ название уже закрыли.​

​ ПОДСТАВИТЬ, для ее​

​ синтаксическую запись:​​ быстро исправить ошибку?​​ бороться с неразрывным​ изменять другими способами.​​ в статье Прекращение​ но сумма значений​ поиска и замены​Формулу можно установить​ заменяется «стар_текст».​ не переименовалось, соответственно​ формулами.​ 1, Len(Original) +​[B4]. =СУММ(A1:B1)​ прописывать в отдельной​ в первом столбце​ хотите еще и​ использования необходимо явно​= ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения])​В данном случае для​ пробелом в этом​

Функция СЖПРОБЕЛЫ() в MS EXCEL

​К началу страницы​ поддержки Office 2003.​ «начальная_позиция» и «число_знаков»​ данных в ячейках.​ с помощью функции.​Номер_вхождения​ при выборе из​Когда вам подскажут​ 1), 1) <>​[C4]. =СУММ(A2:B2)​ ячейке, а потом​

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

​ на странице. Скрывать​​ бан получить? Решение​

​ указывать позицию начального​​Описание аргументов:​ поиска числовых значений​

Пример

​ случае — читайте​Примечание:​В выпусках Excel 2007​ превышают длину текста,​ Об этой функции​ Функция «ПОДСТАВИТЬ» находится​ Необязательный. Определяет, какое вхождение​ списка «март (2)»​ решение можете переходить​ «:» Then ThatFormula​

Символ неразрывного пробела

​[B9] и [C9]​ объединять таких от​ столбцы мне не​ есть, но выкладывать​ символа для замены,​текст – обязательный аргумент,​ номера дома воспользоваться​ в этой статье.​ ​ и Excel 2003​ функция ПСТР возвращает​​ читайте в статье​​ на закладке «Формулы»​ фрагмента «стар_текст» нужно​ в ячейках написано​ к следующему этапу​ = Range(ArCell(n)).Formula ThatFormula​
​ — не важно.​
​ 20-до 50 формул​ хотелось бы.​ не буду из​ а также количество​ характеризующий текстовую строку,​ встроенными функциями не​Функция ПОДСТАВИТЬ в Excel​Отказ от ответственности относительно​ мастер подстановок создает​ знаки вплоть до​ «Функция «ЗАМЕНИТЬ» в​​ в разделе «Библиотека​​ заменить фрагментом «нов_текст».​

​ #н/д​ усовершенствования алгоритма решения​ = «(» &​Как мне подставить​ вместе вручную, чтобы​У меня многие​ за нарушения правил​ символов, которые необходимо​ в которой необходимо​ удастся. Рациональнее всего​ выполняет динамическую замену​ машинного перевода​ формулы подстановки на​ конца текста.​ Excel».​ функций». Нажимаем на​

Примеры функции ПОДСТАВИТЬ для замены текста в ячейке Excel

​ Если этот аргумент​попробовал как-то обновить​ проблемы.И т.д. поэтапно.​ Right$(ThatFormula, Len(ThatFormula) -​ 2 последние формулы​ все было вместе.​ формулы занимают по​ форума. Все равно​ заменить. Функция автоматически​ выполнить замену части​ использовать регулярные выражения.​ определенной части строки​. Данная статья была​

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

​ основе данных листа​Если значение «начальная_позиция» меньше​В этой статье описаны​ кнопку «Текстовые» и​ определен, то заменяется​ список путем растягивания​ И сами научитесь​ 1) & «)»​ в первую?​У меня очень​ 5000 знаков. Зато​ сейчас придут добрейшей​ выполняет поиск указанной​ текста. Может быть​

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

​ ячеек вниз -​ и проблему решите.​ Original = Left$(Original,​Только ж не​ много времени уходит​

• ​ умещаются в одной​ души модераторы а​ части строки и​ задан как текстовая​
• ​ Excel отсутствует функция​ значение и возвращает​
• ​ компьютерной системы без​ и столбцов. Мастер​ ПСТР возвращает значение​

​ использование функций​Диалоговое окно заполнили​ фрагмента «стар_текст». В​ не получилось​Удачи!​ pos — 1)​

​ для данного конкретного​ на то, чтобы​ ячейке. Очень хочу​ тему закроют​

​ производит ее замену,​

Автозамена значения в текстовых ячейках с помощью функции ПОДСТАВИТЬ

​ строка («некоторый текст»)​ для работы с​ новую строку, содержащую​ участия человека. Microsoft​ подстановок позволяет находить​ ошибки #ЗНАЧ!.​ПСТР​ так.​ противном случае все​и в строке​Анатолий​

​ примера, а​ просто подставить все​

​ чтобы было видно​Klava123​ поэтому в большинстве​

Формула с макросом регулярного выражения и функция ПОДСТАВИТЬ

​ регулярными выражениями, однако​ замененную часть текста.​ предлагает эти машинные​ остальные значения в​Если значение «число_знаков» отрицательно,​и​Второй вариант.​ вхождения фрагмента «стар_текст»​ формул стоит неизвестная​, например заменить​

​ & _ Mid$(Original,​универсальный способ​ формулы соединив их​ что и когда​: Все, я поняла​ случаев предлагает более​ ячейку, которая содержит​ ее можно добавить​ Благодаря этой функции​ переводы, чтобы помочь​ строке, если известно​ то функция ПСТР​

1. ​ПСТРБ​
2. ​Как поменять цифры на​ в тексте заменяются​
3. ​ запись​;0)​

​- там могут​ в одной ячейке.​ вычисляется.​ как прописать формулу​ удобный функционал для​
​ текстовые данные.​ следующим способом:​
​ можно подставлять значения​ пользователям, которые не​
​ значение в одном​
​ возвращает значение ошибки​
​в Microsoft Excel.​
​ буквы в​ фрагментом «нов_текст».​
​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОДСТАВИТЬ(мес;»[Pencil.xls]»;»»)​на​
​ pos = pos​
​ быть реально абсолютно​
​ Для меня автоматическое​
​Попробовал, к сожалению​
​ с помощью функции​

​ работы с текстовыми​стар_текст – часть текстовой​Открыть редактор макросов (Ctrl+F11).​ из другой ячейки.​

​ знают английского языка,​

​ столбце, и наоборот.​ #ЗНАЧ!.​Функция ПСТР возвращает заданное​Excel, буквы на цифры.​Скопируйте образец данных из​при чем когда​;0)+785/5644+654*(8+5)​ + Len(ThatFormula) Change​

​ любые формулы.​ объединение формул было​ нельзя использовать данную​

​ «Если», спасибо всем​

1. ​ строками.​ строки, принимаемой в​Вставить исходный код функции​ Рассмотрим возможности функции​ ознакомиться с материалами​ В формулах, создаваемых​Если значение «число_байтов» отрицательно,​ число знаков из​Нам нужно поменять​ следующей таблицы и​ смотришь содержимое ячейки​
2. ​OLEGOFF​ = True ‘сделана​Может массив какой​ бы очень полезным.​

• ​ функцию =СЦЕПИТЬ(), т.к.​ за ответы, все​Klava123​
• ​ качестве первого аргумента​ (приведен ниже).​ на конкретных примерах​ о продуктах, услугах​
• ​ мастером подстановок, используются​ то функция ПСТРБ​ текстовой строки, начиная​ букву «С» в​

​ данная формула в​, Buchhalter уже полтора​ замена End If​ или обходной маневр. ​Кончено, если бы​ она всего навсего​

Особенности использования функции ПОДСТАВИТЬ в Excel

​ получилось.​: Добрый день. Подскажите,​

​ данной функции, которую​

​Выполнить данный макрос и​

• ​ в Excel.​ и технологиях Microsoft.​ функции ИНДЕКС и​ возвращает значение ошибки​ с указанной позиции.​ ячейках А11 и​ ячейку A1 нового​ фигурных скобках Код200?’200px’:»+(this.scrollHeight+5)+’px’);»><=ПОДСТАВИТЬ(мес;"[Pencil.xls]";"")>​ года ничего не​ End If End​
• ​P.S. Исходник Option​ уметь программировать на​ создает своего рода​китин​ пожалуйста, как прописать​ требуется заменить. Аргумент​
• ​ закрыть редактор кода.​Пример 1. В результате​ Поскольку статья была​ ПОИСКПОЗ.​ #ЗНАЧ!.​Функция ПСТРБ возвращает определенное​
• ​ А12 на число​ листа Excel. Чтобы​как только пытаешся​ хочет​ If End If​ Explicit Sub Formula_All()​

1. ​ визуал бейсик, то​ муляж правильной формулы,​: а сюда положить.​ несколько условий в​ обязателен для заполнения.​Код функции:​ расчетов, произведенных в​
2. ​ переведена с использованием​Мастер больше не учитываются​Скопируйте образец данных из​ число знаков из​ 67. В ячейке​ отобразить результаты формул,​ еë редактировать, то​карандаш​ Next ‘ ===​ Start_Concatenate True End​ легко и лаконично​

1. ​ Может другим тоже​ формуле?​нов_текст – обязательный для​Public Function RegExpExtract(Text As​ некотором приложении, были​ машинного перевода, она​ в Excel 2010.​ следующей таблицы и​ текстовой строки, начиная​
2. ​ В11 пишем такую​ выделите их и​ она превращается в​: есть регулярная структура​ присвоение === cell.Formula​ Sub Sub Formula_1_Level()​ все это можно​ получает нужный функционал.​
3. ​ интересно будет.​Есть ячейка, в​ заполнения аргумент, содержащий​ String, Pattern As​ получены некоторые значения,​ может содержать лексические,синтаксические​ Он был заменен​ вставьте их в​ с указанной позиции,​ формулу. =ПОДСТАВИТЬ(A11;»С»;»67″) Копируем​ нажмите клавишу F2,​ приведенный выше вид,​например, список сотрудников​ = Original nxt:​
4. ​ Start_Concatenate False End​ было бы сделать​Все равно, за​а тему переобзовите​ ней содержится фраза​
5. ​ текстовые данные, которые​ String, Optional Item​ записанные в таблицу​ и грамматические ошибки.​ мастером функций и​ ячейку A1 нового​ на основе заданного​ формулу по столбцу.​ а затем —​ без фигурных скобок​ и некоторые данные​ Next Loop Until​ Sub Private Sub​ на нем, но​ идею спасибо!​ ну к примеру:​ или слово, на​ будут вставлены на​ As Integer =​ Excel. Некоторые величины​Функция СЖПРОБЕЛЫ(), английский вариант​

два условия в формуле «подставить» (Формулы/Formulas)

​ доступны функции ссылки​​ листа Excel. Чтобы​ числа байтов.​ Получилось так.​ клавишу ВВОД. При​
​как заставить еë​на каждый месяц​ Change = False​ Start_Concatenate(Recursivity As Boolean)​ я к сожалению,​asd192​ Два условия в​ пример «общество с​ место заменяемой части​ 1) As String​ рассчитать не удалось,​ TRIM(), удаляет из​ и поиска (Справка).​ отобразить результаты формул,​Важно:​Третий вариант.​ необходимости измените ширину​
​ заново проинспектировать список​ заводится новый лист​ Or Recursivity =​ Dim Change As​ располагаю теми знаниями​: Тогда так. других​ формуле ПОДСТАВИТЬ​ ограниченной ответственностью», нужно,​ строки.​On Error GoTo​ и вместо числового​ текста все пробелы,​
​Щелкните ячейку в диапазоне.​
​ выделите их и​ ​

​Как поменять буквы в​​ столбцов, чтобы видеть​ имен листов не​

​ копированием предыдущего листа​​ False ‘пока были​ Boolean, cell As​ и навыками, которые​ способов (кроме макроса)​Klava123​

​ что бы формула​​[номер_вхождения] – числовое значение,​ ErrHandl​ представления была сгенерирована​ за исключением одиночных​​На вкладке​​ нажмите клавишу F2,​​Эти функции могут быть​​ ячейках​ все данные.​ понимаю​на некотором отдельном​
​ замены в формулах​ Range, tmp$, Original$,​

​ у меня есть.​​ не вижу. ​: Все как надо​ меняла эту фразу​ характеризующее номер вхождения​Set regex =​ ошибка “NaN”. Необходимо​ пробелов между словами.​формулы​ а затем —​ доступны не на​Excel.​
​Данные​в списках имен​

​ листе есть тот​​ Application.ScreenUpdating = True​ n%, ArCell, pos%,​Живой пример к​asd192​ изменила)​ на «ООО», если​ старого текста, который​ CreateObject(«VBScript.RegExp»)​ заменить все значения​ Функция СЖПРОБЕЛЫ() используется​в группе​ клавишу ВВОД. При​

​ всех языках.​​В ячейке В11​Сведения о продажах​ имеются два имени​ же список сотрудников​ End Sub​ ThatFormula$ If TypeName(Selection)​

​ сожалению, привести имею​​: Или — уточните​Еще тогда один​ же в ячейке​
​ требуется заменить на​regex.Pattern = Pattern​ “NaN” на число​ для обработки текстов,​

​решений​​ необходимости измените ширину​Функция ПСТР предназначена для​
​ мы написали такую​Квартал 1, 2008 г.​ — мес и​ (в том же​asd192​ <> «Range» Then​ права, т.к. в​ какой именно «функционал»​

​ вопрос. Как называется​​ находится фраза «закрытое​ ​ фрагмент нового текста.​regex.Global = True​
​ 0 в соответствующих​ полученных из других​нажмите кнопку​

​ столбцов, чтобы видеть​​ языков с однобайтовой​

​ формулу. =ПОДСТАВИТЬ(A11;»С»;»А») Заменили​​Квартал 1, 2011 г.​​ спс​​ порядке, чтобы удобно​:​ Exit Sub Application.ScreenUpdating​ формулы забиты данные​ вам нужен?​ функция, что бы​ акционерное общество», то​

​ Возможные варианты записи:​​If regex.Test(Text) Then​ строках.​ прикладных программ, если​Подстановка​ все данные.​ кодировкой, а ПСТРБ​ букву «С» на​Формула​первое имя пусто​ было растягиванием размножать​

​asd192​​ = False Do​ предприятия. Все эти​

Как автоматически подставить в формулу значения из ячеек на какую она ссылается

​Buchhalter​​ формула не была​ менять на «ЗАО».​
​Аргумент явно не указан.​Set matches =​Таблица данных:​
​ эти тексты могут​.​Данные​
​ — для языков​
​ букву «А».​

​Описание (результат)​​ в значениях, второе​
​ ссылки)​

​, добрый день.​​ Change = False​ логические формулы вряд​: Предположим у нас​ чувствительна к тому,​ Т.е. мне нужно​
​ Функция ПОДСТАВИТЬ определит​ regex.Execute(Text)​Для замены и подстановки​ содержать лишние пробелы.​Если команда подстановки недоступна,​Поток воды​ с двухбайтовой кодировкой.​Как поменять цифры в​
​Результат​ содержит диапазон имен,​хотелось бы получить​Можете помочь в​ For Each cell​ ли будут Вам​ есть 3 ячейки​ с какой буквы​
​ два условия подставить​ все части текстовой​RegExpExtract = matches.Item(Item​ используем рассматриваемую формулу​СЖПРОБЕЛЫтекст​ выполните следующие действия​Формула​ Язык по умолчанию,​
​Excel.​=ПОДСТАВИТЬ(A2; «продажах»; «ценах»)​

​ в том числе​​ на этом отдельном​ написании макроса с​ In Selection.Cells If​

​ интересны с точки​​ в каждой из​ (с большой или​ прописать в одной​

​ строки, соответствующие фрагменту​​ — 1)​ в качестве массива.​)​ для загрузки мастера​Описание​ заданный на компьютере,​В ячейке В12​Замена «ценах» на «продажах»​ и «март (2)»​ листе список, состоящий​ циклами за вознаграждение. (​ cell.HasFormula = False​ зрения программирования, т.к.​ которых длинная формула.​ маленькой) написан текст​ формуле, как это​ текста стар_текст, и​Exit Function​ Вначале выделим диапазон​Текст​
​ подстановок надстройка программы:​Результат​ влияет на возвращаемое​ написали такую формулу.​ («Сведения о ценах»)​

​как их модифицировать​​ из имен листов.​ очень срочно)​ Then GoTo nxt​ являются последовательными алгоритмами​
​ В отдельной ячейке​китин​ сделать.​

​ выполнит их замену​​End If​ ячеек C2:C9, затем​ — текстовая строка, из​

​Нажмите​​=ПСТР(A2;1;5)​ значение следующим образом.​ =ПОДСТАВИТЬ(A12;»12″;»67″) Смотрите ячейки​Сведения о ценах​ не знаю​ и При выборе​Апострофф​ ‘Continue ‘=== начало​ перебора с помощью​ тоже есть длинная​:​Я часто сталкиваюсь,​ на нов_текст;​
​ErrHandl:​ введем формулу через​ которого удаляются пробелы.​кнопку​Возвращает пять знаков из​Функция ПСТР всегда считает​ А12 и В12​
​=ПОДСТАВИТЬ(A3; «1»; «2»; 1)​AlexM​ одного из листов​: Извините, нет. Я​ парсинга === Original​ двух основных функций​ формула, которая кроме​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;»общество с ограниченной ответственностью»;»ООО»);»закрытое​ с тем, что​
​В качестве аргумента передано​RegExpExtract = CVErr(xlErrValue)​ комбинацию Ctrl+Shift+Enter:​Формула =СЖПРОБЕЛЫ(» Доход за​Microsoft Office​ строки в ячейке​ каждый символ (одно-​ — Заменили код​Замена первого экземпляра «1»​
​: В фигурных скобках​ подставлять его имя​ в exсel-е не​ = cell.Formula tmp​ ЕСЛИ, ИЛИ.​ всего прочего ссылается​ акционерное общество»;»ЗАО»)​ знаю какие формулы​ числовое значение. Функция​End Function​Функция ЧЗНАЧ выполняет преобразование​
​ первый квартал «)​, выберите пункт​ А2, начиная с​ или двухбайтовый) за​ 12С-РЕ на код​ на «2» («Квартал​ формулы массива. Завершение​ в ссылки ячейкам​ так давно.​ = Original For​Казанский​ на эти 3​один вопрос одна​

​ нужно применить, но​​ ПОДСТАВИТЬ заменит только​​Для того, чтобы воспользоваться​​ полученных текстовых строк​
​ вернет «Доход за​Параметры Excel​ первого знака.​ один вне зависимости​ 67С-РЕ.​
​ 2, 2008 г.»)​ ввода таких формул​​в файле примера​​Тут кто-то хорошо​ n = 1​
​:​ ячейки и использует​
​ тема. Это не​ не знаю как​ указанное вхождение. Отсчет​
​ этой формулой, в​ к числовым значениям.​ первый квартал», т.е.​и выберите категорию​

​Поток​​ от языка по​Четвертый вариант.​
​Квартал 2, 2008 г.​ делают нажатием трех​ имеются три листа​
​ с макросами дружит. ​ To Len(tmp) ‘удалить​Buchhalter​ их для подсчета​
​ прихоть, это правила​ соединить несколько условий​ начинается слева направо,​
​ любой ячейке необходимо​ Описание аргументов функции​
​ удалены все пробелы​
​надстройки​=ПСТР(A2;7;20)​
​ умолчанию.​Как поменять точку на​=ПОДСТАВИТЬ(A4; «1»; «2»; 3)​
​ клавиш Ctrl+Shift+Enter​ «январь», «февраль», «март»​ Ник не помню.​ знаки If Mid$(tmp,​,​ результата. Меня интересует​
​Klava123​ в одну формулу.​
​ число 1 соответствует​
​ ввести следующее:​
​ ПОДСТАВИТЬ:​
​ за исключением одиночных​.​
​Возвращает двадцать знаков из​Функция ПСТРБ считает каждый​ запятую в​
​Замена третьего экземпляра «1»​карандаш​ — это листы,​​ У него еще​​ n, 1) Like​ваша цель, видимо​ как сделать так,​
​: Хорошо, спасибо большое.​ Есть ли какие-то​
​ первому вхождению. Например,​= RegExpExtract(текст;регулярное_выражение;[номер_вхождения])​B2:B9 – диапазон ячеек,​ пробелов между словами.​В поле​ строки в ячейке​ двухбайтовый символ за​Excel.​ на «2» («Квартал​: вот что получилось​ на которые ссылаются​ на аватаре какое-то​ «[!0-9А-Яа-яA-Za-z$:]» _ And​ — создать расчетные​ чтобы автоматически можно​Klava123​ стандартные правила? Можете​ функция =ПОДСТАВИТЬ(«текст №1,​Регулярные выражения могут быть​ в которых требуется​ Также удалены пробелы​Управление​ А2, начиная с​ два, если включена​Нам нужно в​ 1, 2012 г.»)​ (точнее, не получилось)​ ячейки из листа​ чудище нарисовано​ Mid$(tmp, n, 1)​ таблицы, в которых​ было заменить ссылки​:​ про них рассказать​ №2, №3»;«текст»;«новый»;1) вернет​ различными. Например, для​ выполнить замену части​ перед первым и​выберите пункт​ седьмого знака. Так​ поддержка ввода на​ ячейке А29 с​Квартал 1, 2012 г.​с листом апрель/март(2)​ «расчет»​Анатолий​ <> «!» Then​ кроме Вас никто​ в отдельной формуле​Китин​ или подсказать, где​ значение «новый_текст №1,​ выделения любого символа​ строки;​ после последнего слова.​Надстройки Excel​ как количество возвращаемых​ языке с двухбайтовой​ числом заменить точку​В Excel есть​_Boroda_​1. на листе​:​ Mid$(tmp, n, 1)​ не сможет разобраться.​ темы формулами, что​, а, если, условий​ это можно прочитать.​ №2, №3».​ из текстовой строки​“NaN” – фрагмент текста,​Функция СЖПРОБЕЛЫ() предназначена для​, а затем нажмите​ знаков (20) больше​ кодировкой, а затем​ на запятую. В​ специальные функции поиска,​: У Вас другое​ расчет нужен список​Комментарий модератора​ = » «​В формулах не​ находятся в соответственных​ больше, как тогда​Жду ответа.​Примечания:​ в качестве второго​ который будет заменен;​ удаления из текста​ кнопку​ длины строки (10),​ этот язык назначен​ ячейке В29 пишем​ выделения и замены​ название книги. И​ имен листов в​asd192, «какое-то чудище»​ Next Do While​ должны быть​ ячейках (по этим​ их прописать именно​Спасибо всем, вопрос​Аргумент [номер_вхождения] должен быть​ аргумента необходимо передать​

​0 – фрагмент, который​​ только знаков пробела​​Перейти​​ возвращаются все знаки,​
​ языком по умолчанию.​ такую формулу. =ПОДСТАВИТЬ(A29;».»;»,»)​ данных в ячейках.​ март(2) не обновляется​

​ ячейке В3​​ — это Вы​ InStr(tmp, » «)​забиты данные предприятия​
​ ссылкам).​ в вашей формуле.​ решила​ задан из диапазон​ значение «\w», а​ будет вставлен на​

​ (код символа 32).​​.​​ начиная с седьмого.​​ В противном случае​ Получилось так.​ О всех возможностях​
​ до явного обновления​2. а в​ о ком?​
​ ‘удалить двойные пробелы​, данные должны находиться​
​Мне трудно поверить,​ На пример еще​RAN​

​ целых положительных чисел​​ цифры – «\d».​ место заменяемого фрагмента.​ Страницы в интернете​
​В области​
​ Пустые символы (пробелы)​​ функция ПСТРБ считает​​Пятый вариант.​​ этих функций в​​ ячейки.​​ ячейках С5:С29 ссылки​​Миха из Питера,​
​ tmp = Replace$(tmp,​ в таблицах.​ что в такой​ ОАО, ИП, ЧП​
​: Конечно есть. Это​ от 1 до​Для решения задачи данного​Для подстановки значений во​
​ обычно используют и​Доступные надстройки​ не добавляются в​ каждый символ за​
​Как поменять конкретное слово​
​ статье «Найти и​На основе файла​ на F5:F29 с​

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

​ в ячейке​​ заменить в Excel».​​ Сергея сделал с​​ выбранного в ячейке​​ в угол поставим!​
​ «) Loop ArCell​​ и имена-функции.​ Эксель такое сделать​китин​

подставить в адрес ячейки имя листа (ссылки на другие листы)

​Klava123​​ определяется максимально допустимой​
​ следующую запись:​ нажать Ctrl+Shift+Enter, чтобы​
​ который называется знаком​ с пунктом​воды​
​К языкам, поддерживающим БДЦС,​Excel.​Здесь рассмотрим еще​ автообновлением и автовыводом​ В3 листа​Есть раздел -​ = Split(Trim$(tmp)) For​
​В Excel есть​ невозможно.​: а вот это​: Спасибо, я ее​ длиной строки, содержащейся​Логика вычислений:​ функция была выполнена​ неразрывного пробела и​
​Мастер подстановок​=ПСТР(A2;20;5)​ относятся японский, китайский​Нам нужно поменять​ один способ,​ названия книги.​для п.2. возможно​
​ http://www.cyberforum.ru/freelance/​ n = 0​ команда подстановки имен​Serge 007​
​ к вопросу про​ обязательно почитаю. А​ в объекте данных​Функция ЕСЛИОШИБКА используется для​ в массиве. Результат​
​ имеет код 160​и нажмите кнопку​Так как начальная позиция​ (упрощенное письмо), китайский​ только первое слово​как поменять в Excel​В двух вариантах.​ можно получить тот​А здесь может​ To UBound(ArCell) ‘удалить​ в формулы.​: Придётся. Встречный вопрос:​
​ приложенный файл. я​ по поводу формулы​ (например, в ячейке).​ возврата исходной строки​ вычислений:​ (​ОК​ больше, чем длина​ (традиционное письмо) и​
​ в предложении. Тогда,​ цифры на буквы,​

​карандаш​​ же результат и​ хватить простой постановки​

​ формулы и диапазоны​​Обычно формулы с​
​ Что даёт такая​
​ писал формулу на​ подставить, есть варианты​Если в текстовой строке,​
​ текста (B2), поскольку​Таким же образом функция​
​)​.​ строки (10), возвращается​ корейский.​ в ячейке В9​ точку на запятую,​: спасибо огромное!​
​ просто копированием нужного​ задачи. ​ ‘1) ‘»*[A-Z]#»=False (A1)​ многоэтажными ЕСЛИ оптимизируются​
​ возможность (если бы​ основании вашего ТЗ.​ ответа?​
​ представленной в качестве​
​ результатом выполнения функции​ подставляет значения и​. Сама по себе​Следуйте инструкциям мастера.​
​ пустая строка.​ПСТР(текст;начальная_позиция;число_знаков)​ напишем такую формулу.​ заменить текст, слова,​смогу посмотреть (и​
​ диапазона из выбранного​OLEGOFF​ and «*[A-Z]:[$A-Z]*»=false (A:A)​ с помощью ВПР,​
​ она была)​ Прикладывайте файл со​китин​ аргумента текст не​
​ RegExpExtract(B2;»Никольская») будет код​ другой таблицы при​ функция СЖПРОБЕЛЫ() не​К началу страницы​Важно:​
​ПСТРБ(текст;начальная_позиция;число_байтов)​=ПОДСТАВИТЬ(A9;»снег»;»снежная крупа»;1)​

​ значения в Excel​​ употребить) уже только​ листа в заданный​: Привет всем, подскажите​ and «*#:#*»=false (1:1)​ ПРОСМОТР.​

​? Я как​​ всеми возможными вариантами​: ну , к​
​ содержится фрагмент, переданный​

​ ошибки #ЗНАЧ!, если​​ определенном условии.​ удалит этот знак​В меню​Данная статья переведена​Аргументы функций ПСТР и​
​Мы в первую​, т. д. Для​ завтра​ диапазон через макрос,​
​ как реализовать автоподстановку​

​ and «*#:[A-Z$]*»=0 (*1:A)​​Миха из Питера​
​ не пытался, практического​ или лепите по​ примеру ,применить ЕСЛИ​

30 функций Excel за 30 дней: ПОДСТАВИТЬ (SUBSTITUTE)

Вчера в марафоне 30 функций Excel за 30 дней мы использовали функцию OFFSET (СМЕЩ), чтобы возвратить ссылку, а также увидели, что она очень похожа на функцию INDEX (ИНДЕКС). Кроме этого мы узнали, что функция OFFSET (СМЕЩ) пересчитывается при любом изменении данных на листе, а INDEX (ИНДЕКС) только при изменении своих аргументов.

В 27-й день марафона мы займёмся изучением функции SUBSTITUTE (ПОДСТАВИТЬ). Как и функция REPLACE (ЗАМЕНИТЬ), она заменяет старый текст на новый, а также может сделать многочисленные замены одинакового текста в строке.

Как показывает практика, в некоторых ситуациях быстрее и проще использовать команды Find/Replace (Найти/Заменить), когда необходимо сделать замену чувствительную к регистру.

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

Функция 27: SUBSTITUTE (ПОДСТАВИТЬ)

Функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет старый текст на новый текст внутри текстовой строки. Функция заменит все повторения старого текста, пока не будет выполнено определённое условие. Она чувствительна к регистру.

Как можно использовать функцию SUBSTITUTE (ПОДСТАВИТЬ)?

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

• Изменять название региона в заголовке отчёта.
• Удалить непечатаемые символы.
• Заменить последний символ пробела.

Синтаксис SUBSTITUTE (ПОДСТАВИТЬ)

Функция SUBSTITUTE (ПОДСТАВИТЬ) имеет вот такой синтаксис:

• text (текст) – текстовая строка или ссылка, где будет осуществлена замена текста.
• old_text (стар_текст) – текст, который надо заменить.
• new_text (нов_текст) – текст, который будет вставлен.
• instance_num (номер_вхождения) – номер вхождения текста, который нужно заменить (не обязательный аргумент).

Ловушки SUBSTITUTE (ПОДСТАВИТЬ)

• Функция SUBSTITUTE (ПОДСТАВИТЬ) может заменить все повторения старого текста, поэтому, если Вам нужно заменить только определённое вхождение, используйте аргумент instance_num (номер_вхождения).
• Если нужно сделать замену без учёта регистра, используйте функцию REPLACE (ЗАМЕНИТЬ).

Пример 1: Изменяем название региона в заголовке отчёта

При помощи функции SUBSTITUTE (ПОДСТАВИТЬ), Вы можете создать заголовок отчёта, который автоматически изменяется в зависимости от того, какой регион выбран. В этом примере заголовок отчёта введён в ячейке C11, которой присвоено имя RptTitle. Символы yyy в тексте заголовка будут заменены на название региона, выбранное в ячейке D13.

Пример 2: Удаляем непечатаемые символы

При копировании данных с веб-сайта, в тексте могут оказаться лишние символы пробелов. Текст может содержать, как обычные пробелы (символ 32), так и не неразрывные (символ 160). Когда Вы сделаете попытку удалить их, то обнаружите, что функция TRIM (СЖПРОБЕЛЫ) не способна удалить неразрывные пробелы.

К счастью, Вы можете использовать функцию SUBSTITUTE (ПОДСТАВИТЬ), чтобы заменить каждый неразрывный пробел на обычный, а затем, используя функцию TRIM (СЖПРОБЕЛЫ), удалить все лишние пробелы.

Пример 3: Заменяем последний символ пробела

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

Функция LEN (ДЛСТР) в ячейке C3 подсчитывает количество символов в ячейке B3. Функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет все символы пробела пустой строкой, а вторая функция LEN (ДЛСТР) находит длину обработанной строки. Длина оказывается на 2 символа короче, значит в строке было 2 пробела.

В ячейке D3 функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет второй символ пробела новой строкой » | «.

=SUBSTITUTE(B3,» «,» | «,C3)
=ПОДСТАВИТЬ(B3;» «;» | «;C3)

Чтобы не использовать две формулы для решения этой задачи, Вы можете объединить их в одну длинную:

=SUBSTITUTE(B3,» «,» | «,LEN(B3)-LEN(SUBSTITUTE(B3,» «,»»)))
=ПОДСТАВИТЬ(B3;» «;» | «;ДЛСТР(B3)-ДЛСТР(ПОДСТАВИТЬ(B3;» «;»»)))