Как установить замещающий текст в экселе

В excel замена слова

​Смотрите также​Создал список замены.​из колонки​ Вам спасибо большое!Приветствую.​Прикиньте сами: Есть​ ?​ («а 1 а​ данной функции, которую​ выделения любого символа​Примечание: в данном примере​ расчетов, произведенных в​=ЗАМЕНИТЬ(B3;1;3;C3)​(ЗАМЕНИТЬ) может заменять​Если согласны, тогда выберите​

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

​ нажмете на кнопку​​ при необходимости и​

​т.е. для функции ЗАМЕНИТЬ()​​Функция ЗАМЕНИТЬ(), английский вариант​ Работает — заменяет.​»B»​
​ Будте добры, подскажите,​​ Иванов А.А. -​​Это просто пример.​​ 2 а 3»;«а»;«б»;4)​ требуется заменить. Аргумент​​ из текстовой строки​​ ПОДСТАВИТЬ также используется​
​ некотором приложении, были​​Чтобы определить начальную позицию​​ символы в текстовой​​ одну из опций​​ Найти далее, то​​ заменять, информацию на​
​ потребовалось вычислить начальную​​ REPLACE(), замещает указанную​Но знаний не​​, соответствующей названию​​ как вышеизложенную формулу​

Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()

​ найдем если 4​Добавлено через 16 минут​​ вернут строку «а​​ обязателен для заполнения.​ в качестве второго​ в массиве Ctrl+Shift+Enter.​ получены некоторые значения,​​ для функции​​ строке, например:​ замены:​

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

​ хватает решить вопрос:​​колонке «A»​​Код =ВПР(A2;list1!A$2:B$12;2;0) дополнить​ последних символа =»А.А.»​Допустим на первом​ 1 а 2​

​нов_текст – обязательный для​
​ аргумента необходимо передать​

​В результате получим:​ записанные в таблицу​REPLACE​Изменить код города в​Заменить: исправляет по одному​ поиска. Вы также​ рамках данного урока​ (10) и его​

​ строки другой строкой​как дополнить формулу​.​ для ситуации, когда​ заменим на Антон​ листе у меня​ а 3».​ заполнения аргумент, содержащий​
​ значение «\w», а​Пример 3. При составлении​​ Excel. Некоторые величины​​(ЗАМЕНИТЬ), Вы можете​ телефонном номере.​ значению зараз.​
​ можете выбрать Найти​
​ Вы научитесь выполнять​
​ длину (6). Это​ текста. «Указанную часть​ для ситуации, когда​MrRuslanBB​ в ячейке помимо​ Андреевич, может же​
​ два столбца, в​Рассматриваемая функция чувствительная к​ текстовые данные, которые​ цифры – «\d».​

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

Использование функции для вставки нового текста в строку

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

Поиск и замена в Excel

​ варианты искомого текста​ увидеть все варианты,​ области документа Excel,​ ПОДСТАВИТЬ() справляется с​ нужно указать начальную​ исходного значения для​ Код =ВПР(C1;$A$1:$B$2;2;0)​ переименования, находятся еще​ Иванова А.А., а​ во втором имена​ строки «Слово» и​ место заменяемой части​ Примера 3 используем​ ошибка: все номера​ представления была сгенерирована​

​(НАЙТИ), чтобы обнаружить​Использовать вложенную функцию​ в книге. В​ которые для Вас​ а также изменять​ задачей гораздо проще.​ позицию и длину​ переименования, находятся еще​MrRuslanBB​ разные данные?​ она Анна Андреевна?​ в хронологическом порядке.​ «слово» не являются​ строки.​ следующую запись:​ домов на улице​ ошибка “NaN”. Необходимо​ определённую строку текста​REPLACE​ нашем примере мы​

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

​ отыскал Excel.​ найденную информацию на​Кроме того, функция ЗАМЕНИТЬ()​ заменяемой части строки.​ всякие разные логически​

​: Не получается.​Gotcha​Zorgun​Пример:​ тождественными.​[номер_вхождения] – числовое значение,​Логика вычислений:​ Никольская должны быть​ заменить все значения​

1. ​ или символ. В​(ЗАМЕНИТЬ) для вставки​ воспользуемся этой опцией​Закончив поиск, воспользуйтесь кнопкой​ требуемое значение.​
2. ​ заменяет по понятным​ Функция используется редко,​ не связанные?​Результат прикрепляю во​, без примера трудно​: В каждой строке​
3. ​А.А. Андрей Анатольевич​Для решения аналогичных задач​ характеризующее номер вхождения​Функция ЕСЛИОШИБКА используется для​
4. ​ записаны как «№№-Н»,​ “NaN” на число​ этом примере мы​ нескольких дефисов.​ для экономии времени.​ Закрыть, чтобы выйти​При работе с большим​ причинам только одно​ но имеет плюс:​Пример исходного содержимого​
5. ​ вложенном файле.​ что-то предлагать.​ первого столбца стоят​А.Б. Андрей Борисович​

​ по замене части​ старого текста, который​ возврата исходной строки​ где №№ -​ 0 в соответствующих​

​ хотим заменить первый​Функция​Появится диалоговое окно, подтверждающее​ из диалогового окна​ количеством данных в​

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

​ вхождение строки, функция​ позволяет легко вставить​ ячейки A2: Турбина​P. S. Этот​MrRuslanBB​ индивидуальные значения (инициалы),​А.В. Антон Вячеславович.​ символов текстовой строки​ требуется заменить на​ текста (B2), поскольку​ номер дома. Как​ строках.​ пробел, встречающийся в​REPLACE​ количество замен, которые​ Найти и Заменить.​

1. ​ Excel, иногда достаточно​ ПОДСТАВИТЬ() может заменить​ в указанную позицию​ для​ способ подойдёт для​
2. ​: Добрый день!​ которые соответствуют индивидуальным​На втором листе​ можно использовать функцию​ фрагмент нового текста.​
3. ​ результатом выполнения функции​ быстро исправить ошибку?​Таблица данных:​ текстовой строке, на​(ЗАМЕНИТЬ) имеет вот​
4. ​ будут сделаны. Нажмите​Вы можете получить доступ​ трудно отыскать какую-то​
5. ​ все вхождения или​ строки новый текст.​Alfa-Romeo​
6. ​ присваивания ID в​У меня есть​ значениям второго столбца​
   • ​ у меня только​ ЗАМЕНИТЬ. Однако, в​
   • ​ Возможные варианты записи:​ RegExpExtract(B2;»Никольская») будет код​В данном случае для​Для замены и подстановки​ двоеточие с пробелом.​ такой синтаксис:​

​ инициалы. Вот я​ отличие от функции​
​Аргумент явно не указан.​
​ ошибки #ЗНАЧ!, если​

​ поиска числовых значений​

30 функций Excel за 30 дней: ЗАМЕНИТЬ (REPLACE)

​ используем рассматриваемую формулу​​=REPLACE(B3,FIND(» «,B3,1),1,»: «)​REPLACE(old_text,start_num,num_chars,new_text)​​Содержимое ячеек будет заменено.​​ с помощью комбинации​​ как правило, такой​ второе и т.д.​)​Заменяемое значение:​

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

​ в качестве массива.​=ЗАМЕНИТЬ(B3;НАЙТИ(» «;B3;1);1;»: «)​ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)​​Закончив, нажмите Закрыть, чтобы​​ клавиш Ctrl+F на​ поиск занимает очень​Поясним на примере.​Исходный_текст​Alfa-Romeo​

Функция 25: REPLACE (ЗАМЕНИТЬ)

​: В первом файле​​Колонка «A»​​ попадаться «А.А.» должно​ заменить на имена.​ использования необходимо явно​ все части текстовой​ найти хотя бы​

Как можно использовать функцию REPLACE (ЗАМЕНИТЬ)?

​ встроенными функциями не​​ Вначале выделим диапазон​​Функцию​old_text​ выйти из диалогового​

• ​ клавиатуре.​ много времени. Excel​
• ​ Пусть в ячейке​ — текст, в котором​
• ​.​​ пример был не​​- содержит​ замениться на «Андрей​

Синтаксис REPLACE (ЗАМЕНИТЬ)

​ Строк 14 тысяч,​​ указывать позицию начального​​ строки, соответствующие фрагменту​ одно вхождение подстроки​

​ удастся. Рациональнее всего​
​ ячеек C2:C9, затем​

• ​REPLACE​​(старый_текст) – текстовая​ окна Найти и​Чтобы увидеть дополнительные параметры​
• ​ предлагает прекрасный инструмент​​А2​ требуется заменить некоторые​
• ​Желаемое итоговое содержимое​​ такой как во​ID категории​
• ​ Анатольевич», другого имени​​ примерно 200 строк​ символа для замены,​ текста стар_текст, и​

Ловушки REPLACE (ЗАМЕНИТЬ)

​ «Никольская» в строке​​ использовать регулярные выражения.​​ введем формулу через​(ЗАМЕНИТЬ) разрешается вкладывать​ строка, в которой​ заменить.​ поиска и замены,​ поиска. Вы можете​введена строка Продажи​ знаки.​​ ячейки B2: Турбина​​ втором. Вариант для​;​ нет.​

Пример 1: Меняем код города в телефонном номере

​ из них с​​ а также количество​​ выполнит их замену​ B2.​ По умолчанию, в​ комбинацию Ctrl+Shift+Enter:​ одну в другую,​ будут заменены символы.​Урок подготовлен для Вас​ нажмите кнопку Параметры​ с легкостью отыскать​ (январь), прибыль (январь).​Нач_поз​ для​

​ второго файла Код​
​2.​

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

​В файле сделал пример​ инициалами «А.А.», которые​​ символов, которые необходимо​​ на нов_текст;​Если результат выполнения сравнения​​ Excel отсутствует функция​​Функция ЧЗНАЧ выполняет преобразование​ таким образом в​start_num​ командой сайта office-guru.ru​ в диалоговом окне​ любую нужную информацию​ Запишем формулы:​ — позиция знака в​

​Альфа-Ромео (Alfa-Romeo)​
​ =ПРОСМОТР(C1;$B$1:$B$2;$A$1:$A$2)​

Пример 3: Вложенные функции REPLACE (ЗАМЕНИТЬ) для вставки нескольких дефисов

​Колонка «B»​​ с инициалами и​​ я хочу заменить​ заменить. Функция автоматически​В качестве аргумента передано​ значений RegExpExtract(B2;»Никольская»)=»Никольская» является​ для работы с​ полученных текстовых строк​ исходном тексте можно​(нач_поз) – начальная​Источник: http://www.gcflearnfree.org/office2013/excel2013/7/full​ Найти и заменить.​ в рабочей книге​​=ЗАМЕНИТЬ(A2;10;6;»февраль»)​​Исходном_тексте​седан.​MrRuslanBB​- содержит​ именами стран. На​ на «Андрей Анатольевич»​ выполняет поиск указанной​ числовое значение. Функция​

​ ИСТИНА, будет выполнена​
​ регулярными выражениями, однако​

​ к числовым значениям.​ делать множественные замены.​
​ позиция старых символов.​
​Автор/переводчик: Антон Андронов​

​Бывают случаи, когда допущена​

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

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

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

​ В этом примере​num_chars​Автор: Антон Андронов​ ошибка, повторяющаяся во​ команды Найти, которая​получим в первом​ знаки заменяются текстом​ 2х листов, где​ на быструю руку,​;​ в первом инициалы​Zorgun​ производит ее замену,​ указанное вхождение. Отсчет​

​ следующим способом:​ ПОДСТАВИТЬ:​ телефонные номера должны​(число_знаков) – количество​Вчера в марафоне​ всей книге Excel.​ к тому же​

​ случае строку Продажи​новый_текст​ List 1 -​ т. к. исходник​———​

• ​ во втором имена.​: а А.А. не​ поэтому в большинстве​ начинается слева направо,​
• ​ текст, содержащий полный​Открыть редактор макросов (Ctrl+F11).​
• ​B2:B9 – диапазон ячеек,​ содержать дефисы после​ старых символов.​

​30 функций Excel за​ Например, неправильно написано​ позволяет еще и​ (февраль), прибыль (январь),​.​ список замены):​

​ очень большого размера.​Эти колонки дополняют друг​ На листе2 надо​ может соответствовать несколько​

​ случаев предлагает более​

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

​ число 1 соответствует​ адрес;​Вставить исходный код функции​ в которых требуется​ первой тройки символов​new_text​ 30 дней​ чье-то имя или​ изменить данные с​ во втором -​Число_знаков​

​Вторая функция помогла)​ друга​

​ заменить инициалы на​ значений Андрей Анатольевич​ удобный функционал для​

​ первому вхождению. Например,​

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

​b. RegExpExtract(B2;»\d+») – формула,​ (приведен ниже).​ выполнить замену части​ и после второй.​(новый_текст) – текст,​мы использовали функцию​ необходимо изменить определенное​ помощью инструмента Найти​ Продажи (февраль), прибыль​ — число знаков в​

​: из примера непонятно,​Благодарю, Вас!​;​ имена.​ или Александр Афанасьевич​ работы с текстовыми​ функция =ПОДСТАВИТЬ(«текст №1,​ выделяющая номер дома​Выполнить данный макрос и​ строки;​ Используя​ который будет вставлен​

1. ​INDEX​
2. ​ слово или фразу​ и заменить.​
3. ​ (февраль).​Исходном_тексте​

​ что все-таки нужно.​

​AlexM​3.​Gotcha​ или ещё кто,​ строками.​
​ №2, №3»;«текст»;«новый»;1) вернет​ из строки с​
​ закрыть редактор кода.​“NaN” – фрагмент текста,​
​ноль​
​ вместо старых символов.​
​(ИНДЕКС), чтобы вернуть​
​ на другую. Вы​В нашем примере мы​
​Записав формулу =ПОДСТАВИТЬ(A2;​, которые заменяются текстом​
​ Нет как «Пример​
​: Доброе утро,​
​Колонка «C»​
​: Вариант как поняла.​
​ всетаки 14 тыс.​

​Zorgun​ значение «новый_текст №1,​ полным адресом;​Код функции:​

​ который будет заменен;​

​, как количество символов​Функция​ значение ячейки, расположенной​ можете воспользоваться инструментом​ воспользуемся командой Найти,​ «январь»;»февраль»;2) получим строку​новый_текст​ исходного содержимого ячейки​

​Fairuza​- содержит​ В столбец F​

1. ​: Добрый день!​ №2, №3».​c. RegExpExtract(B2;»\d+»)&»-Н» – новый​Public Function RegExpExtract(Text As​0 – фрагмент, который​ для замены, мы​REPLACE​ на пересечении заданных​ Найти и заменить,​ чтобы отыскать требуемое​ Продажи (январь), прибыль​
2. ​.​ A1: Турбина для​!​название категории в которой​

• ​ на листе2​файл приложите.​Подскажите как можно​
• ​Примечания:​ номер, содержащий исходное​ String, Pattern As​ будет вставлен на​
• ​ получим такой результат,​(ЗАМЕНИТЬ) заменяет определённое​ строки и столбца.​ чтобы быстро внести​

​ (февраль).​Новый_текст​ Alfa-Romeo седан.» так​Благодарю Вас, за​ лежит товар​Код =ВПР(A2;list1!A$2:B$12;2;0)​

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

​Zorgun​ одновременно найти слова​

​Аргумент [номер_вхождения] должен быть​

​ значение и символы​

• ​ String, Optional Item​ место заменяемого фрагмента.​ что ни один​ количество символов, начиная​25-й день марафона мы​ исправления. В нашем​ списке сотрудников.​Кроме того, функция ПОДСТАВИТЬ()​ — текст, который заменяет​ и «Желаемое итоговое​
• ​ ниже напечатанную формулу:​; — этих позиций​Вот так?Честно сказать,​: На всех листах,​ и заменить их​ задан из диапазон​
• ​ «-Н».​ As Integer =​Для подстановки значений во​ из символов телефонного​ с указанной позиции.​ посвятим изучению функции​
• ​ примере мы воспользуемся​Если перед использованием команды​ чувствительна к РЕгиСТру.​ знаки в​ содержимое ячейки B1:​Код =ПРОСМОТР(C1;$B$1:$B$2;$A$1:$A$2) Эта​

1. ​ будет в разы​ я в шоке.​ где встречаются инициалы​ на слова, которые​ целых положительных чисел​Результат вычислений:​ 1) As String​
2. ​ всех ячейках необходимо​ номера не будет​ Чтобы заменить определённую​REPLACE​ командой Заменить, чтобы​ Найти Вы выделите​ Записав =ПОДСТАВИТЬ(A2; «ЯНВАРЬ»;»февраль»)​Исходном_тексте​ Турбина для Альфа-Ромео​ формула отлично сработала​ больше чем категорий. ​

1. ​ «А.А.», мне надо​ находятся в заранее​ от 1 до​Данный способ может показаться​On Error GoTo​ нажать Ctrl+Shift+Enter, чтобы​ удалён, лишь добавятся​ строку текста в​(ЗАМЕНИТЬ), которая относится​
2. ​ исправить список адресов​ одну ячейку, то​ получим строку без​.​ седан.»​ при замене названий​Пример:​ это можно сделать​
3. ​ заменить на «Андрей​ заготовленным списке.​ n, где n​ громоздким, однако он​ ErrHandl​ функция была выполнена​ 2 дефиса.​ любом месте текста​ к категории текстовых.​ электронной почты.​ Excel будет выполнять​ изменений Продажи (январь),​Функция ПОДСТАВИТЬ() используется, когда​aeroseo​
4. ​ на артикула, а​1. «A»1″ -​ так легко. Спасибо​ Анатольевич».​Мне надо заменить​
5. ​ определяется максимально допустимой​ весьма удобен для​Set regex =​ в массиве. Результат​=REPLACE(REPLACE(B3,4,0,»-«),8,0,»-«)​ оригинала, Вы можете​ Она заменяет определённое​На вкладке Главная нажмите​ поиск во всем​ прибыль (январь), т.к.​ нужно заменить​: Поясняю: формулой получается:​ вот почему то​ авто; | «A»2″​ Вам большое!​Я заменял их​ в тексте аббревиатуры​ длиной строки, содержащейся​ работы с таблицами,​ CreateObject(«VBScript.RegExp»)​ вычислений:​

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

​=ЗАМЕНИТЬ(ЗАМЕНИТЬ(B3;4;0;»-«);8;0;»-«)​​ использовать функцию​
​ количество символов текстовой​ команду Найти и​ листе. А если​ для функции ПОДСТАВИТЬ()​определенный​ «Alfa-Romeo» заменить на​
​ при замене артикулов​ — моторы;​И еще один​ вручную при помощи​ на слова. Каждый​ в объекте данных​ содержащими большое количество​
​regex.Pattern = Pattern​Таким же образом функция​Урок подготовлен для Вас​SUBSTITUTE​ строки другим текстом.​

​ выделить, а затем​​ диапазон ячеек, то​ «ЯНВАРЬ» не тоже​

​текст в текстовой​​ «Альфа-Ромео (Alfa-Romeo)».​
​ на названия, -​
​2. «B»1» -​

​ вопрос: А как​​ «Найти и заменить».​ раз набирать в​ (например, в ячейке).​
​ данных.​
​regex.Global = True​
​ подставляет значения и​ командой сайта office-guru.ru​(ПОДСТАВИТЬ), которую мы​Итак, давайте внимательно посмотрим​ из раскрывающегося списка​ только внутри этого​
​ самое, что «январь».​
​ строке; функция ЗАМЕНИТЬ()​
​Но если в​
​ вычисляет не корректно.​
​ 1234; | «B»2″​ это формулу можно​ Конечно, все делается​ полях «найти» и​Если в текстовой строке,​Функция ПОДСТАВИТЬ имеет следующую​If regex.Test(Text) Then​ другой таблицы при​Источник: http://blog.contextures.com/archives/2011/01/26/30-excel-functions-in-30-days-25-replace/​ рассмотрим позже в​ информацию и примеры​ выберите пункт Заменить.​

​ диапазона​​Функцию ЗАМЕНИТЬ() удобно использовать​ используется, когда нужно​ A2 перед «Alfa-Romeo»​В сообщение во​ — 185255;​ копировать во все​ как надо, но​
​ «заменить на» дело​

​ представленной в качестве​​ синтаксическую запись:​Set matches =​ определенном условии.​Перевел: Антон Андронов​ рамках нашего марафона.​
​ по функции​Появится диалоговое окно Найти​На вкладке Главная воспользуйтесь​ для вставки в​ заменить​ вставить, Например, «Турбина​ вложении прикрепляю файл​3. «C»1» -​ строки одного столбца.​ это процесс нудный​ утомительное.​
​ аргумента текст не​= ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения])​

​ regex.Execute(Text)​​​Автор: Антон Андронов​При помощи функции​REPLACE​ и заменить. Введите​ командой Найти и​ строку нового текста.​
​любой​ для» , то​ образец.​ авто; | «C»2″​ Выделить и тянуть​ и долгий. Проблема​Мне надо заменить​ содержится фрагмент, переданный​Описание аргументов:​

​RegExpExtract = matches.Item(Item​​Пример 2. Провайдер домашнего​Функция ПОДСТАВИТЬ в Excel​REPLACE​(ЗАМЕНИТЬ), а если​ текст, который Вы​ выделить, а затем​ Например, имеется перечень​текст начиная с​ формула не срабатывает.​Подскажите пожалуйста в​ — авто; |​
​ вниз за крестик​ в том, что​ «А.А.» на «Андрей​ в качестве аргумента​текст – обязательный аргумент,​ — 1)​ интернета хранит данные​ выполняет динамическую замену​(ЗАМЕНИТЬ) Вы можете​

​ у Вас есть​​ ищете в поле​ из раскрывающегося списка​ артикулов товаров вида​
​ определенной позиции.​

​Michael_S​ чем-же дело?​ «C»3″ — моторы;​ (если понимаете о​ мне это надо​ Андреевич», «А.Б.» на​
​ стар_текст, функция ПОДСТАВИТЬ​ характеризующий текстовую строку,​Exit Function​ о своих абонентах​ определенной части строки​ изменить первые три​ дополнительные сведения или​ Найти.​ выберите пункт Найти.​ «ID-567(ASD)», необходимо перед​При замене определенного текста​:​Пример некорректной работы​ | «C»4» -​ чем я) долго.если​ сделать в нескольких​ «Андрей Борисович» и​ вернет строку текст​ в которой необходимо​End If​ в таблице Excel.​ на указанное новое​ цифры в телефонном​
​ примеры, прошу делиться​Введите текст, на который​Появится диалоговое окно Найти​ текстом ASD вставить​ функцию ЗАМЕНИТЬ() использовать​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОДСТАВИТЬ(A1;ПРОСМОТР(99;ПОИСК(list1!$A$1:$A$67;A1);list1!$A$1:$A$67);ПРОСМОТР(99;ПОИСК(list1!$A$1:$A$67;A1);list1!$B$1:$B$67))​​ формулы.Пробуйте так Код​​ авто; | «C»5″​ слева или справа​

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

​ файлах.​​ так далее.​
​ без изменений.​ выполнить замену части​
​ErrHandl:​​ Предположим, улица Садовая​​ значение и возвращает​​ номере, например, когда​​ ими в комментариях.​
​ требуется заменить найденный,​​ и заменить. Введите​​ новый текст Micro,​​ неудобно. Гораздо удобнее​​aeroseo​
​ =ПРОСМОТР(2;1/(C1=$B$1:$B$1610);$A$1:$A$1610)​​ — моторы;​ есть данные то​​Файл приложить не​
​Zorgun​​Если число вхождений заменяемого​​ текста. Может быть​​RegExpExtract = CVErr(xlErrValue)​ была переименована в​​ новую строку, содержащую​ задан новый код​Функция​
​ в поле Заменить​
​ данные, которые необходимо​ чтобы получилось «ID-567(MicroASD)».​ воспользоваться функцией ПОДСТАВИТЬ().​
​: Michael_S, Уважаемый, Михаэл,​aeroseo​Идентичный пример прикрепляю​
​ двойной клик по​ могу.​: Вариант, таблицу соответствия​ фрагмента в обрабатываемой​ задан как текстовая​End Function​ Никольскую. Необходимо быстро​
​ замененную часть текста.​ города. В нашем​REPLACE​
​ на. А затем​​ отыскать. В нашем​​ Для этого напишем​​Пусть в ячейке​​ не имел возможности​​: Приветствую. Будте добры,​​ файлом excel во​​ маркеру заполнения (это​​Vlad999​​ и =ВПР()​​ строке меньше, чем​

​ строка («некоторый текст»)​​Для того, чтобы воспользоваться​ произвести замену названия​

​ Благодаря этой функции​​ случае новый код​
​(ЗАМЕНИТЬ) заменяет символы​ нажмите Найти далее.​
​ примере мы введем​ простую формулу:​А2​ выразить благодарность за​

​ посоветуйте.​​ вложение.​ если навести на​: Да Вы примерчик​Vlad999​ числовое значение, переданное​ или ссылка на​

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

​=ЗАМЕНИТЬ(A2;8;0;»Micro»).​​введена строка Продажи​​ ответ. После нашего​​Встала задача: заменить​
​Помогите пожалуйста заменить​ правый нижний угол​
​ сделайте: в каких​: а почему не​ в качестве аргумент​ ячейку, которая содержит​ любой ячейке необходимо​ данных об адресе​ из другой ячейки.​ столбце C, а​
​ на заданном количестве​ то содержащая его​Нажмите Найти далее. Если​
​Поиск и замена в​ (январь). Чтобы заменить​

​ диалога, форум благополучно​ слова используя список​название категорий​

Замена слов из заготовленного списка. Прошу совета. (Формулы/Formulas)

​ ячейки то курсор​​ столбцах, где находится​АНТОН АЛЕКСЕЕВИЧ​
​ [номер_вхождения], функция ПОДСТАВИТЬ​ текстовые данные.​ ввести следующее:​
​ проживания каждого клиента.​ Рассмотрим возможности функции​
​ откорректированные телефонные номера​
​ символов и начальной​ ячейка будет выделена.​
​ данные присутствуют на​ Excel – это​
​ слово январь, на​ завис.​ замены.​в​ сменит вид на​ этот пресловутый «А.А.»​ИЛИ АННА АНТОНОВКА​
​ вернет текстовую строку​стар_текст – часть текстовой​= RegExpExtract(текст;регулярное_выражение;[номер_вхождения])​​Таблица данных:​​ на конкретных примерах​
​ отображаются в столбце​​ позиции.​​Посмотрите на текст и​
​ листе, то они​ достаточно мощный и​ февраль, запишем формулы:​​Вашей формулой сэкономил​​Нашел на полях​
​колонке «C»​ крестик — это​ (вместе с фамилией,​Zorgun​

​ в исходном виде.​​ строки, принимаемой в​Регулярные выражения могут быть​Для выполнения заданного условия​ в Excel.​ D.​Функция​ убедитесь, что Вы​ будут выделены.​ удобный инструмент, который​=ЗАМЕНИТЬ(A2;10;6;»февраль»)​

​ кучу времени.​​ форума формулу:​, на​ и есть маркер)Шок​
​ перед фамилией, после​: И как это​ Например, аргументы функции​ качестве первого аргумента​ различными. Например, для​

​ используем формулу:​​Пример 1. В результате​ ​=REPLACE(B3,1,3,C3)​

​REPLACE​​ согласны заменить его.​Если Вы еще раз​ позволяет находить, а​=ПОДСТАВИТЬ(A2; «январь»;»февраль»)​Большое спасибо.​=ВПР(A2;list1!A$2:B$72;2;0)​
​ID категории​ номер два… И​
​ нее)​

Функция ЗАМЕНИТЬ() в EXCEL

Функция ЗАМЕНИТЬ( ) , английский вариант REPLACE(), замещает указанную часть знаков текстовой строки другой строкой текста. «Указанную часть знаков» означает, что нужно указать начальную позицию и длину заменяемой части строки. Функция используется редко, но имеет плюс: позволяет легко вставить в указанную позицию строки новый текст.

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

ЗАМЕНИТЬ ( исходный_текст ; нач_поз ; число_знаков ; новый_текст )

Исходный_текст — текст, в котором требуется заменить некоторые знаки. Нач_поз — позиция знака в Исходном_тексте , начиная с которой знаки заменяются текстом новый_текст . Число_знаков — число знаков в Исходном_тексте , которые заменяются текстом новый_текст . Новый_текст — текст, который заменяет знаки в Исходном_тексте .

Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()

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

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

Пусть в ячейке А2 введена строка Продажи (январь) . Чтобы заменить слово январь , на февраль , запишем формулы:

=ЗАМЕНИТЬ(A2;10;6;»февраль») =ПОДСТАВИТЬ(A2; «январь»;»февраль»)

т.е. для функции ЗАМЕНИТЬ() потребовалось вычислить начальную позицию слова январь (10) и его длину (6). Это не удобно, функция ПОДСТАВИТЬ() справляется с задачей гораздо проще.

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

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

Использование функции для вставки нового текста в строку

Функцию ЗАМЕНИТЬ() удобно использовать для вставки в строку нового текста. Например, имеется перечень артикулов товаров вида » ID-567(ASD) «, необходимо перед текстом ASD вставить новый текст Micro , чтобы получилось » ID-567(MicroASD) «. Для этого напишем простую формулу: =ЗАМЕНИТЬ(A2;8;0;»Micro»).

7 хитростей чтобы сделать вас экспертом Excel

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

1. Переход указателя к нужной ячейке во время ввода данных.

Excel автоматически перемещает указатель в следующую ячейку вниз, когда вы нажимаете Enter после ввода данных в ячейку. Это отвлекает, особенно, если вы хотите добавить данные в следующий столбец в строке, а не ниже. Чтобы изменить это поведение, используйте окно настроек Excel.

Выберите: Файл -> Параметры -> Дополнительно -> Параметры правки. Поставьте флажок «Переход к другой ячейке после нажатия клавиши ВВОД» и в соответствующем раскрывающемся списке выберите то направление, которое вам больше подходит в данный момент.

Совет: При отключении флажка, клавиши со стрелками можно использовать для управления перемещением указателя. Например, если вы вводите данные в строке, нажмите клавишу со стрелкой вправо, а не Enter, чтобы перейти к следующей ячейке в той же строке.

2. Храните данные в таблицах данных:

Таблица Excel является сеткой из строк и столбцов. При добавлении данных в Excel, он интуитивно не использует таблицы данных. Однако таблицы данных Excel являются простым и мощным инструментом. Они помогают легко добавлять больше строк и столбцов, не беспокоясь об обновлении ссылок формул, опций, настроек фильтров и т.д. Таблицы данных сами позаботятся о форматировании.

Для добавления таблицы данных Excel, выберите диапазон ячеек, содержащих данные, и нажмите на кнопку Таблица, находящуюся во вкладке «Вставка» на ленте или нажмите сочетание клавиш «CTRL + T».

Несколько хитростей таблиц данных:

• Таблицы данных вставляются со своим собственным набором фильтров данных и параметром сортировки по умолчанию. Без таблиц данных Excel допускает только один набор фильтров на листе. Однако если рабочий лист имеет более одной таблицы, то каждая таблица может иметь свои собственные фильтры и параметры сортировки.
• Формулы внутри таблицы данных могут использовать осмысленные имена заголовков вместо использования ссылок на ячейки. Кроме того, при добавлении или удалении строк не нужно беспокоиться об обновлении ссылок.
• С помощью структурированных ссылок, вычисления внутри столбцов становятся очень простыми. Кроме того, Excel автоматически заполняет формулу внутри остальной части ячеек в столбце.
• Таблицы данных могут быть отформатированы одним кликом мыши по пункту «Стили» во вкладке «Конструктор».
• Повторяющиеся данные могут быть удалены из таблиц данных одним кликом мыши по пункту «Удалить дубликаты» во вкладке «Конструктор» на ленте.
• Таблицы данных могут быть преобразованы обратно в диапазон одним кликом мыши по кнопке «Преобразовать в диапазон» во вкладке «Конструктор».
• Таблицы данных могут быть экспортированы в список SharePoint так же одним кликом мыши по кнопке «Экспорт» во вкладке «Конструктор».
• Таблицы данных можно суммировать, добавив итоговую строку, поставив флажок в чек-боксе «Строка итогов» во вкладке «Конструктор».

Ввод данных с помощью форм

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

Нажмите на кнопку Настройка панели быстрого доступа

Выберите из списка пункт Другие команды. Выберите Все команды из выпадающего списка. Чуть ниже найдите Форма в большом списке и выделите его

Нажмите кнопку Добавить, и этот пункт должен добавиться в панель быстрого доступа после нажатия на кнопку ОК

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

В этом диалоговом окне есть следующие кнопки:

• Добавить: Добавляет новую запись в таблицу данных
• Удалить: Удаляет отображаемую запись из таблицы данных
• Вернуть: Восстановление изменений, внесенных в таблицу данных
• Назад / Далее: Находит записи, указанные в поле Критерии
• Критерии: Получение данных, которые удовлетворяют критериям набора
• Закрыть: Закрывает диалоговое окно

4. Проверка данных с помощью раскрывающегося списка:

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

• Введите список элементов в диапазоне.
• Выделите ячейку, которая будет содержать выпадающий список
• Выберите вкладку Данные на ленте -> Работа с данными -> Проверка данных.
• В диалоговом окне Проверка вводимых значений, перейдите на вкладку Настройки.
• В раскрывающемся списке Тип данных, выберите Список.
• В поле Источник укажите диапазон, содержащий нужные вам элементы.

Совет: Если в раскрывающемся списке мало данных, то вы можете ввести значения непосредственно в поле Источник, разделенные запятыми.

5. Автозамены для ввода данных:

Автозамена по умолчанию подразумевает такие полезные вещи, как написание предложений с заглавной буквы, или исправление случайного нажатия Caps Lock. Кроме того, можно настроить Автозамену создавая сокращения для часто используемых слов или фраз. Например, ниже я использую аббревиатуру: «гос» для «государство».

Для того чтобы добраться до Параметров автозамены, нажмите на Файл -> Параметры -> Правописание -> Параметры автозамены. В качестве альтернативы можно воспользоваться сочетанием клавиш: ALT + T + Т.

Автозамена несколько хитростей:

• Чтобы добавить новую автозамену, введите заменяемый текст в поле Заменять и введите замещающий текст, который будет в поле На, а затем нажмите кнопку Добавить.
• Чтобы удалить автозамену найдите ее в списке автозамен и нажмите кнопку Удалить.
• Чтобы отменить сработавшую автозамену, нажмите CTRL + Z при вводе информации в ячейку.
• Для совместного использования автозамен с вашими друзьями или коллегами, просто найдите файл *.acl на своем компьютере и скопируйте его на компьютер друга или коллеги.
• Excel преобразует адрес электронной почты или записи веб URL в гиперссылки с использованием автозамены. Чтобы изменить автоматическое преобразование текста в гиперссылку, просто нажмите кнопку Отменить (или нажмите Ctrl + Z) после того, как вы вводите текст. Гиперссылка исчезнет, но текст, который вы ввели останется неизменным. Чтобы полностью отключить эту функцию, снова перейдите в раздел Параметры автозамены -> Нажмите на вкладку: Автоформат при вводе -> уберите флажок: «адреса Интернета и сетевые пути гиперссылками».

6. Автозаполнения для автоматизации ввода данных:

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

Автозаполнение несколько хитростей:

• Автозаполнение работает только на смежной ячейке, поэтому не оставляйте пустых строк, чтобы в полной мере использовать автозаполнение
• Автозаполнение изменяет регистр букв автоматически
• Если столбец содержит несколько вхождений записи, которые соответствуют первым нескольким вводимых вами символам, то автозаполнение не сработает пока ваша запись не станет соответствовать хотя бы одному из них полностью.
• Вы можете получить доступ к автозаполнению, кликнув правой кнопкой мыши по ячейке и выбрав пункт «Выбрать из раскрывающегося списка». Также вы можете получить доступ к этому же раскрывающемуся списку с помощью горячей клавиши: Shift + F10.

7. Аудиокоррекция (работает, к сожалению, только с английским текстом и числами)

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

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

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

Как в Excel вместо объекта в ячейке отображался замещающий текст.

попробую угадать.
Эта фишка называется «Присвоить имена ячейкам»
В ячейку AZ1 записываем «1»
AZ2=2
Выделяем обе ячейки и за нижний правый уголок протягиваем вниз.
Выделяем AZ1
слева от строки формул есть адресное поле. Щелкаем курсором и записываем «один»
Так же поступаем с другими ячейками.
Теперь проверим как это работает
В любой свободной ячейке делаем запись:
=четыре*три и жмем ЕNТЕRИли можно записать «пять» ( без кавычек) и делать ссылку на эту ячейку.
=345/В3