Excel функция подставить

Текстовые функции Excel

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

Функция ЛЕВСИМВ

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

Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
количество_знаков – необязательный аргумент. Целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1.

Пример использования:

Формула: =ЛЕВСИМВ(«Произвольный текст»;8) – возвращенное значение «Произвол».

Функция ПРАВСИМВ

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

Пример использования:

Формула: =ПРАВСИМВ(«произвольный текст»;5) – возвращенное значение «текст».

Функция ДЛСТР

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

Синтаксис: =ДЛСТР(текст)

Пример использования:

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])

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

Пример использования:

Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.

Функция ЗАМЕНИТЬ

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

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

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

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

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

Функция ПОДСТАВИТЬ

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

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

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

Пример использования:

Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

Если ли бы последний аргумент был опущен, то результатом бы стала строка «строка, содержащая новый-новый текст».

Функция ПСТР

ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

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

Пример использования:

Из текста, находящегося в ячейке A1 необходимо вернуть последние 2 слова, которые имеют общую длину 12 символов. Первый символ возвращаемой фразы имеет порядковый номер 12.

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Читать еще: Функция объединения ячеек в excel

Функция СЖПРОБЕЛЫ

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

Синтаксис: =СЖПРОБЕЛЫ(текст)

Пример использования:

=СЖПРОБЕЛЫ( » Текст с лишними пробелами между словами и по краям « )

Результатом выполнения функции будет строка: «Текст с лишними пробелами между словами и по краям» .

Функция СЦЕПИТЬ

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

Функция должна содержать не менее одного аргумента

Пример использования:

Функция возвратит строку: «Слово1 Слово2».

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

Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=»Слово1″&» «&«Слово2″».

Функция ПОДСТАВИТЬ() в MS EXCEL

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

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

Исходный_текст — текст или ссылка на ячейку, содержащую текст, в котором заменяется часть строки.
Старый_текст — заменяемый текст.
Новый_текст — текст, на который заменяется старый_текст.
Номер_вхождения — определяет, какое вхождение фрагмента Старый_текст нужно заменить фрагментом. Если этот аргумент определен, то заменяется только заданное вхождение фрагмента Старый_текст. В противном случае все вхождения фрагмента Старый_текст в текстовой строке заменяются фрагментом Новый_текст.

Пусть в ячейке А6 введена строка Продажи (январь), прибыль (январь).

Чтобы заменить оба слова январь, на февраль, запишем формулу (см. файл примера внизу статьи ):

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

Кроме того, функция ПОДСТАВИТЬ() чувствительна к РЕгиСТру. Записав =ПОДСТАВИТЬ(A2; «ЯНВАРЬ»;»февраль») получим строку без изменений Продажи (январь), прибыль (январь), т.к. для функции ПОДСТАВИТЬ() «ЯНВАРЬ» не тоже самое, что «январь».

Если строка содержит слова в разных регистрах, например, Продажи (январь), прибыль (ЯНВАРЬ), то для замены слова январь на февраль можно порекомендовать формулу =ПОДСТАВИТЬ(СТРОЧН(A2); «январь»;»февраль») В результате получим продажи (февраль), прибыль (февраль).

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

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

Исправляем ошибки в тексте с помощью функции ПОДСТАВИТЬ

Одним из примеров применения функции ПОДСТАВИТЬ является наличие дефиса в русскоязычных словах или апострофа перед суффиксом ‘S в английских названиях фирм, которые искажают работу функции ПРОПНАЧ. Данная функция должна только первые буквы в словах изменять на большие, прописные. А в результате в исходном слове где иметься апостроф функция возвращает 2 большие буквы, что искажает результат ее работы:

Дело в том, что текстовая функция ПРОПНАЧ работает по принципу замены всех первых символов на большую букву, которые находятся после символов, которые не соответствуют буквам: . -,’,*,/, и т.д. Поэтому если внутри слова находится символы, которые не являются буквами работа функции ПРОПНАЧ будет искажена.

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

В основе выше указанной формулы лежит функция ПОДСТАВИТЬ, которая требует заполнения 3-х обязательных из 4-х аргументов:

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

Если внимательно присмотреться к формуле целиком, но легко заменить что функция ПОДСТАВИТЬ используется здесь 2 раза. Не только в основе, а и в качестве аргумента для функции ПРОПНАЧ. Поэтому чтобы детально проанализировать данную формулу и не запутаться разобьем ее на 3 части, так как в ней применяется 3 функции.

Сначала функция ПОДСТАВИТЬ используется для замены апострофа на временный текст «zzz». На первый взгляд такой подход выглядит не профессиональным и бессмысленным, но это не так. Чтобы получить правильный результат при работе функции ПОДСТАВИТЬ и при этом не удалять из текста символы отличные от букв, нам придется пойти на небольшую хитрость в Excel. Сохраняя в оригинале наличие всех символов в исходном тексте. Первая часть формулы для второй части вернет такой результат для следующей обработки:

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

Читать еще: Формула в excel больше или равно

Полезный совет! Вместо ввода символа одинарной кавычки апострофа (‘) более читабельно в формуле будет выглядеть функция СИМВОЛ(39), которая возвращает тот же символ по коду таблицы Unicode. О чем свидетельствует код 39 в аргументе функции СИМВОЛ. То есть следующая более читабельная формула возвращает тот же результат:

А после осталось лишь функции ПОДСТАВИТЬ обратно заменить временный текст «zzz» на апостроф «’». Чтобы слова приобрели свой оригинальный вид по наличию тех же символов.

Иногда приходиться находить собственные альтернативные решения в 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 отсутствует функция для работы с регулярными выражениями, однако ее можно добавить следующим способом:

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

Public Function RegExpExtract(Text As String , Pattern As String , Optional Item As Integer = 1) As String
On Error GoTo ErrHandl
Set regex = CreateObject( «VBScript.RegExp» )
regex.Pattern = Pattern
regex. Global = True
If regex.Test(Text) Then
Set matches = regex.Execute(Text)
RegExpExtract = matches.Item(Item — 1)
Exit Function
End If
ErrHandl:
RegExpExtract = CVErr(xlErrValue)
End Function

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

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

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

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

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

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

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

Функция ПОДСТАВИТЬ имеет следующую синтаксическую запись:

текст – обязательный аргумент, характеризующий текстовую строку, в которой необходимо выполнить замену части текста. Может быть задан как текстовая строка («некоторый текст») или ссылка на ячейку, которая содержит текстовые данные.
стар_текст – часть текстовой строки, принимаемой в качестве первого аргумента данной функции, которую требуется заменить. Аргумент обязателен для заполнения.
нов_текст – обязательный для заполнения аргумент, содержащий текстовые данные, которые будут вставлены на место заменяемой части строки.
[номер_вхождения] – числовое значение, характеризующее номер вхождения старого текста, который требуется заменить на фрагмент нового текста. Возможные варианты записи:

Аргумент явно не указан. Функция ПОДСТАВИТЬ определит все части текстовой строки, соответствующие фрагменту текста стар_текст, и выполнит их замену на нов_текст;
В качестве аргумента передано числовое значение. Функция ПОДСТАВИТЬ заменит только указанное вхождение. Отсчет начинается слева направо, число 1 соответствует первому вхождению. Например, функция =ПОДСТАВИТЬ(«текст №1, №2, №3»;«текст»;«новый»;1) вернет значение «новый_текст №1, №2, №3».

Читать еще: Формула в excel если ячейка пустая то равна 0

Примечания:

Аргумент [номер_вхождения] должен быть задан из диапазон целых положительных чисел от 1 до n, где n определяется максимально допустимой длиной строки, содержащейся в объекте данных (например, в ячейке).
Если в текстовой строке, представленной в качестве аргумента текст не содержится фрагмент, переданный в качестве аргумента стар_текст, функция ПОДСТАВИТЬ вернет строку текст без изменений.
Если число вхождений заменяемого фрагмента в обрабатываемой строке меньше, чем числовое значение, переданное в качестве аргумент [номер_вхождения], функция ПОДСТАВИТЬ вернет текстовую строку в исходном виде. Например, аргументы функции («а 1 а 2 а 3»;«а»;«б»;4) вернут строку «а 1 а 2 а 3».
Рассматриваемая функция чувствительная к регистру, то есть строки «Слово» и «слово» не являются тождественными.
Для решения аналогичных задач по замене части символов текстовой строки можно использовать функцию ЗАМЕНИТЬ. Однако, в отличие от функции ПОДСТАВИТЬ, для ее использования необходимо явно указывать позицию начального символа для замены, а также количество символов, которые необходимо заменить. Функция автоматически выполняет поиск указанной части строки и производит ее замену, поэтому в большинстве случаев предлагает более удобный функционал для работы с текстовыми строками.

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

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

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

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

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

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

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

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

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

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

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

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

SUBSTITUTE(text,old_text,new_text,instance_num)
ПОДСТАВИТЬ(текст;стар_текст;нов_текст;номер_вхождения)

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 пробела.