Как изменить значение ячейки в excel по условию

Excel замена текста в ячейках по условию

​Смотрите также​ найдем если 4​ и =ВПР()​ 0 To UBound(Масс_1)​Mark1976​ будет назван «1»,​Тщетно пытался найти​ — 185255;​ можно работать с​Функция «Выделить группу ячеек…»​ молоку), смотрите в​ в Экселе​ снятия галочек, можно​Замена значения будет выполнена,​ переход по пункту​

​Закончив, нажмите Закрыть, чтобы​ все, что бы​Поиск и замена в​ последних символа =»А.А.»​Vlad999​cell.Value = _​: Nikitos79, привет. Я​ то макрос срабатывает​ что-то подобное, но​3. «C»1» -​ этим объектом отдельно.​- выделяет ячейки​ статье «Подстановочные знаки​Как видим, программа Microsoft​ указать, учитывать ли​ а пользователь может​«Заменить»​ выйти из диалогового​ увидеть все варианты,​ Excel – это​

Поиск данных в ячейках Excel

​ заменим на Антон​: а почему не​Replace(cell.Value, Масс_1(i), Масс_2(i))​ выделял строки и​ и для этого​

​ не преуспел. Прошу​ авто; | «C»2″​Например, нажимаем в списке​ по предложенному списку​ в Excel».​ Excel предоставляет довольно​ при поиске регистр​ дальше продолжать в​.​

1. ​ окна Найти и​ которые для Вас​ достаточно мощный и​ Андреевич, может же​АНТОН АЛЕКСЕЕВИЧ​
2. ​If cell.Value =​ поэтому у меня​ договора. А можно​ помочь.​ — авто; |​ на фигуру «Овал»​
3. ​ – пустые ячейки,​Если нажмем на​ функциональный и удобный​ букв, искать ли​
4. ​ поисковой выдаче искать​Открывается окно​ заменить.​ отыскал Excel.​ удобный инструмент, который​ быть, что есть​ИЛИ АННА АНТОНОВКА​ _​ не получилось. Выделил​ ли как-то привязаться​
5. ​Имеется таблица. Всю​ «C»3» — моторы;​ и эта фигура​ ячейки с примечаниями​

​кнопку «Заменить»,​ инструмент для поиска​ точное соответствие в​ нужные ему результат​«Найти и заменить»​

​Урок подготовлен для Вас​Закончив поиск, воспользуйтесь кнопкой​ позволяет находить, а​ Иванова А.А., а​Zorgun​

Замена содержимого ячейки в Excel

​Replace(Масс_1(i), «.», «»)​ ячейки и получилось.​ именно к пробелу?​ ее приводить не​ | «C»4» -​ выделилась. Если нажмем​ или с формулами,​то выйдет окно​ и замены данных​ ячейках.​ для повторной процедуры.​во вкладке​ командой сайта office-guru.ru​ Закрыть, чтобы выйти​ при необходимости и​ она Анна Андреевна?​

1. ​: И как это​ Then _​Mark1976​Владимир​ стал, для примера​
2. ​ авто; | «C»5″​ на рисунок глаза,​ т.д. Например, так.​ «Найти и заменить»,​ в таблицах. Если​
3. ​Также, можно указать среди​Можно выполнить автоматическую замену​«Заменить»​Источник: http://www.gcflearnfree.org/office2013/excel2013/7/full​ из диалогового окна​
4. ​ заменять, информацию на​Zorgun​ сделать через =ВПР()​
5. ​cell.Value = Масс_2(i)​: Nikitos79, еще нажимал​: Ну так в​
6. ​ нужны только два​ — моторы;​ расположенного справа от​
   • ​ Мы поставим –​ открытое на закладке​
   • ​ нужно заменить абсолютно​ ячеек какого формата​ нажатием всего одной​. В поле​Автор/переводчик: Антон Андронов​ Найти и Заменить.​

​ «выделить пустые ячейки».​ «Заменить». Рассмотрим пример​
​ все однотипные значения​
​ будет производиться поиск.​

​ кнопки. Для этого​

Замена символов в Microsoft Excel

​«Найти»​Автор: Антон Андронов​Вы можете получить доступ​ рамках данного урока​ первого столбца стоят​Это просто пример.​Next​ все. Поэтому наверное​ было «пробел» указывать.​ «D», начиная с​ файлом excel во​ эта фигура, картинка​Получилось так.​

​ этих функций.​ на конкретное выражение,​

Способы замены символов в Excel

​ Для этого нужно​ после ввода заменяемых​вводим число, слова​Бывают ситуации, когда в​ к команде Найти​ Вы научитесь выполнять​ индивидуальные значения (инициалы),​Добавлено через 16 минут​End Sub​ и не получалось.​ Опять какой он​ ячейки D6 имеются​ вложение.​ скроются.​Теперь эти ячейки можно​Как в Excel заменить​ то это можно​ кликнуть по кнопке​ значений, и значений,​ или символы, которые​

​ документе нужно заменить​ с помощью комбинации​ поиск в заданной​ которые соответствуют индивидуальным​Допустим на первом​_Boroda_​LSV​

Поиск с заменой

​ там у Вас​ договоры. Количество этих​Помогите пожалуйста заменить​Чтобы скрыть все​ окрасить цветом, вставить​ запятую на точку.​ сделать нажатием только​ «Формат» напротив параметра​ на которые производится​ требуется отыскать и​ один символ (или​

​ клавиш Ctrl+F на​​ области документа Excel,​​ значениям второго столбца​ листе у меня​​: Нууу, да, есть​​: Здравствуйте. Помогите, пожалуйста,​​ — 160 или​​ договоров неизвестно и​название категорий​ выделенные объекты, нажимаем​​ в них формулы,​​У нас такая​

​ на кнопку «Скрыть​ текст, символ, т.д.​ таблица.​​ случае, если выборку​​После этого откроется окно,​​«Заменить все»​​«Заменить на»​ другой. Причин может​​Чтобы увидеть дополнительные параметры​​ найденную информацию на​​ листах где будут​​ первом инициалы а​​ было дать нам​​Необходимо найти и​

​, на​ все скрытые объекты,​

​Здесь мы окрасили ячейки.​ А1 и А2​ подробно, то и​​ указать формат ячеек​​Процедура выполняется практически моментально.​

Автоматическая замена

​ количеством данных в​ Анатольевич», другого имени​Пример:​ в файле сделал​ текста (или весь​If Asc(ActiveCell) =​ в столбце «D»​​из колонки​​ «Показать все».​

​ точки. Выделяем эти​ полной мере предоставлена​Единственной настройкой значения для​ быстрота и удобство.​Как видим, в нижней​ шаблона или удалением​ Найти и заменить.​ Excel, иногда достаточно​ нет.​А.А. Андрей Анатольевич​ в диапазоне H1:I99​ текст) на буквы.​ 32 Then..​ не может быть​»B»​Чтобы удалить выделенные​

​Текст написали в​ ячейки. Вызываем диалоговое​ в данном табличном​

Дополнительные параметры

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

​Бывают случаи, когда допущена​ трудно отыскать какую-то​В файле сделал пример​А.Б. Андрей Борисович​

​ окно «Найти и​ процессоре.​ все тот же​ в том, что​ кнопки замены –​ как быстро заменить​ ошибка, повторяющаяся во​ конкретную информацию. И,​ с инициалами и​А.В. Антон Вячеславович.​ так написать​ заменить на буквенный​: Так я вроде​ заканчивается перечисление всех​колонке «A»​ «Delete».​ нажимаем клавишу «Enter»​ заменить» любым, выше​Автор: Максим Тютюшев​ формат ячеек. Для​ вы должны быть​«Заменить всё»​ символы в программе​ всей книге Excel.​ как правило, такой​ именами стран. На​На втором листе​

​=ЗАМЕНИТЬ(A2;1;ПОИСК(«.»;A2&».»);ВПР(—ЛЕВБ(A2;ПОИСК(«.»;A2&».»)-1);H$1:I$99;2;))​ (с этим я​ и указал пробел​ договоров, то все​.​Другой способ выделения​ и становится активной​

​ перечисленным способом. На​Многие знают функцию​ выбора формата вставляемого​ уверены, что введенные​

​и​ Microsoft Excel.​ Например, неправильно написано​ поиск занимает очень​ листе1 два столбца​ у меня только​LSV​ криво, но справился),​

​Извиняюсь, если неправильно​ ячейки в столбце​MrRuslanBB​ сразу всех картинок,​ другая​ закладке «Найти» ставим​в​ значения жмем на​ символы нуждаются в​«Заменить»​

​Скачать последнюю версию​ чье-то имя или​ много времени. Excel​​ в первом инициалы​​ инициалы. Вот я​: Спасибо,​ задача усложняется тем,​ выразился​ будут пустыми. Но​

​: Для конкретного примера​ ячеек, несмежных ячеек,​выделенная​ точку в поисковой​Excel «Найти и выделить».​ одноименную кнопку напротив​ замене во всех​, и кнопки поиска​

​ слово или фразу​ поиска. Вы можете​

​ На листе2 надо​ заменить на имена.​, Формула ЗАМЕНИТЬ меняет​ через точку может​Но значение может​ невидимый пробел, или​MrRuslanBB​ в статье «Как​Как найти все формулы​ закладку «Заменить». В​ все возможности функции​Открывается точно такое же​ предыдущих способах была​«Найти всё»​ заменить один символ​ на другую. Вы​ с легкостью отыскать​ заменить инициалы на​ Строк 14 тысяч,​

​ часть текста на​

Найти и заменить в Excel.

​ идти номер и​​ содержать невидимый пробел,​​ похожий на него​​: Не получается.​ выделить в Excel​ в Excel.​ поисковой строке ставим​​ «Найти и заменить»​ окно, как и​ возможность найти и​и​ на другой является​ можете воспользоваться инструментом​ любую нужную информацию​ имена.​​ примерно 200 строк​
​ нужный независимо от​ его надо оставить​ или похожий на​
​ символ. В приложенном​Результат прикрепляю во​
​ ячейки, таблицу, др.»​Функция «Формулы» в​
​ точку. Заполнили диалоговое​ -​ в предыдущем случае.​ отобрать нужные ячейки​«Найти далее»​ ручное редактирование ячеек.​ Найти и заменить,​ в рабочей книге​Gotcha​ из них с​ того что это​
​Файл прилагаю.​​ него символ.В общем​​ файле ячейка «D7″​ вложенном файле.​ здесь.​ диалоговом окне функции​ окно так.​как найти и заменить​ Тут устанавливается, как​ для изменения, то​. Жмем на кнопку​ Но, как показывает​ чтобы быстро внести​
​ Excel с помощью​: Вариант как поняла.​ инициалами «А.А.», которые​ за текст.​and_evg​ загуглил как определить​
​ — имеет этот​​P. S. Этот​​Найти, выделить и​ «Найти и выделить».​Нажимаем кнопку «Заменить все».​ слово, цифры, как​ будут отформатированы ячейки​
​ при использовании данного​«Найти далее»​
​ практика, далеко не​ исправления. В нашем​
​ команды Найти, которая​ В столбец F​ я хочу заменить​Мне же нужно​:​ код символа (=КОДСИМВ(. )).​ символ. Задача в​ способ подойдёт для​ заменить данные в​У нас выделились​ Получилось так.​ заменить точку за​ после замены их​ варианта такая возможность​.​
​ всегда этот способ​ примере мы воспользуемся​
​ к тому же​ на листе2​ на «Андрей Анатольевич»​ указать, ЕСЛИ перавя​LSV​ Формула выдает код​
​ том, что нужно​
​ присваивания ID в​ ячейках можно с​ две ячейки, имеющиеся​Заменить можно любой​ запятую в Excel,​ данных. Можно установить​ исключается.​После этого производится поиск​ самый легкий в​ командой Заменить, чтобы​ позволяет еще и​Код =ВПР(A2;list1!A$2:B$12;2;0)​
​ и так далее.​ часть текстав ячейке​
​, А из каких​
​ 1.​ макросом проверить весь​ 19 тыс. ячеек?​ помощью формул, используя​ на листе Excel,​ элемент, букву, число,​
​ как найти ячейки​​ выравнивание, числовые форматы,​Урок: как заменить точку​ по документу искомого​ масштабных таблицах, где​ исправить список адресов​ изменить данные с​
​Вот так?Честно сказать,​Zorgun​ =»68.», то заменить​ соображений ставятся та​По этому коду​
​ столбец «D», начиная​​MrRuslanBB​ другие функции. Пример​ с формулами и​ т. д. Например,​ с формулами, примечаниями,​ цвет ячеек, границы,​ на запятую в​ слова. По умолчанию,​​ количество однотипных символов,​
​ электронной почты.​ помощью инструмента Найти​ я в шоке.​: а А.А. не​ её на «аа»​
​ или иная буква​ все хорошо работает.​
​ с ячейки «D6″​
​: В первом файле​ такого поиска и​ не нужно перебирать​ заменить в предложении​ как выделить сразу​​ и т.д.​​ Экселе​
​ направление поиска производится​ которые требуется изменить,​
​На вкладке Главная нажмите​ и заменить.​ Не ожидал, что​
​ может соответствовать несколько​Не хватает знания​ и их количество?​ Большое спасибо.​ и, если в​ пример был не​ замены значений, цифр,​ все ячейки большой​ запятую на букву​ все картинки, объекты​Также, нажав на соответствующий​Кроме того, существует возможность​ построчно. Курсор останавливается​ может достигать очень​ команду Найти и​В нашем примере мы​ это можно сделать​ значений Андрей Анатольевич​
​ формул в Excel​LSV​​Владимир​ нем есть ячейка​ такой как во​ т. д., смотрите​
​ таблицы. Теперь можно​​ «и».​, т. д.​ пункт из выпадающего​ расширенного поиска и​​ на первом же​ большого количества. Даже​ выделить, а затем​ воспользуемся командой Найти,​ так легко. Спасибо​ или Александр Афанасьевич​LSV​: Каждому цифровому коду​
​: Видимо я с​​ содержащая только символ​ втором. Вариант для​ в статье «Функция​ поменять эти формулы​Внимание!​
​Окно функции «Найти​ списка под кнопкой​ замены по дополнительным​ результате, который совпал.​ на поиск нужных​ из раскрывающегося списка​ чтобы отыскать требуемое​ Вам большое!​ или ещё кто,​
​:​ соответствует буквенный, например​ буквой «г» перепутал.​ похожим на пробел​ второго файла Код​ «ПОДСТАВИТЬ» в Excel».​ или ссылки в​
​Чтобы было расстояние​ и заменить» можно​«Формат»​
​ параметрам.​ Для замены содержимого​ ячеек может быть​ выберите пункт Заменить.​ имя в длинном​И еще один​ всетаки 14 тыс.​_Boroda_​
​ 68=aa​ Они очень похожи.​ (тот, что в​ =ПРОСМОТР(C1;$B$1:$B$2;$A$1:$A$2)​MrRuslanBB​ формулах, т. д.​ между словом «Мама»​ найти несколькими способами.​, можно установить формат​Находясь во вкладке «Заменить»,​

Заменить текст согласно условию

​ ячейки жмем на​​ потрачено значительное количество​
​Появится диалоговое окно Найти​ списке сотрудников.​
​ вопрос: А как​​ строк.​​, Спасибо, большое. То​​and_evg​​Mark1976​
​ файле), то заменить​​MrRuslanBB​​: Добрый день!​​ Не убирая выделение​​ и буквой «и»,​
​1)Сочетание клавиш –​​ идентичный любой выбранной​ в окне «Найти​​ кнопку​
​ времени, не говоря​​ и заменить. Введите​​Если перед использованием команды​​ это формулу можно​файл приложите.​​ что надо, буду​: А мы должны​: Всем привет. Никак​
​ этот пробел на​
​: Пример я создал​У меня есть​ ячеек, нажимаем на​
​ которую мы вставим,​ «Shift» + «F5».​ ячейке на листе,​
​ и заменить» жмем​«Заменить»​ уже о временных​ текст, который Вы​ Найти Вы выделите​ копировать во все​Zorgun​
​ разбираться.​ посредством телепатии об​ не пойму как​
​ текст в ячейке​​ на быструю руку,​​ 3 колонки:​​ кнопку «Найти» и​​ нужно в строке​​2)Сочетание клавиш –​​ достаточно только ее​​ на кнопку Параметры.​​.​​ затратах на редактирование​​ ищете в поле​

​ одну ячейку, то​​ строки одного столбца.​: На всех листах,​

​LSV​​ этом узнать?​
​ использовать команду: найти​ этой же строки,​
​ т. к. исходник​1.​ меняем ссылки. Подробнее,​ «Заменить на:» перед​

​ «Ctrl» + «F».​​ выделить.​Открывается окно дополнительных параметров.​Чтобы продолжить поиск данных,​ каждой из них.​ Найти.​ Excel будет выполнять​

​ Выделить и тянуть​​ где встречаются инициалы​:​LSV​ и заменить. Мне​
​ но из столбца​
​ очень большого размера.​

​Колонка «A»​​ смотрите в статье​​ буквой «и» сделать​​3)На закладке «Главная»​
​Дополнительным ограничителем поиска может​ Оно практически идентично​
​ опять жмем на​К счастью, в программе​Введите текст, на который​ поиск во всем​ вниз за крестик​ «А.А.», мне надо​InExSu​:​
​ надо заменить август​ «J». Ячейки с​Вторая функция помогла)​
​- содержит​ «Поменять ссылки на​

​ один пробел. Точка​ в разделе «Редактирование»​ быть указание диапазона​

Замена текста в ячейке на текст в другой ячейке по условию макросом

​ окну расширенного поиска.​​ кнопку​
​ Эксель имеется инструмент​ требуется заменить найденный,​ листе. А если​ (если понимаете о​
​ заменить на «Андрей​, спасибо, до макросов​and_evg​ на сентябрь только​ любым другим значением,​Благодарю, Вас!​ID категории​ другие листы в​ в предложении стоит​ нажимаем на кнопку​ ячеек, среди которых​ Единственное отличие –​«Найти далее»​ «Найти и заменить»,​ в поле Заменить​ диапазон ячеек, то​ чем я) долго.если​ Анатольевич».​ ещё не дошёл​, Я в таблице​ в ячейках C​ должны остаться без​AlexM​;​ формулах Excel» здесь.​ без пробела, п.​ «Найти и выделить».​ будет производиться поиск​ присутствие блока настроек​. Тем же способом​ который поможет быстро​ на. А затем​ только внутри этого​ слева или справа​Я заменял их​ )​ написал справа что​ и D при​ изменений.​: Доброе утро,​2.​Функции «Примечания», «Условное форматирование»,​ э. нужно поставить​ Если нажмем на​ и замена. Для​

​«Заменить на»​​ меняем следующий результат,​ отыскать нужные ячейки,​ нажмите Найти далее.​ диапазона​ есть данные то​ вручную при помощи​Zorgun​

​ на что менять,​​ этом ячейки A​
​Владимир​
​Fairuza​Колонка «B»​ «Константы», «Проверка данных»​ пробел перед «и».​ стрелку у этой​ этого достаточно просто​.​ и т.д.​ и выполнит в​Если значение будет найдено,​На вкладке Главная воспользуйтесь​ двойной клик по​ «Найти и заменить».​: Добрый день!​ может не совсем​ и B не​: Sub Change() Range(«D6»).Activate​

​!​​- содержит​выделяют ячейки с​Заполним диалоговое окно​ кнопки, то выйдет​ выделить нужный диапазон​Вся нижняя часть окна​Можно найти все удовлетворяющие​
​ них замену символов.​
​ то содержащая его​
​ командой Найти и​
​ маркеру заполнения (это​ Конечно, все делается​

​Подскажите как можно​​ корректно.​ трогать. Я выделяю​​ Do If Len(ActiveCell)​Благодарю Вас, за​
​название категории​
​ вставленными этими функциями.​ так.​ диалоговое окно со​ вручную.​ отвечает за поиск​ запросу результаты сразу.​Простая замена с поиском​ ячейка будет выделена.​
​ выделить, а затем​ если навести на​ как надо, но​

​ одновременно найти слова​​and_evg​ ячейки C и​ = 1 Then​

Найти и заменить только в выделенных ячейках (Формулы/Formulas)

​ ниже напечатанную формулу:​​;​ Например, здесь нашли​Получится так.​ списком функций. Вот​Не забываем в поля​ данных, замену которых​После ввода поискового запроса​ предполагает замену одного​Посмотрите на текст и​ из раскрывающегося списка​ правый нижний угол​ это процесс нудный​ и заменить их​: без таблицы замены,​ D, вызываю найти​ ActiveCell = ActiveCell.Offset(,​Код =ПРОСМОТР(C1;$B$1:$B$2;$A$1:$A$2) Эта​———​ ячейку с примечанием.​Этой же функцией можно​

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

​ только общий ответ.​​ и заменить, пишу​ 6) ActiveCell.Offset(1).Activate Loop​ формула отлично сработала​Эти колонки дополняют друг​Функция «Выбор объектов»​

​ удалить лишние пробелы.​​ разберем подробнее.​ на…» вписать соответствующие​ можно установить, где​ жмем на кнопку​

Замена части текста в столбце (Формулы/Formulas)

​ набора символов (числа,​​ согласны заменить его.​Появится диалоговое окно Найти​
​ сменит вид на​ в том, что​ находятся в заранее​ Формула на основе​ заменить август на​
​ Until IsEmpty(ActiveCell) End​ при замене названий​ друга​выделяет вставленные фигуры,​ Читайте об этом​Нажимаем на​ значения. Когда все​ искать (на листе​«Найти все»​
​ слова, знаки, и​

​Если согласны, тогда выберите​​ и заменить. Введите​​ крестик — это​​ мне это надо​ заготовленным списке.​=ЗАМЕНИТЬ()​ сентябрь, у меня​

​ Sub​​ на артикула, а​;​ рукописный текст, картинки,​

​ статью «Как удалить​​кнопку «Найти»​ настройки указаны, выбираем​ или во всей​

​.​​ т.д.) на другой​​ одну из опций​​ данные, которые необходимо​ и есть маркер)Шок​ сделать в нескольких​Мне надо заменить​InExSu​

​ меняется август во​​Serghey1900​ вот почему то​3.​
​ текст на картинке,​

​ лишние пробелы в​​- выходит диалоговое​ способ выполнения процедуры.​
​ книге) и как​
​Производится поиск всех релевантных​
​ после того, как​ замены:​
​ отыскать. В нашем​ номер два… И​
​ файлах.​ в тексте аббревиатуры​
​: Выделите нужный диапазон​ всех ячейках. Прошу​
​: Спасибо большое.​ при замене артикулов​
​Колонка «C»​
​ т.д. Например, так.​
​ Excel» тут.​ окно «Найти и​
​ Либо жмем на​ искать (по строкам​
​ ячеек. Их список,​
​ эти символы будут​
​Заменить: исправляет по одному​
​ примере мы введем​

​ Вам спасибо большое!Приветствую.​​Файл приложить не​ на слова. Каждый​ и . ​ помощи в данном​Все заработало.​ на названия, -​- содержит​
​Здесь выделено четыре вставленных​Функция «Перейти»​
​ заменить», открытое на​

​ кнопку «Заменить все»,​​ или по столбцам).​​ в котором указано​​ найдены при помощи​ значению зараз.​ имя сотрудника.​ Будте добры, подскажите,​ могу.​
​ раз набирать в​Sub ЗаменаМассомInExSu()​ вопросе.​Для себя попробовал​ вычисляет не корректно.​
​название категории в которой​ объекта. Чтобы так​

​- можно быстро​​ закладке «Найти». Как​​ и замена происходит​​ В отличие от​ значение и адрес​ специального встроенного инструмента​

​Заменить все: исправляет все​​Нажмите Найти далее. Если​​ как вышеизложенную формулу​​Vlad999​ полях «найти» и​’для http://www.excelworld.ru/forum/2-34762-1#227565​

Замена слов из заготовленного списка

​Nikitos79​​ разобраться. Код ищет​
​В сообщение во​ лежит товар​ выделить, нажимаем на​ перейти в нужное​ работать с функцией​ автоматически, согласно введенным​
​ обычного поиска, поиск​ каждой ячейки, открывается​ программы.​ варианты искомого текста​ данные присутствуют на​Код =ВПР(A2;list1!A$2:B$12;2;0) дополнить​: Да Вы примерчик​
​ «заменить на» дело​Dim cell As​: Попробуй выделить целиком​ в указанном диапазоне​ вложении прикрепляю файл​

​; — этих позиций​​ кнопку «Выбор объектов»​ место таблицы. Вызвать​

​ «Найти и заменить»,​​ данным, или же​
​ для замены можно​
​ в нижней части​

​Кликаем по кнопке​​ в книге. В​ листе, то они​ для ситуации, когда​
​ сделайте: в каких​
​ утомительное.​
​ Range​ столбцы,​ с ячейки D6​ образец.​ будет в разы​ и ведем нажатой​
​ окно функции «Перейти»​
​ как заменить ссылки​
​ жмем на кнопку​
​ производить исключительно по​
​ окна. Теперь можно​«Найти и выделить»​ нашем примере мы​ будут выделены.​ в ячейке помимо​ столбцах, где находится​Мне надо заменить​Масс_1 = Array(«68.»,​Найти​ до пустого значения​Подскажите пожалуйста в​ больше чем категорий. ​

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

​ исходного значения для​​ этот пресловутый «А.А.»​ «А.А.» на «Андрей​ «17.», «8.», «33.»)​- августа,​ в этом столбце​
​ чем-же дело?​Пример:​ документу, выделяя нужные​ «F5».​ д., смотрите в​ отдельно производим замену​ по тем значениям,​ из ячеек, в​ вкладке​ для экономии времени.​ нажмете на кнопку​
​ переименования, находятся еще​ (вместе с фамилией,​

​ Андреевич», «А.Б.» на​​Масс_2 = Array(«aa»,​Заменить на​ любой договор, в​Пример некорректной работы​1. «A»1» -​ объекты.​Как быстро открыть​
​ статье «Поиск в​ в каждой ячейке​ которые указываются в​ которой мы хотим​«Главная»​Появится диалоговое окно, подтверждающее​ Найти далее, то​ разные данные?​ перед фамилией, после​

​ «Андрей Борисович» и​​ «bb», «ccc», «r»)​сентября, нажимаешь​ названии которого имеется​ формулы.Пробуйте так Код​ авто; | «A»2″​Функция «Область выделения»​ такое окно и​ Excel».​ по тому алгоритму,​ строке формул при​ выполнить замену, и​
​в блоке настроек​ количество замен, которые​ увидите следующий вариант​Gotcha​ нее)​ так далее.​For Each cell​Заменить все​ любой один символ.​

​ =ПРОСМОТР(2;1/(C1=$B$1:$B$1610);$A$1:$A$1610)​​ — моторы;​- появляется список​ работать в нем,​
​Как найти все​

​ о котором писалось​ выделении ячейки. Кроме​ нажать на кнопку​«Редактирование»​ будут сделаны. Нажмите​ поиска. Вы также​
​, без примера трудно​Прикиньте сами: Есть​Zorgun​ In Selection​и готово, попробуй​ То есть, если​Serghey1900​2. «B»1» -​ всех вставленных объектов.​ смотрите в статье​ слова в разных​ уже выше.​ того, тут же,​«Заменить»​. В появившемся после​ ОК для продолжения.​ можете выбрать Найти​ что-то предлагать.​ Иванов А.А. -​: Вариант, таблицу соответствия​For i =​ еще разок)))​ к примеру, договор​
​: Добрый день.​ 1234; | «B»2″​ Выбрав из списка,​ «Горячие клавиши Excel».​ падежах (молоком, молоко,​Урок: Как сделать поиск​​ путем установки или​​.​ этого списке делаем​

Как в excel заменить значения в ячейках которые содержат определенные значения в соседнем столбце?

Ну например, как мне сделать чтобы только там где «od_1» поменять значение «test» на «super».

• Вопрос задан 27 июн. 2022
• 143 просмотра

• Facebook
• Вконтакте
• Twitter

Как вариант — в третьей колонке («С») поставить формулу:
=ЕСЛИ(A1=»od_1″;»super»;B1)
и растянуть её вниз.
Суть формулы. Если в рассматриваемой строке, в ячейке»A1″ указано значение «od_1», то в ячейке «C1» указать «super». Иначе — указать значение из «B1». И так по каждой строке таблицы.
После вычислений, значение столбца «С» можно скопировать и через специальную вставку — вставить только значения вместо столбца «В».
Все.

PS. Если в задании во второй колонке («В») должна быть проверка значения на «test» — формулу легко дополнить вторым условием.

Примеры функции ПОДСТАВИТЬ для замены текста в ячейке 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+»)&»-Н» – новый номер, содержащий исходное значение и символы «-Н».

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

Массовая замена текста формулами

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

А теперь представьте, что к вам такие кривые данные попадают регулярно, т.е. это не единичная история из серии «исправил вручную, забыл», а проблема на регулярной основе и в большом количестве ячеек.

Что же делать? Не заменять же вручную 100500 раз кривой текст на правильный через окошко «Найти и заменить» или нажимая Ctrl + H ?

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

К сожалению, при очевидной распространенности подобной задачи, в Microsoft Excel не существует простых встроенных способов для её решения. Для начала, давайте разберёмся, как это делать формулами, без привлечения «тяжелой артиллерии» в виде макросов на VBA или Power Query.

Случай 1. Массовая полная замена

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

Предположим, что у нас есть две таблицы:

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

• Обе таблицы преобразованы в динамические («умные») с помощью сочетания клавиш Ctrl + T или командой Вставка — Таблица (Insert — Table) .
• На появившейся вкладке Конструктор (Design) первой таблице присвоено имя Данные , а второй таблице-справочнику — Замены .

Чтобы объяснить логику формулы зайдём чуть издалека.

Взяв в качестве примера первую компанию из ячейки A2 и забыв временно про остальные компании, попробуем определить какой именно вариант из столбца Найти там встречается. Для этого выделим любую пустую ячейку в свободной части листа и введём туда функцию НАЙТИ (FIND) :

Эта функция определяет входит ли заданная подстрока (первый аргумент — все значения из столбца Найти) в исходный текст (первая компания из таблицы данных) и должна вывести на выходе либо порядковый номер символа, начиная с которого текст был найден, либо ошибку если подстрока не обнаружена.

Хитрость тут в том, что поскольку первым аргументом мы указали не одно, а несколько значений — эта функция будет возвращать в качестве результата тоже не одно значение, а массив из 3 элементов. Если у вас не последняя версия Office 365 с поддержкой динамических массивов, то после ввода этой формулы и нажатия на Enter вы этот массив увидите прямо на листе:

Если же у вас предыдущие версии Excel, то после нажатия на Enter мы увидим только первое значение из массива результатов, т.е. ошибку #ЗНАЧ! (#VALUE!) .

Пугаться не стоит 🙂 На самом деле наша формула работает и увидеть весь массив результатов всё равно можно, если выделить введённую функцию в строке формул и нажать клавишу F9 (только не забудьте потом нажать Esc , чтобы вернуться обратно к формуле):

Полученный массив результатов означает, что в исходном кривом названии компании (ГК Морозко ОАО) из всех значений в столбце Найти нашлось только второе (Морозко) , причём начиная с 4-го по счёту символа.

Теперь добавим к нашей формуле функцию ПРОСМОТР (LOOKUP) :

У этой функции три аргумента:

1. Искомое значение — можно использовать любое достаточно большое число (главное, чтобы оно превышало длину любого текста в исходных данных)
2. Просматриваемый_вектор — тот диапазон или массив, где мы ищем искомое значение. Здесь это введённая ранее функция НАЙТИ, возвращающая массив <#ЗНАЧ!:4:#ЗНАЧ!>
3. Вектор_результатов — диапазон, откуда мы хотим вернуть значение, если искомое значение найдено в соответствующей ячейке. Здесь это правильные названия из столбца Заменить нашей таблицы-справочника.

Главная и неочевидная фишка тут в том, что функция ПРОСМОТР при отсутствии точного совпадения всегда ищет ближайшее наименьшее (предыдущее) значение. Поэтому, указав в качестве искомого значения любое здоровенное число (например 9999), мы заставим ПРОСМОТР находить ячейку с ближайшим наименьшим числом (4) в массиве <#ЗНАЧ!:4:#ЗНАЧ!>и выдавать соответствующее ей значение из вектора результатов, т.е. правильное название компании из столбца Заменить.

Второй нюанс заключается в том, что, технически, наша формула является формулой массива, т.к. функция НАЙТИ возвращает в качестве результатов не одно, а массив из трёх значений. Но поскольку функция ПРОСМОТР поддерживает массивы «из коробки», то нам не придётся вводить эту формулу как классическую формулу массива — с помощью сочетания клавиш Ctrl + Shift + Enter . Достаточно будет простого Enter .

Вот и всё. Надеюсь вы ухватили логику.

Осталось перенести готовую формулу первую ячейку B2 столбца Исправлено — и наша задача решена!

Само-собой, с обычными (не умными) таблицами эта формула тоже замечательно работает (только не забудьте про клавишу F4 и закрепление соответствующих ссылок):

Случай 2. Массовая частичная замена

Этот случай чуть похитрее. Снова имеем две «умных» таблицы:

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

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

Готовая формула будет выглядеть так (для удобства восприятия я разделил её на насколько строк с помощью Alt + Enter ):

Основную работу здесь выполняет стандартная Excel’евская текстовая функция ПОДСТАВИТЬ (SUBSTITUTE) , у которой 3 аргумента:

1. Исходный текст — первый кривой адрес из столбца Адрес
2. Что ищем — тут мы используем трюк с функцией ПРОСМОТР(LOOKUP) из предыдущего способа, чтобы вытащить значение из столбца Найти, которое входит как фрагмент в кривой адрес.
3. На что заменить — аналогичным образом находим соответствующее ему правильное значение из столбца Заменить.

Вводить эту формулу с Ctrl + Shift + Enter здесь тоже не нужно, хотя она и является, по-сути, формулой массива.

И хорошо видно (см. ошибки #Н/Д на предыдущей картинке), что такая формула, при всей её элегантности, обладает и парой недостатков:

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

Не идеально и, местами, громоздко, но гораздо лучше, чем однообразная замена вручную, правда? 🙂

В следующей статье разберёмся, как реализовать подобную массовую подстановку с помощью макросов и Power Query.