Skip to content
В статье объясняется на примерах как работают функции Excel ЗАМЕНИТЬ (REPLACE в английской версии) и ПОДСТАВИТЬ (SUBSTITUTE по-английски). Мы покажем, как использовать функцию ЗАМЕНИТЬ с текстом, числами и датами, а также как вложить несколько функций ЗАМЕНИТЬ или ПОДСТАВИТЬ в одну формулу.
Функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ используются для замены одной буквы или части текста в ячейке. Но делают они это немного по-разному. Об этом и поговорим далее.
Как работает функция ЗАМЕНИТЬ
Функция ЗАМЕНИТЬ позволяет заместить слово, один или несколько символов в текстовой строке другим словом или символом.
ЗАМЕНИТЬ(старый_текст; начальная_позиция; число_знаков, новый_текст)
Как видите, функция ЗАМЕНИТЬ имеет 4 аргумента, и все они обязательны для заполнения.
- Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором вы хотите поменять некоторые символы.
- Начальная_позиция — позиция первого символа в старый_текст, начиная с которого вы хотите сделать замену.
- Число_знаков — количество символов, которые вы хотите заместить новыми.
- Новый_текст – текст замены.
Например, чтобы исправить слово «кит» на «кот», следует поменять вторую букву в слове. Вы можете использовать следующую формулу:
=ЗАМЕНИТЬ(«кит»;2;1;»о»)
И если вы поместите исходное слово в какую-нибудь ячейку, скажем, A2, вы можете указать соответствующую ссылку на ячейку в аргументе старый_текст:
=ЗАМЕНИТЬ(А2;2;1;»о»)
Примечание. Если аргументы начальная_позиция или число_знаков отрицательные или не являются числом, формула замены возвращает ошибку #ЗНАЧ!.
Использование функции ЗАМЕНИТЬ с числами
Функция ЗАМЕНИТЬ предназначена для работы с текстом. Но безусловно, вы можете использовать ее для замены не только букв, но и цифр, являющихся частью текстовой строки, например:
=ЗАМЕНИТЬ(A1; 9; 4; «2023»)
Обратите внимание, что мы заключаем «2023» в двойные кавычки, как вы обычно делаете с текстовыми значениями.
Аналогичным образом вы можете заменить одну или несколько цифр в числе. Например формула:
=ЗАМЕНИТЬ(A1;3;2;»23″)
И снова вы должны заключить значение замены в двойные кавычки («23»).
Примечание. Формула ЗАМЕНИТЬ всегда возвращает текстовую строку, а не число. На скриншоте выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B1 и сравните его с исходным числом, выровненным по правому краю в A1. А поскольку это текст, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в статье Как преобразовать текст в число.
Как заменить часть даты
Как вы только что видели, функция ЗАМЕНИТЬ отлично работает с числами, за исключением того, что она возвращает текстовую строку 
Помните, что во внутренней системе Excel даты хранятся в виде чисел. Поэтому нельзя пытаться заменить часть даты, работая с ней как с текстом.
Например, у вас есть дата в A3, скажем, 15 июля 1992г., и вы хотите изменить «июль» на «май». Итак, вы пишете формулу ЗАМЕНИТЬ(A3; 4; 3; «Май»), которая предписывает Excel поменять 3 символа в ячейке A3, начиная с четвертого. Мы получили следующий результат:
Почему так? Потому что «15-июл-92» — это только визуальное представление базового серийного номера (33800), представляющего дату. Итак, наша формула замены заменяет цифры начиная с четвертой (а это два нуля) в указанном выше числе на текст «Май» и возвращает в результате текстовую строку «338Май».
Чтобы заставить функцию ЗАМЕНИТЬ правильно работать с датами, вы должны сначала преобразовать даты в текстовые строки, используя функцию ТЕКСТ. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент старый_текст функции ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(ТЕКСТ(A3; «дд-ммм-гг»); 4; 3; «Май»)
Помните, что результатом приведенной выше формулы является текстовая строка, и поэтому это решение работает только в том случае, если вы не планируете использовать измененные даты в своих дальнейших расчетах. Если вам нужны даты, а не текстовые строки, используйте функцию ДАТАЗНАЧ , чтобы преобразовать значения, возвращаемые функцией Excel ЗАМЕНИТЬ, обратно в даты:
=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A3; «дд-ммм-гг»); 4; 3; «Май»))
Как заменить сразу несколько букв или слов
Довольно часто может потребоваться выполнить более одной замены в одной и той же ячейке Excel. Конечно, можно было сделать одну замену, вывести промежуточный результат в дополнительный столбец, а затем снова использовать функцию ЗАМЕНИТЬ. Однако лучший и более профессиональный способ — использовать вложенные функции ЗАМЕНИТЬ, которые позволяют выполнить сразу несколько замен с помощью одной формулы. В этом смысле «вложение» означает размещение одной функции внутри другой.
Рассмотрим следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на привычные нам телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить «123456789» в «123-456-789».
Вставить первый дефис легко. Вы пишете обычную формулу замены Excel, которая заменяет ноль символов дефисом, т.е. просто добавляет дефис на четвёртой позиции в ячейке:
=ЗАМЕНИТЬ(A3;4;0;»-«)
Результат приведенной выше формулы замены выглядит следующим образом:
А теперь нам нужно вставить еще один дефис в восьмую позицию. Для этого вы помещаете приведенную выше формулу в еще одну функцию Excel ЗАМЕНИТЬ. Точнее, вы встраиваете её в аргумент старый_текст другой функции, чтобы вторая функция ЗАМЕНИТЬ обрабатывала значение, возвращаемое первой формулой, а не первоначальное значение из ячейки А3:
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;4;0;»-«);8;0;»-«)
В результате вы получаете номера телефонов в нужном формате:
Аналогичным образом вы можете использовать вложенные функции ЗАМЕНИТЬ, чтобы текстовые строки выглядели как даты, добавляя косую черту (/) там, где это необходимо:
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»)
Кроме того, вы можете преобразовать текстовые строки в реальные даты, обернув приведенную выше формулу ЗАМЕНИТЬ функцией ДАТАЗНАЧ:
=ДАТАЗНАЧ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»))
И, естественно, вы не ограничены в количестве функций, которые вы можете последовательно, как матрёшки, вложить друг в друга в одной формуле (современные версии Excel позволяют использовать до 8192 символов и до 64 вложенных функций в одной формуле).
Например, вы можете попробовать 3 вложенные функции ЗАМЕНИТЬ, чтобы число отображалось как дата и время:
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»);9;0;» «);12;0;»:»)
Как заменить текст в разных местах
До сих пор во всех примерах мы имели дело с простыми задачами и производили замены в одной и той же позиции в каждой ячейке. Но реальные задачи часто бывают сложнее. В ваших рабочих листах заменяемые символы могут не обязательно появляться в одном и том же месте в каждой ячейке, и поэтому вам придется найти позицию первого символа, начиная с которого нужно заменить часть текста. Следующий пример продемонстрирует то, о чем я говорю.
Предположим, у вас есть список адресов электронной почты в столбце A. И название одной компании изменилось с «ABC» на, скажем, «BCA». Изменилось и название их почтового домена. Таким образом, вы должны соответствующим образом обновить адреса электронной почты всех клиентов и заменить три буквы в адресах электронной почты, где это необходимо.
Но проблема в том, что имена почтовых ящиков имеют разную длину, и поэтому нельзя указать, с какой именно позиции начинается название домена. Другими словами, вы не знаете, какое значение указать в аргументе начальная_позиция функции Excel ЗАМЕНИТЬ. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию, с которой начинается доменное имя в адресе электронной почты:
=НАЙТИ(«@abc»; A3)
Затем вставьте указанную выше функцию НАЙТИ в аргумент начальная_позиция формулы ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(A3; НАЙТИ(«@abc»;A3); 4; «@bca»)
Примечание. Мы включаем символ «@» в нашу формулу поиска и замены Excel, чтобы избежать случайных ошибочных замен в именах почтовых ящиков электронной почты. Конечно, вероятность того, что такие совпадения произойдут, очень мала, и все же вы можете перестраховаться.
Как вы видите на скриншоте ниже, у формулы нет проблем, чтобы поменять символы в разных позициях. Однако если заменяемая текстовая строка не найдена и менять в ней ничего не нужно, формула возвращает ошибку #ЗНАЧ!:
И мы хотим, чтобы формула вместо ошибки возвращала исходный адрес электронной почты без изменения. Для этого заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A3; НАЙТИ(«@abc»;A3); 4; «@bca»);A3)
И эта доработанная формула прекрасно работает, не так ли?
Заменить заглавные буквы на строчные и наоборот
Еще один полезный пример – заменить первую строчную букву в ячейке на прописную (заглавную). Всякий раз, когда вы имеете дело со списком имен, товаров и т.п., вы можете использовать приведенную ниже формулу, чтобы изменить первую букву на ЗАГЛАВНУЮ. Ведь названия товаров могут быть записаны по-разному, а в списках важно единообразие.
Таким образом, нам нужно заменить первый символ в тексте на заглавную букву. Используем формулу
=ЗАМЕНИТЬ(СТРОЧН(A3);1;1;ПРОПИСН(ЛЕВСИМВ(A3;1)))
Как видите, эта формула сначала заменяет все буквы в тексте на строчные при помощи функции СТРОЧН, а затем первую строчную букву меняет на заглавную (прописную).
Быть может, это будет полезно.
Описание функции ПОДСТАВИТЬ
Функция ПОДСТАВИТЬ в Excel заменяет один или несколько экземпляров заданного символа или текстовой строки указанными символами.
Синтаксис формулы ПОДСТАВИТЬ в Excel следующий:
ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_вхождения])
Первые три аргумента являются обязательными, а последний – нет.
- Текст – исходный текст, в котором вы хотите заменить слова либо отдельные символы. Может быть тестовой строой, ссылкой на ячейку или же результатом вычисления другой формулы.
- Старый_текст – что именно вы хотите заменить.
- Новый_текст – новый символ или слово для замены старого_текста.
- Номер_вхождения — какой по счёту экземпляр старый_текст вы хотите заменить. Если этот параметр опущен, все вхождения старого текста будут заменены новым текстом.
Например, все приведенные ниже формулы подставляют вместо «1» – цифру «2» в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:
=ПОДСТАВИТЬ(A3;»1″;»2″;1) — Заменяет первое вхождение «1» на «2».
=ПОДСТАВИТЬ(A3;»1″;»2″;2) — Заменяет второе вхождение «1» на «2».
=ПОДСТАВИТЬ(A3;»1″;»2″) — Заменяет все вхождения «1» на «2».
На практике формула ПОДСТАВИТЬ также используется для удаления ненужных символов из текста. Вы просто меняете их на пустую строку “”.
Например, чтобы удалить пробелы из текста, замените их на пустоту.
=ПОДСТАВИТЬ(A3;» «;»»)
Примечание. Функция ПОДСТАВИТЬ в Excel чувствительна к регистру . Например, следующая формула меняет все вхождения буквы «X» в верхнем регистре на «Y» в ячейке A2, но не заменяет ни одной буквы «x» в нижнем регистре.
=ПОДСТАВИТЬ(A3;»Х»;»Y»)
Замена нескольких значений одной формулой
Как и в случае с функцией ЗАМЕНИТЬ, вы можете вложить несколько функций ПОДСТАВИТЬ в одну формулу, чтобы сделать несколько подстановок одновременно, т.е. заменить несколько символов или подстрок при помощи одной формулы.
Предположим, у вас есть текстовая строка типа « пр1, эт1, з1 » в ячейке A3, где «пр» означает «Проект», «эт» означает «этап», а «з» означает «задача». Вы хотите заместить три этих кода их полными эквивалентами. Для этого вы можете написать 3 разные формулы подстановки:
=ПОДСТАВИТЬ(A3;»пр»;»Проект «)
=ПОДСТАВИТЬ(A3;»эт»;»Этап «)
=ПОДСТАВИТЬ(A3;»з»;»Задача «)
А затем вложить их друг в друга:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3;»пр»;»Проект «); «эт»;»Этап «);»з»;»Задача «)
Обратите внимание, что мы добавили пробел в конце каждого аргумента новый_текст для лучшей читабельности.
Другие полезные применения функции ПОДСТАВИТЬ:
- Замена неразрывных пробелов в ячейке Excel обычными
- Убрать пробелы в числах
- Удалить перенос строки в ячейке
- Подсчитать определенные символы в ячейке
Что лучше использовать – ЗАМЕНИТЬ или ПОДСТАВИТЬ?
Функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ очень похожи друг на друга в том смысле, что обе они предназначены для подмены отдельных символов или текстовых строк. Различия между двумя функциями заключаются в следующем:
- ПОДСТАВИТЬ замещает один или несколько экземпляров данного символа или текстовой строки. Итак, если вы знаете тот текст, который нужно поменять, используйте функцию Excel ПОДСТАВИТЬ.
- ЗАМЕНИТЬ замещает символы в указанной позиции текстовой строки. Итак, если вы знаете положение заменяемых символов, используйте функцию Excel ЗАМЕНИТЬ.
- Функция ПОДСТАВИТЬ в Excel позволяет добавить необязательный параметр (номер_вхождения), указывающий, какой по счету экземпляр старого_текста следует заместить на новый_текст.
Вот как вы можете заменить текст в ячейке и использовать функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Надеюсь, эти примеры окажутся полезными при решении ваших задач.
Как мы знаем, мы можем использовать функцию «Найти и заменить», чтобы быстро найти определенный символ или строку и заменить их другим в Excel. Но пробовали ли вы когда-нибудь найти и заменить строку только в формулах?
Найти и заменить строку в формулах
Найти и заменить строку в формулах
Предположим, вы хотите найти $ C $ 1 в формулах и заменить его на $ C $ 2, как вы можете это сделать?
1. Выберите диапазон или рабочий лист, который вы используете, нажмите Ctrl + G включить Перейти к диалоговое окно, нажмите Особый.
2. в Перейти к специальному диалог, проверьте Формулы опцию.
3. Нажмите OK. Теперь все формулы выбраны.
4. Нажмите Ctrl + H для отображения Найти и заменить диалог, в Найти то, что текстовое поле введите строку, которую вы хотите найти, и введите строку, которую вы хотите использовать в Заменить текстовое окно.
5. Нажмите Заменить всепоявится диалоговое окно с напоминанием о количестве замен, и все $ C $ 1 были заменены на $ C $ 2.
6. Нажмите OK > Закрыть закрыть диалоги.
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (0)
Оценок пока нет. Оцените первым!
|
Как при копировании формулы менять только столбец |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Вот я балда! Всё проще гораздо оказалось! Щёлкнула мышкой на нужной ячейке (в столбце, который нужно заполнить), в строке формул =ТРАНСП , перешла на нужный лист и просто выделила диапазон, щёлкнула ентер. Всё. итог: 
ЗАМЕНИТЬ, ЗАМЕНИТЬБ (функции ЗАМЕНИТЬ, ЗАМЕНИТЬБ)
Смотрите такжеВладимир И характеристики а в ячейке д1 » » & ввиду. 10. Макросом вы
Описание
А сейчас подумалось, As Integer Dim =»68.», то заменить посредством телепатии об
Но сегодня настоятельно старого текста выбрали номера не будет количество символов, начиная
на пересечении заданных Обязательный. Позиция символа в
-
В этой статье описаны: Sub Change() Range(«D6»).Activate отдельных полях для
-
должен быть список x(3) & «Dophin это не сделаете, что и ПОДСТАВИТЬ, Price iLastRow = её на «аа» этом узнать? рекомендуется отказываться от
-
ячейку А2, в удалён, лишь добавятся с указанной позиции. строки и столбца. старом тексте, начиная синтаксис формулы и
-
Do If Len(ActiveCell) отбора. Тогда точно значений соответствующий столбцу » & Target: тип того? только можно формулой. и ЗАМЕНИТЬ - Cells(Rows.Count, 2).End(xlUp).Row ForНе хватает знанияLSV данной функции так качестве нач_поз установили
2 дефиса. Чтобы заменить определённую25-й день марафона мы с которого требуется использование функций
Синтаксис
= 1 Then
не поставишь такого
таблице 1 для & » «
-
McCinlyПоиск и замена те же текстовые i = 2
-
формул в Excel: как она имеет число 5, так=REPLACE(REPLACE(B3,4,0,»-«),8,0,»-«) строку текста в
-
посвятим изучению функции выполнить замену новымЗАМЕНИТЬ ActiveCell = ActiveCell.Offset(, крана, какого нет.
-
диаметра 50 (см. & x(5) &: Хорошая иллюстрация ))) работают не только
-
функции, и какая To iLastRow PriceLSVand_evg
Пример
свои ограничения и как именно с=ЗАМЕНИТЬ(ЗАМЕНИТЬ(B3;4;0;»-«);8;0;»-«) любом месте текстаREPLACE текстом.и 6) ActiveCell.Offset(1).Activate Loop И вводить не пример) » » & Два знака меняет, с текстом, но
|
из них быстрее/медленнее |
||
|
= Split(Cells(i, 2), |
||
|
: |
||
|
, Я в таблице |
||
|
более требовательна к |
пятой позиции слова |
Урок подготовлен для Вас |
|
оригинала, Вы можете |
(ЗАМЕНИТЬ), которая относитсяЧисло_знаковЗАМЕНИТЬБ Until IsEmpty(ActiveCell) End |
надо. |
|
При введении числа |
x(6) ставишь 100, потом и с формулой. |
— не проверял. |
|
« |
_Boroda_ написал справа что ресурсам в сравнении |
«Молоко» мы символы |
support.office.com
30 функций Excel за 30 дней: ЗАМЕНИТЬ (REPLACE)
командой сайта office-guru.ru использовать функцию к категории текстовых. Обязательный. Число символов вв Microsoft Excel. SubDophin отличающегося от спискаEnd If
50 ,будет уже2. Как вариантMcCinly») For j =, Спасибо, большое. То на что менять, с простым и не берем для
Источник: http://blog.contextures.com/archives/2011/01/26/30-excel-functions-in-30-days-25-replace/SUBSTITUTE Она заменяет определённое старом тексте, которыеФункция ЗАМЕНИТЬ заменяет указанноеSerghey1900: вот пожалуйста. возможных значений нужноApplication.EnableEvents = True
Функция 25: REPLACE (ЗАМЕНИТЬ)
500. решения можно сделать: Не думаю, что 1 To UBound(Price) что надо, буду может не совсем удобным оператором амперсанд.
Как можно использовать функцию REPLACE (ЗАМЕНИТЬ)?
нашего итогового слова,Перевел: Антон Андронов(ПОДСТАВИТЬ), которую мы количество символов текстовой требуется ЗАМЕНИТЬ новым
- число символов текстовой: Спасибо большое.
- Теперь будем менять сигнализировать об этом
- End SubЮрий М два поля: одно в файле на
Синтаксис REPLACE (ЗАМЕНИТЬ)
Cells(i, 3) = разбираться. корректно.Alex Nova
число_знаков установили равным
Автор: Антон Андронов
- рассмотрим позже в строки другим текстом. текстом. строки другой текстовой
- Все заработало. уже диаметр? )) например условным форматированием
- 911old: Если отталкиваться от содержит «что менять»,
- скорость повлияет «Сцепить». Cells(i, 3) &LSVand_evg
Ловушки REPLACE (ЗАМЕНИТЬ)
: Добрый день. Нужен 2, так какФункция ЗАМЕНИТЬ, входит в рамках нашего марафона.Итак, давайте внимательно посмотримЧисло_байтов строкой.Для себя попробовалSerghey1900 — цвет текста: Да я тоже конкретного примера, то второе «на чтоТам же не
Пример 1: Меняем код города в телефонном номере
Cells(i, 1) &:: без таблицы замены, совет. Необходимо заменить именно это число состав текстовых функцийПри помощи функции информацию и примеры Обязательный. Число байтов старогоФункция ЗАМЕНИТЬ заменяет часть разобраться. Код ищет: Добрый день. красный для поиска 5
там чётко всё
менять".
Пример 2: Заменяем пробел на двоеточие с пробелом
65 тыс ячеек «-» & (UBound(Price)InExSu только общий ответ. ячейки, содержащие определенные не учитывается в MS Excel иREPLACE по функции текста, который требуется текстовой строки, соответствующую в указанном диапазонеТщетно пытался найтиDophin
слова в шифре
обозначено: разделитель пробелы.
Пример 3: Вложенные функции REPLACE (ЗАМЕНИТЬ) для вставки нескольких дефисов
И тогда уже будут обрабатываться. + 1 -, спасибо, до макросов Формула на основе части текста, пустыми новом слове, в предназначена для замены(ЗАМЕНИТЬ) Вы можетеREPLACE ЗАМЕНИТЬБ новым текстом. заданному числу байтов, с ячейки D6 что-то подобное, но: недопонял что надо) использую функцию SubString(a1;» Если про заменяемое прицеплять макрос, аvikttur j) & «:» ещё не дошёл=ЗАМЕНИТЬ()
значениями. То есть,
качестве нового текста
конкретной области текстовой изменить первые три
(ЗАМЕНИТЬ), а если
Нов_текст
другой текстовой строкой.
office-guru.ru
Примеры работы с текстовой функцией ЗАМЕНИТЬ в Excel
до пустого значения не преуспел. Прошу но думаю пока «;5) число, то оно так только функцией.: Так и я & Price(j) Next )
Как работает функция ЗАМЕНИТЬ в Excel?
InExSu в ячейки вида установили функцию ПРАВСИМВ строки, в которой цифры в телефонном у Вас есть Обязательный. Текст, который заменитВажно: в этом столбце помочь. такпредварительно использую еще всегда занимает пятуюvikttur об этом же Next End Subkidalov: Выделите нужный диапазон «ID_8889», содержащие текст с параметрами ячейки находится исходный текст номере, например, когда дополнительные сведения или
символы в старом
Функция заменить в Excel и примеры ее использования
- любой договор, вИмеется таблица. ВсюЮрий М одну ячейку для
- позицию в строке.: Макросом можно, цепляясь :)kidalov: Есть выборка полей. и … «ID», необходимо очистить, А3 и взятием на указанную строку задан новый код
- примеры, прошу делиться тексте.
Эти функции могут быть названии которого имеется ее приводить не: 911old, я вот сжатия лишних пробелов. Может я не за пробелы. Но911old: Восхищен!!! Как васВо втором столбцеSub ЗаменаМассомInExSu() оставив без изменений последних двух символов текста (новый текст). города. В нашем ими в комментариях.Скопируйте образец данных из доступны не на любой один символ. стал, для примера чего не поймуВариант Dophin очень
так задание понял? так ли необходимо?: если я формулу
Как заменить часть текста в ячейке Excel?
можно отблагодарить? выражение, которое имеет’для http://www.excelworld.ru/forum/2-34762-1#227565 строки в целом. «ок».С целью детального изучения случае новый кодФункция следующей таблицы и всех языках. То есть, если нужны только два — всё это
- интересен.Юрий МВариант McCinly не запишу в ячейкуЮрий М
- необходимые параметры дляDim cell As Может кто подскажетДалее нажимаем на кнопку работы данной функции города введён вREPLACE вставьте их вФункция ЗАМЕНИТЬ предназначена для
к примеру, договор столбца. В столбце делается для чего?Буду тестировать.: Вот простенький пример. подходит? то это будет: Я бы логин замены: Range как прописать это «ОК» и получаем рассмотрим один из столбце C, а(ЗАМЕНИТЬ) заменяет символы ячейку A1 нового языков с однобайтовой будет назван «1», «D», начиная с Чтобы набрав текстСпасибо
exceltable.com
Замена части текста в ячейках Excel
DophinЮрий М формула а там поменял ))2:null:315:0:5000;Масс_1 = Array(«68.», в макрос. Спасибо результат: простейших примеров. Предположим откорректированные телефонные номера внутри текста, основываясь листа Excel. Чтобы кодировкой, а ЗАМЕНИТЬБ то макрос срабатывает
ячейки D6 имеются «КМР 101 СVolchok: поправил, работает с: McCinly, Вы полагаете, должен быть текст.Kuzmich30:null:305:0:5000;
CyberForum.ru
Замена части текста в столбце (Формулы/Formulas)
«17.», «8.», «33.»)Step_UAПример 2. Рассмотрим еще
у нас имеется отображаются в столбце на заданном количестве отобразить результаты формул, — для языков
и для этого договоры. Количество этих 10 50 НЗ»: а не проще текстом между 4 что макросом невозможноИ должна быть: Логин не поменяли,50:null:265:0:5000;
Масс_2 = Array(«aa»,
: for each C один небольшой пример. несколько слов в D. символов и начальной выделите их и с двухбайтовой кодировкой.
договора. А можно договоров неизвестно и затем в другой использовать =»блабла»&A1&»ла ла
и 5 пробелом изменить часть строки? связь в оба благодарность повисла в
100:null:245:0:5000; «bb», «ccc», «r») in activesheet.usedrange ‘ Предположим у нас разных столбцах, необходимо=REPLACE(B3,1,3,C3) позиции. нажмите клавишу F2,
Язык по умолчанию, ли как-то привязаться каждый раз может ячейке выбрать некое
ла" и брать
DophinMcCinly направления — меняю
воздухе!
300:null:230:0:5000;
For Each cell .specialcells(xlcelltypeconstants) - без
в ячейках табличного получить новые слова
=ЗАМЕНИТЬ(B3;1;3;C3)Функция
а затем — заданный на компьютере,
именно к пробелу? быть разным. Столбец
число, которое заменит
значение из A1
: прикольная функция): Конечно можно!!!
текст в А1911old
500:null:220:0:5000;
In Selection
значений вычисляемых по
процессора Excel имеются
используя исходные. ДляЧтобы определить начальную позициюREPLACE клавишу ВВОД. При влияет на возвращаемоеВладимир «J» содержит комментарии часть строки? И
McCinlyвот применительно к
Но какую часть
меняется ячейка корректор: Требуется макрос который1000:null:215:0:5000;For i = формулам if c.value столбцы с мужскими данного примера помимо для функции
(ЗАМЕНИТЬ) может заменять необходимости измените ширину значение следующим образом.: Ну так в к договорам. Ячейка
для этого создаются: Только добавьте в
данному примеру строки менять? д1 и наоборот… бы изменял быНеобходимо заменить текст 0 To UBound(Масс_1)
like «*ID*» then фамилиями и именами основной нашей функцииREPLACE символы в текстовой столбцов, чтобы видеть
excelworld.ru
Динамическая замена фрагментов текста в ячейке
Функция ЗАМЕНИТЬ всегда считает примере и нужно
в столбце «D» дополнительные таблицы, именованные макрос, если меняетеЮрий М
| Два символа или | меняю ячейку- корректор | значение в ячейке | - на значение из | cell.Value = _ | c.clear next | сотрудников некоторой фирмы. ЗАМЕНИТЬ используем также |
(ЗАМЕНИТЬ), Вы можете строке, например: все данные. каждый символ (одно- было «пробел» указывать. не может быть | диапазоны, пишутся макросы… текст в А1,: Чтобы не писать три? Или анализировать — меняется текст — текст, вставляя первого столбца. ВReplace(cell.Value, Масс_1(i), Масс_2(i)) LSV Необходимо заменить их функцию ПРАВСИМВ – использовать функцию Изменить код города вДанные или двухбайтовый) за Опять какой он пустой. Вернее, когда А просто, находясь пусть в D1 длинную строку, можно пробелы? А если в ячейке А1. туда вместо одного примере это 489,If cell.Value =: Здравствуйте. Помогите, пожалуйста, буквы в определенных данная функция служитFIND телефонном номере.абвгдеёжзий один вне зависимости там у Вас |
заканчивается перечисление всех в этой ячейке, заносится число. циклом: вставили текст са в варианте числа другое выбираемое Но замена со _ составить формулу.
местах так, чтобы для возврата определенного(НАЙТИ), чтобы обнаружить Заменить пробел на двоеточие2009 от языка по — 160 или договоров, то все нажать F2 иА если такого planetaexcel.ru |
Как изменить текст в ячейке изменяя значение в другой?
Private Sub Worksheet_Change(ByVal пробелом? с формулой ячейку в списке другой следующими правилами: вReplace(Масс_1(i), «.», «»)Необходимо найти и преобразовать их в числа знаков от
определённую строку текста с пробелом.123456
умолчанию. 32. ячейки в столбце
поменять 50 на числа в списке Target As Range)
Да и если а1 нельзя редактировать ячейки.
последнем Then _
заменить в ячейке женские. конца строки текста.
или символ. ВИспользовать вложенную функциюФормула
Функция ЗАМЕНИТЬБ считает каждый???
будут пустыми. Но 20? Или задача нет, то выдаетсяIf Target.Cells.Count > шагать по всемуMcCinlyВладимирменяется на 489-1, предпоследнееcell.Value = Масс_2(i) столбца первую! частьСоздадим на листе рабочей
То есть, например, этом примере мыREPLACEОписание (результат)
двухбайтовый символ за———- значение может содержать
более глабальная? сообщение об ошибке 1 Then Exit заданию (включая комментарии
: Задание: Если вы: А формулой не на 489-2… иNext i текста (или весь книги Excel табличку
у нас есть хотим заменить первый(ЗАМЕНИТЬ) для вставкиРезультат два, если включенаЗамените на:
невидимый пробел, или911old диаметра клапана.
Sub в файле), то введете в ячейку
хотите? к началу 489-7.Next текст) на буквы. со фамилией и
два слова: молоко
пробел, встречающийся в нескольких дефисов.=ЗАМЕНИТЬ(A2;6;5;»*») поддержка ввода наIf Asc(ActiveCell) =
похожий на него: Юрий М, просто24442
If Not Intersect(Target, нужно сделать проверку число, то изменится
vikttur Количество таких заменEnd SubЦифровой код необходимо
именем, как показано и каток, в текстовой строке, наФункцияЗаменяет пять знаков последовательности языке с двухбайтовой 32 Then.. символ. В приложенном
так поменять можноЮрий М Range(«C1»)) Is Nothing на ввод текста
текст в ячейке.: Только, наверное, без в разных ячейках_Boroda_ заменить на буквенный на рисунке:
результате мы должны двоеточие с пробелом.REPLACE
абвгдеёжзий одним знаком кодировкой, а затемSerghey1900 файле ячейка «D7″
но на какое: Dophin, перемудрили мы
Then в А1 тогдаРазбор полета: ссылки:
таблицы может меняться: Нууу, да, есть
(с этим яДалее на этом же
получить слово молоток.=REPLACE(B3,FIND(» «,B3,1),1,»: «)(ЗАМЕНИТЬ) имеет вот *, начиная с этот язык назначен
: Так я вроде — имеет этот число… его надо — ведь можноApplication.EnableEvents = False изменяется D1, а1) Как Excel=ЗАМЕНИТЬ(«КМР 101 С от 1 до немного. Вам нужно криво, но справился), листе рабочей книги=ЗАМЕНИТЬ(B3;НАЙТИ(» «;B3;1);1;»: «) такой синтаксис: шестого знака (е). языком по умолчанию. и указал пробел символ. Задача в брать из таблицы. обойтись без склейки:x = Split(Cells(1, если меняется D1 узнает какую часть 10 20 НЗ
15. было дать нам задача усложняется тем,
подготовим область дляСоздадим на листе рабочей
ФункциюREPLACE(old_text,start_num,num_chars,new_text)абвгд*й В противном случаеИзвиняюсь, если неправильно том, что нужно
На один диаметрx = Split(Cells(1, 1)) тогда меняется А1. строки изменить, когда У»;14;2;D1)Обыскался на форуме, весь список. Примерно что после кода размещения нашего результата книги табличного процессора
REPLACEЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)
=ЗАМЕНИТЬ(A3;3;2;»10″) функция ЗАМЕНИТЬБ считает выразился макросом проверить весь
может быть аж 1))
Cells(1, 1) = Но вопрос опять
вы еще раз=ПОДСТАВИТЬ(«КМР 101 С но ничего подходящего так, как я
через точку может – измененных фамилий
Excel табличку со(ЗАМЕНИТЬ) разрешается вкладыватьold_text
Заменяет последние два знака каждый символ заЦитатаSerghey1900 написал:
столбец «D», начиная
11 значений поэтомуCells(1, 1) =
«» же про выбор
введете другой номер? 10 20 НЗ
не нашел. Помогите в файле сделал идти номер и
и имен. Заполним словами, как показано одну в другую,(старый_текст) – текстовая
(09) числа 2009
один.Но значение может с ячейки «D6″ легче прописать список. Replace(Cells(1, 1), x(4),For i = части строки, какуюНапример: было «ТРУ У»;20;D1) пожалуйста. в диапазоне H1:I99 его надо оставить
ячейки двумя типами
на рисунке:
таким образом в
строка, в которой на 10.К языкам, поддерживающим БДЦС, содержать невидимый пробел, и, если вDophin, я попытался
Target) 0 To 6 нужно менять. Ведь
ЛЯ ЛЯ 40McCinly
Bema
Тогда формулу можно
Файл прилагаю. формул как показаноДалее на листе рабочей исходном тексте можно будут заменены символы.
2010 относятся японский, китайский или похожий на нем есть ячейка сделать но у911old
If i = автор писал раньше ЛА-ЛА-ЛА».: Или так:: Я бы воспользовался
так написать
and_evg на рисунке: книги подготовим область делать множественные замены.
start_num=ЗАМЕНИТЬ(A4;1;3;,»@»)
(упрощенное письмо), китайский него символ.В общем содержащая только символ
меня коряво но: McCinly Вы были 4 Then x(i) в файле, онВвели 100 -
=»КМР 101 С инструментом «Текст по
=ЗАМЕНИТЬ(A2;1;ПОИСК(«.»;A2&».»);ВПР(—ЛЕВБ(A2;ПОИСК(«.»;A2&».»)-1);H$1:I$99;2;)):Обратите внимание! Во второй
для размещения нашего В этом примере(нач_поз) – начальнаяЗаменяет первые три знака (традиционное письмо) и загуглил как определить похожим на пробел смысл понять можно. почти правы 4
= Target и «КМР» хочет стало «ТРУ ЛЯ 10 «&D1&» НЗ столбцам». Разобрать наLSVLSV
формуле мы используем результата – полученного телефонные номера должны позиция старых символов.
последовательности 123456 одним корейский. код символа (=КОДСИМВ(…)). (тот, что вменяю диаметр меняется слово в шифреCells(1, 1) = менять. А вдруг ЛЯ 100 ЛА-ЛА-ЛА». У» составляющие по разделителю: Спасибо,, А из каких оператор «&» для слова «молоток», как содержать дефисы послеnum_chars знаком @.ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст) Формула выдает код файле), то заменить
список доступных значений…. означает диаметр. Cells(1, 1) & заменит на «КМРДПотом ввели 20,Можете вообще хоть «. А пототand_evg соображений ставятся та добавления символа «а»
показано ниже на первой тройки символов(число_знаков) – количество@456
ЗАМЕНИТЬБ(стар_текст;начальная_позиция;число_байтов;нов_текст) 1.
этот пробел наВсем спасибо кто
А 5 слово x(i) & « 2 «, тогда должно стать «ТРУ все слова по уже собирал в, Формула ЗАМЕНИТЬ меняет или иная буква к мужской фамилии, рисунке. Установим курсор и после второй. старых символов.Вчера в марафоне
Аргументы функций ЗАМЕНИТЬ иПо этому коду
текст в ячейке откликнулся…
planetaexcel.ru
Замена текста в ячейке на текст в другой ячейке по условию макросом
— пропускную способность. «
уже не позиция, ЛЯ ЛЯ 20 одному сцепить: одну ячейку с
часть текста на и их количество? чтобы преобразовать ее в ячейке А6 Используяnew_text30 функций Excel за ЗАМЕНИТЬБ описаны ниже. все хорошо работает. этой же строки,McCinlyСвязь одного сNext ни пробелы не ЛА-ЛА-ЛА»!!!=СЦЕПИТЬ(«КМР»;» «;»101″;» С нужными дополнениями. нужный независимо отLSV в женскую. Для и вызовем функциюноль(новый_текст) – текст, 30 днейСтар_текст Большое спасибо. но из столбца: Я бы сделал другим задается в’Cells(1, 1) = подойдут. И поискомНо как Excel «;»10″;D1;»НЗ У»)Kuzmich того что это: Каждому цифровому коду решения данной задачи ЗАМЕНИТЬ:, как количество символов который будет вставленмы использовали функцию Обязательный. Текст, в которомВладимир «J». Ячейки с список «Типов» всех
таблице 1. x(0) & « с конца строки должен понять, чтоvikttur: Sub Price() Dim за текст. соответствует буквенный, например
можно было быЗаполняем функцию аргументами, которые
для замены, мы
вместо старых символов.INDEX требуется заменить некоторые: Видимо я с любым другим значением, клапанов и сгруппировалПоэтому при написании » & x(1) будете менять? нужно изменить именно: Я намеренно не i As LongМне же нужно 68=aa использовать функцию =СЦЕПИТЬ(B3;»а») изображены на рисунке: получим такой результат,
Функция(ИНДЕКС), чтобы вернуть символы. буквой «г» перепутал. должны остаться без их по диаметрам, в шифре «КМР & » «
McCinly
100 на 20,
приводил пример со
Dim iLastRow As указать, ЕСЛИ перавя
and_evg вместо формулы =B3&»а»Выбор данных параметров поясним
что ни одинREPLACE
значение ячейки, расположенной
Начальная_позиция Они очень похожи. изменений. напорам и т.д. 101 С 50″ & x(2) &: Именно это имелось а например не
СЦЕПИТЬ — медленная. Long Dim j часть текстав ячейке
: А мы должны – результат идентичный. так: в качестве из символов телефонного
planetaexcel.ru
(ЗАМЕНИТЬ) заменяет определённое
Наши советы помогут работать с обычными суммами значений в выбранном диапазоне ячеек или сложными вычислениями с десятками аргументов. Главное, что при большом количестве формул их будет легко расположить в нужных местах.
1 Простое протягивание формулы
Это самый простой и привычный для многих пользователей способ распространения формулы сразу на несколько ячеек строки или столбца. Он требует выполнения следующих действий:
- В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
- После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
- Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.
Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.
Способ отличается простотой и высокой скоростью. Но не всегда подходит для больших таблиц. Так, если в столбце несколько сотен или даже тысяч значений, формулу проще растягивать другими способами, чтобы сэкономить время. Один из них — автоматическое копирование, требующее всего лишь двойного клика кнопкой мыши.
2 Быстрое автозаполнение
Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:
- Ввести в верхнюю ячейку формулу, в которой применяются аргументы из соседних столбцов. Нажать кнопку Enter.
- Навести курсор на правый нижний угол, чтобы он приобрел форму черного крестика.
- Кликнуть два раза по нижнему правому углу ячейки. Результатом станет автоматическое распространение формулы по столбцу с соответствующим изменением аргументов.
Стоит отметить, что автоматическое протягивание выполняется только до первой пустой ячейки. И если столбец был прерван, действия придется повторить для следующего диапазоне.
Еще одна особенность такого автоматического копирования формул — невозможность использования для строки. При попытке распространить значение ячейки не вниз, а в сторону, ничего не происходит. С другой стороны, длина строк обычно намного меньше по сравнению со столбцами, которые могут состоять из нескольких тысяч пунктов.
3 Протягивание без изменения ячеек в формуле
Еще один способ позволяет распространять формулы в Excel без изменения некоторых аргументов. Это может понадобиться в тех случаях, когда одно или несколько значений будут содержаться в одной и той же ячейке. Поможет в закреплении формулы специальная функция фиксации ссылок.
Для распределения без изменения адреса ячейки выполняются те же действия, что и при обычном протягивании или автоматическом копировании. Но при вводе формулы следует зафиксировать адреса, которые не будут меняться. Для этого используются символы доллара — $. Если в каждом новом пункте столбца при расчетах используется одна и та же ячейка, значки надо будет поставить и перед номером строки, и перед литерой, которая указывает на колонку. Как в примере: $G$6.
Ставить знак $ перед названием только строки или столбца при распределении функции не имеет смысла. Потому что, когда формула протягивается, в ней автоматически меняются только нужные части аргументов. Для столбцов это будут номера строк, для строк — названия колонок.
4 Простое копирование
Еще один способ представляет собой не совсем протягивание, а копирование. Но только более простое и позволяющее выделить конкретный диапазон, а не доверять такое выделение компьютеру. Процесс распределения требует выполнить следующие действия:
- Записать в одну из крайних ячеек строки или столбца нужную формулу и нажать Enter.
- Скопировать значение функции — с помощью контекстного меню, иконки на панели или комбинации клавиш Ctrl + C.
- Установить курсор в противоположную часть столбца или строки.
- Нажать на клавиши Ctrl + Shift + «Стрелка вверх». Результатом становится выделение нужного диапазона, даже если на этом участке столбца будет несколько сотен или тысяч пунктов.
- Вставить формулу. Самый простой способ сделать это — нажать комбинацию Ctrl + V.
Результатом будет такое же распределение функции по столбцу, как и при использовании способа №2. Но в отличие от него здесь можно выделить только часть диапазона. Или, наоборот, продлить такое протягивание дальше даже при наличии пустых строк. Правда, во втором случае лишнее значение придется удалить вручную.
Эта небольшая хитрость подходит и для распределения вдоль строки. В этом случае вместо комбинации Ctrl + Shift + «Стрелка вверх» придется нажать Ctrl + Shift + «Стрелка влево» (или вправо, если копируемая формула находится в крайнем левом столбце).
5 Протягивание формул в таблице Excel
Распределять формулы можно и в том случае, если данные размещены не на практически бесконечном листе, а в границах таблицы.
Для преобразования в табличную форму достаточно выделить одну из ячеек и нажать комбинацию Ctrl + T, чтобы вызвать диалоговое окно и указать диапазон данных таблицы.
Перед тем, как протянуть формулу в Excel, достаточно всего лишь ввести нужную функцию в самой верхней строчке таблицы и нажать Enter. Способ работает только при отсутствии других значений в столбце с формулой.
Формула автоматически распределяется по колонке. Преимущества способа — скорость, сравнимая с применением макроса. Недостаток — работает он только при использовании табличной формы размещения данных в Excel и позволяет протянуть формулу сразу до конца таблицы, а не до нужной строки.
Читайте также:
- Лучшие веб-камеры для дома и офиса: рейтинг 2021 года=«content_internal_link»>
- Нумерация страниц в Опен Офис: простая инструкция=«content_internal_link»>