Excel как заменить в ячейке одно значение на другое

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

​Смотрите также​ label15.Text + textBox23.Text;​ все!​=’C:Baza[baza.xlsx]Лист1′!$C$2 — работает​ ты прямо в​ ошибка на текст​ делает каждая строка​ например: 1,234.56- а​ может возникнуть проблема​ допущения таких ошибок.​ все части текстовой​ CreateObject(«VBScript.RegExp»)​ записанные в таблицу​

​ чье-то имя или​ эта возможность в​

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

​ обычного поиска, поиск​ построчно. Курсор останавливается​Бывают ситуации, когда в​ label27.Text = label16.Text​=ЗАМЕНИТЬ(A2;ДЛСТР(A2);1;C2)​=ДВССЫЛ(«‘C:Baza[baza.xlsx]Лист1’!$C$»&A1) — не​ числах мечтаешь замену​ по столбцам. Может​ вашего кода​ нужно что бы​ при замене, скажем​ Текст макроса использованного​ строки, соответствующие фрагменту​regex.Pattern = Pattern​ Excel. Некоторые величины​ необходимо изменить определенное​ полной мере предоставлена​ для замены можно​ на первом же​ документе нужно заменить​

​ + textBox23.Text; label28.Text​Savra​ работает​ провести? Если это​ и не надо​и почему не​ было -1 234.56​

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

​ «KS-200″->»KS-200 (FT-80)» так​ мной:​ текста стар_текст, и​regex.Global = True​ рассчитать не удалось,​ слово или фразу​ в данном табличном​ производить исключительно по​ результате, который совпал.​ один символ (или​ = label17.Text +​

1. ​: ага, сейчас, чуть​​=ДВССЫЛ(«[baza.xlsx]Лист1!$C$»&A1) — не​​ внутри текстовых значений,​ его (ее, их)?)​​ работал приведенный мною​​ (кол-во знаков и​​ как при повторной​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub DNtoME()​ выполнит их замену​If regex.Test(Text) Then​​ и вместо числового​​ на другую. Вы​

   

2. ​ процессоре.​​ формулам, то есть,​​ Для замены содержимого​​ группу символов) на​​ textBox23.Text; label29.Text =​​ попозже протестирую и​​ работает​ то, например:​Hugo​ код​​ разряды могут быть​​ замене двухзначных значений​Cells.Replace What:=»KS-20″, Replacement:=»FT-18″,​ на нов_текст;​

   ​Set matches =​ представления была сгенерирована​ можете воспользоваться инструментом​​Автор: Максим Тютюшев​​ по тем значениям,​​ ячейки жмем на​​ другой. Причин может​ label18.Text + textBox23.Text;​​ отпишусь.​​=ДВССЫЛ(АДРЕС(A$1;СТРОКА(A3);;;»[baza.xlsx]Лист1″)) — не​​Меняешь «1» на​​: У меня тоже​​я еще только​​ разными)​

   

3. ​ после букв, будут​ LookAt:=xlPart, SearchOrder _​В качестве аргумента передано​ regex.Execute(Text)​ ошибка “NaN”. Необходимо​ Найти и заменить,​Поиск и замена в​ которые указываются в​ кнопку​ быть множество, начиная​​ label30.Text = label19.Text​​Спасибо.​

   

4. ​ работает​ «Ё», потом «2»​ ругается — нет​​ учусь быть подмастерьем,​​В макросе с​ меняться и уже​:=xlByRows, MatchCase:=False, SearchFormat:=False,​

​ числовое значение. Функция​RegExpExtract = matches.Item(Item​

1. ​ заменить все значения​ чтобы быстро внести​ Excel – это​​ строке формул при​​«Заменить»​

   

2. ​ от банальной ошибки,​ + textBox23.Text; //​Savra​если открыть файл базы,​ на «1», потом​ такого в 2003.​ знаний не хватает​ помощью мастера текста​ замененные данные.​ ReplaceFormat:=False​ ПОДСТАВИТЬ заменит только​ — 1)​​ “NaN” на число​​ исправления. В нашем​

   

3. ​ достаточно мощный и​ выделении ячейки. Кроме​.​ и, заканчивая переделкой​ label7.Text = name1​: Попробовал, немного разобрался,​

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

​ то все 4​ «Ё» на «2».​nilem​СПАСИБО ЕЩЕ РАЗ​ и функции автозамены​deus_russia​Cells.Replace What:=»KS-200″, Replacement:=»FT-80″,​​ указанное вхождение. Отсчет​​Exit Function​

​ 0 в соответствующих​

​ примере мы воспользуемся​ удобный инструмент, который​ того, тут же,​Чтобы продолжить поиск данных,​ шаблона или удалением​ + textBox24.Text !=​ ссылки (их текстовый​ примера работают как​ И все. Вместо​: Останавливается здесь​ !!!!​ (Ctrl+H) удается добиться​: я не силен​ LookAt:=xlPart, SearchOrder _​ начинается слева направо,​End If​

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

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

​ путем установки или​ опять жмем на​ пробелов. Давайте выясним,​ null ? textBox24.Text.ToString()​

1. ​ вид) получаются.​ надо.​ «Ё» можно использовать​Columns(«E:E»).TextToColumns​

   

2. ​_Boroda_​ лишь варианта 1234.56-​ в этом. Можете​:=xlByRows, MatchCase:=False, SearchFormat:=False,​ число 1 соответствует​​ErrHandl:​​Таблица данных:​

   

   ​ исправить список адресов​ при необходимости и​ снятия галочек, можно​ кнопку​ как быстро заменить​ : «temp» +​Но они почему​примечание: в ячейке А1​ любой отсутствующий в​и пишет, что,​: Вот так работает​ , дальше чего​ пример привести, как​ ReplaceFormat:=False​ первому вхождению. Например,​RegExpExtract = CVErr(xlErrValue)​Для замены и подстановки​ электронной почты.​ заменять, информацию на​ указать, учитывать ли​«Найти далее»​ символы в программе​ name2; label7.Text =​ то не функциональны,​ этих примеров, ставится​ текстах символ.​ мол, Не выделены​

   ​ без дополнительных преобразований​ бы не делал​ правильно написать? Спасибо.​End Sub​ функция =ПОДСТАВИТЬ(«текст №1,​End Function​ используем рассматриваемую формулу​

   

   ​На вкладке Главная нажмите​ рабочем листе. В​ при поиске регистр​. Тем же способом​

   

   ​ Microsoft Excel.​ name1 + textBox24.Text​ просто текст, и​ число, которое изменяет​Aleksey shatalov​ данные для разбора​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub tt()​ ни формат и​

   

   ​deus_russia​Заранее спасибо!​ №2, №3»;«текст»;«новый»;1) вернет​Для того, чтобы воспользоваться​ в качестве массива.​ команду Найти и​ рамках данного урока​ букв, искать ли​ меняем следующий результат,​Скачать последнюю версию​

   

   ​ + name2; }​ даже из других​ все нужные ссылки.​​: ВПР вам поможет​​Ексель 2010​Workbooks.OpenText Filename:=»C:F1023.txt», Origin:=​ само значение изменить​: Не подскажите на​Оформляйте коды тегами​

   

   ​ значение «новый_текст №1,​ этой формулой, в​ Вначале выделим диапазон​ выделить, а затем​ Вы научитесь выполнять​ точное соответствие в​ и т.д.​ Excel​

3. ​ private void button3_Click(object​ ячеек ссылкой на​Подскажите как это​Savra​Ну да, перед​ _​ не удается. Что​ небольшом примере, как​ (кнопка #)​ №2, №3».​ любой ячейке необходимо​ ячеек C2:C9, затем​ из раскрывающегося списка​ поиск в заданной​ ячейках.​Можно найти все удовлетворяющие​Конечно, наиболее простым способом​ sender, EventArgs e)​

​ эти, возвращает только​ побороть, или может​

​: Здравствуйте!​ этой строкой в​1251, StartRow:=1, DataType:=xlDelimited,​ бы добиться нужного​ это правильно сделать?​AlexM​Примечания:​ ввести следующее:​ введем формулу через​ выберите пункт Заменить.​ области документа Excel,​Также, можно указать среди​ запросу результаты сразу.​ заменить один символ​ { excelapp.Visible =​ текст.​ по другому как​Подскажите пожалуйста, не​ ст. Е ничего​

​ TextQualifier:=xlDoubleQuote, _​

lumpics.ru

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

​ формата приходиться убирать​AlexM​: Нужен пример с​Аргумент [номер_вхождения] должен быть​= RegExpExtract(текст;регулярное_выражение;[номер_вхождения])​ комбинацию Ctrl+Shift+Enter:​Появится диалоговое окно Найти​ а также изменять​ ячеек какого формата​После ввода поискового запроса​ на другой является​ true; //включаем видимость​Виктор Михалыч​ нить сделать можно?​ получается что то​

​ нет, все находится​ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,​ минус (-), НО​: Пример в файле.​ файлом, как рекомендовано​ задан из диапазон​Регулярные выражения могут быть​Функция ЧЗНАЧ выполняет преобразование​ и заменить. Введите​ найденную информацию на​ будет производиться поиск.​ и заменяющих символов​ ручное редактирование ячеек.​ программы Excel workbook​: Тогда макросом.​Поясню для понимания.​ реализовать такую задачу…​ в ст. А.​ Comma:=False, _​ тогда значение становится​

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

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

​ текст, который Вы​ требуемое значение.​ Для этого нужно​ жмем на кнопку​ Но, как показывает​ = excelapp.Workbooks.Open(shablon); //открываем​Savra​ Мне нужно сделать​1. Есть база(массив)​

1. ​ Может так Columns(«А:А»).TextToColumns?​Space:=False, Other:=False, FieldInfo:=Array(Array(1,​ положительным 1 234.56,​: В вашем макросе​Если без файла,​
2. ​ от 1 до​ выделения любого символа​ к числовым значениям.​ ищете в поле​При работе с большим​ кликнуть по кнопке​
3. ​«Найти все»​ практика, далеко не​ Excel файл шаблон​: ладно, сейчас что​
4. ​ 350 документов/карточек(эти документы​ данных в таблице​Только непонятно зачем.​ 2), Array(2, 2),​ а это не​ замените​ то пробуйте добавить​ n, где n​ из текстовой строки​ Описание аргументов функции​
5. ​ Найти.​ количеством данных в​ «Формат» напротив параметра​.​

​ всегда этот способ​ worksheet = workbook.Sheets[«Лист1»];​ нибудь придумаем…​ будут в дальнейшем​ excel​

​ Вроде и так​ Array(3 _​ приемлемо (нужно именно​LookAt:=xlPart​ пробелы.​

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

​ определяется максимально допустимой​ в качестве второго​ ПОДСТАВИТЬ:​Введите текст, на который​ Excel, иногда достаточно​ «Найти».​Производится поиск всех релевантных​ самый легкий в​ //открываетм лист с​Виктор Михалыч​ активно использоваться со​2. Делаются выборки​ нормально.​, 2), Array(4,​ отрицательное число)​на​

1. ​»KS-20 » ->​ длиной строки, содержащейся​ аргумента необходимо передать​B2:B9 – диапазон ячеек,​ требуется заменить найденный,​
2. ​ трудно отыскать какую-то​После этого откроется окно,​ ячеек. Их список,​ масштабных таблицах, где​ именем worksheet.Unprotect(pass); //снять​
3. ​: Два файла выложите​ своими остальными данными​ по шаблону из​Hugo​ 2), Array(5, 1),​
4. ​Выкладываю исходный файл​LookAt:=xlWhole​ «FT-18 » и​
5. ​ в объекте данных​ значение «w», а​ в которых требуется​
6. ​ в поле Заменить​ конкретную информацию. И,​ в котором можно​
   • ​ в котором указано​ количество однотипных символов,​
   • ​ защиту листа worksheet.Cells[1,​ без данных, укажите​ и вычислениями.​ этих данных в​: У меня вообще-то​ Array(6, 1)), DecimalSeparator:=».»,​

   

7. ​ из программы, и​deus_russia​ «KS-200 » ->​ (например, в ячейке).​
8. ​ цифры – «d».​
9. ​ выполнить замену части​ на. А затем​ как правило, такой​ указать формат ячеек​

​ значение и адрес​ которые требуется изменить,​
​ «D»] = label6.Text​
​ цветом, где нужно​

​В этих документах​

office-guru.ru

Как в excel заменить определенные значения?

​ отдельные файлы excel​ вообще по колонкам​ _​ фал с макросом,​: Разобрался! Кажется, Ваш​ «FT-80 «​Если в текстовой строке,​Для решения задачи данного​ строки;​ нажмите Найти далее.​

​ поиск занимает очень​​ для поиска.​ каждой ячейки, открывается​

​ может достигать очень​​ != null ?​ поменять формулы, я​ есть порядка 11​Так вот, чтобы​ не раскидывает -​
​ThousandsSeparator:=»,», TrailingMinusNumbers:=True​ которым преобразую текст​ способ сработал. Спасибо​

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

​Вопросы по VBA​ представленной в качестве​ Примера 3 используем​“NaN” – фрагмент текста,​Если значение будет найдено,​ много времени. Excel​Единственной настройкой значения для​ в нижней части​ большого количества. Даже​ label6.Text.ToString() : «»;​ вам сделаю макрос.​ ссылок на базу​ сделать(подготовить) по шаблону​

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

​ всё в A​With Columns(«B:C»)​ в Excel ,​ большое Вам и​ в другом разделе​ аргумента текст не​ следующую запись:​ который будет заменен;​ то содержащая его​ предлагает прекрасный инструмент​ вставки будет являться​ окна. Теперь можно​ на поиск нужных​ worksheet.Cells[5, «D»] =​

​ Для примера.​

​ с конкретными оригинальными​ много файлов(выборок) приходится​ лежит…​.ColumnWidth = 5.13​ ну и пример​ всем кто мне​ форума.​

​ содержится фрагмент, переданный​Логика вычислений:​0 – фрагмент, который​ ячейка будет выделена.​ поиска. Вы можете​

• ​ все тот же​ кликнуть по любой​ ячеек может быть​ label20.Text != null​
• ​Savra​ данными.​
• ​ в шаблоне менять​Ошибся — работает​.HorizontalAlignment = xlCenter​

​ того, что получается…​ отвечал.​deus_russia​ в качестве аргумента​Функция ЕСЛИОШИБКА используется для​ будет вставлен на​

​Посмотрите на текст и​ с легкостью отыскать​ формат ячеек. Для​ из ячеек, в​

​ потрачено значительное количество​

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

​ ? label20.Text.ToString() :​: Удалось реализовать задачу​Мне нужно 350​ одну и туже​ TextToColumns, если есть​.VerticalAlignment = xlBottom​Мне предложили вот​Gelandewagen​: Спасибо за совет.​ стар_текст, функция ПОДСТАВИТЬ​ возврата исходной строки​

​ место заменяемого фрагмента.​

​ убедитесь, что Вы​ любую нужную информацию​

​ выбора формата вставляемого​ которой мы хотим​ времени, не говоря​

​ «»; worksheet.Cells[5, «F»]​

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

​ с помощью формы​ раз поменять все​ цифру во многих​ что обрабатывать:​.WrapText = False​ такое решение:​: Товарищи! Помогите! Где​ После добавления пробела,​ вернет строку текст​ текста (B2), поскольку​

​Для подстановки значений во​ согласны заменить его.​ в рабочей книге​ значения жмем на​ выполнить замену, и​ уже о временных​ = label21.Text !=​ на сишарпе.​ формулы этих ссылок​ разных местах итогового(следующего)​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Columns(«A:A»).TextToColumns​.Orientation = 0​

1. ​Dim s​
2. ​ я сделал ошибку?​ данные перестают меняться.​
3. ​ без изменений.​ результатом выполнения функции​

​ всех ячейках необходимо​

​Если согласны, тогда выберите​ Excel с помощью​ одноименную кнопку напротив​ нажать на кнопку​ затратах на редактирование​
​ null ? label21.Text.ToString()​теперь в "скоростном"​
​ и каждый новый​ листа(выборки).​
​nilem​
​.AddIndent = False​
​s = "123.45-"​
​=ИНДЕКС(M1:M2803;ПОИСКПОЗ("*E2*";M1:M2436;0);0)​ Файл приложил.​
​Если число вхождений заменяемого​ RegExpExtract(B2;"Никольская") будет код​
​ нажать Ctrl+Shift+Enter, чтобы​
​ одну из опций​
​ команды Найти, которая​
​ параметра «Заменить на…».​
​«Заменить»​


​ каждой из них.​ : «»; worksheet.Cells[5,​ режиме формируются файлы.​ документ сохранить отдельным​

​Решил сделать так​

​: Кстати, недавно видел​.IndentLevel = 0​Debug.Print Val(s) *​Нужно из массива​deus_russia​ фрагмента в обрабатываемой​ ошибки #ЗНАЧ!, если​ функция была выполнена​

​ замены:​ к тому же​Открывается точно такое же​

​.​

1. ​К счастью, в программе​ «H»] = label22.Text​код, может кому​ файлом.​ — на втором​ код, написанный специально​.ShrinkToFit = False​ IIf(Right$(s, 1) =​ достать ту ячейку,​: Забыл добавить. У​ строке меньше, чем​
2. ​ ей не удалось​ в массиве. Результат​Заменить: исправляет по одному​ позволяет еще и​

• ​ окно, как и​Замена значения будет выполнена,​ Эксель имеется инструмент​
• ​ != null ?​ пригодится.​Вот думаю, как​ листе файла выборки​
• ​ для 2003, и​.ReadingOrder = xlContext​ «-«, -1, 1)​ в которой есть​

​ меня Excel 2013​

​ числовое значение, переданное​ найти хотя бы​ вычислений:​ значению зараз.​ изменить данные с​ в предыдущем случае.​

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

​ а пользователь может​ «Найти и заменить»,​

​ label22.Text.ToString() : «»;​

​C# using System;​

• ​ можно перед сохранением​ все нужные формулы(ссылки​ там было (была,​.MergeCells = False​s = «-123.45″​ значение ячейки E2.​gling​ в качестве аргумент​ одно вхождение подстроки​Таким же образом функция​
• ​Заменить все: исправляет все​ помощью инструмента Найти​ Тут устанавливается, как​ дальше продолжать в​ который поможет быстро​ worksheet.Cells[7, «D»] =​
• ​ using System.Collections.Generic; using​ файлов менять ссылки​ на базу) выписал​ был — ну​End With​Debug.Print Val(s) *​
• ​ В ячейке, которую​: Здравствуйте. А просто​ [номер_вхождения], функция ПОДСТАВИТЬ​ «Никольская» в строке​ подставляет значения и​ варианты искомого текста​

1. ​ и заменить.​ будут отформатированы ячейки​ поисковой выдаче искать​ отыскать нужные ячейки,​ label23.Text != null​ System.ComponentModel; using System.Data;​ не в ручную,​
2. ​ в столбик(и сделал​ как все-таки?​Columns(«D:D»).ColumnWidth = 19.88​ IIf(Right$(s, 1) =​ нужно найти, есть​ Ctrl+H нельзя, обязательно​ вернет текстовую строку​ B2.​ другой таблицы при​ в книге. В​В нашем примере мы​

​ после замены их​

1. ​ нужные ему результат​ и выполнит в​ ? label23.Text.ToString() :​ using System.Drawing; using​ а как нибудь​ вид «показать формулы»),​) .TextToColumns.​Columns(«D:D»).ColumnWidth = 22.25​ «-«, -1, 1)​
2. ​ не только значение​ макрос писать? Вообще​ в исходном виде.​Если результат выполнения сравнения​ определенном условии.​ нашем примере мы​ воспользуемся командой Найти,​ данных. Можно установить​
3. ​ для повторной процедуры.​ них замену символов.​ «»; worksheet.Cells[7, «I»]​ System.Linq; using System.Text;​ автоматизировать это дело.​ а уже на​Наверное, где-то есть​Columns(«E:I»).ColumnWidth = 15.75​s = «123.45»​ из E2.​ то нужна только​ Например, аргументы функции​ значений RegExpExtract(B2;»Никольская»)=»Никольская» является​​
4. ​ воспользуемся этой опцией​ чтобы отыскать требуемое​ выравнивание, числовые форматы,​Можно выполнить автоматическую замену​Простая замена с поиском​
5. ​ = label24.Text !=​ using System.Windows.Forms; using​ Хотя могу и​ первый лист(основной) в​ все же.​Columns(«E:I»).Replace What:=» «,​Debug.Print Val(s) *​Спасибо!​ эта часть​ («а 1 а​ ИСТИНА, будет выполнена​Пример 2. Провайдер домашнего​ для экономии времени.​ имя в длинном​ цвет ячеек, границы,​ нажатием всего одной​ предполагает замену одного​ null ? label24.Text.ToString()​ Excel = Microsoft.Office.Interop.Excel;​ в ручную поменять​ нужные места сделал​

exceltable.com

Массовая замена одних значений на другие (Формулы/Formulas)

​Скорее всего «был»​​ Replacement:=»», LookAt:=xlPart, _​
​ IIf(Right$(s, 1) =​Ваш вопрос не​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Cells.Replace What:=»KS-20″, Replacement:=»FT-18″​ 2 а 3»;«а»;«б»;4)​ функция ПОДСТАВИТЬ(B2;RegExpExtract(B2;»d+»);RegExpExtract(B2;»d+»)&»-Н»), где:​ интернета хранит данные​Появится диалоговое окно, подтверждающее​ списке сотрудников.​ и т.д.​ кнопки. Для этого​ последовательного и фиксированного​ : «»; worksheet.Cells[1,​ using Word =​ ссылки за пару​ ссылки на эти​ — метод как​SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,​ «-«, -1, 1)​ относится к данной​остальное не к​ вернут строку «а​a. B2 – исходный​ о своих абонентах​ количество замен, которые​
​Если перед использованием команды​
​Также, нажав на соответствующий​ после ввода заменяемых​
​ набора символов (числа,​ "H"] = label25.Text​
​ Microsoft.Office.Interop.Word; namespace Замена_ссылок​ тройку ночей. Но​
​ ячейки.​ никак. Solved​
​ _​
​но оно не​
​ теме. Создайте свою​ чему. И что​

​ 1 а 2​​ текст, содержащий полный​ в таблице Excel.​ будут сделаны. Нажмите​
​ Найти Вы выделите​ пункт из выпадающего​ значений, и значений,​
​ слова, знаки, и​ != null ?​ { public partial​ можно наделать ошибок.​
​И еще решил,​Hugo​ReplaceFormat:=False​

​ работает…​​Ирина Шахова​ добавляется, что то,​ а 3».​ адрес;​

​ Предположим, улица Садовая​​ ОК для продолжения.​ одну ячейку, то​

​ списка под кнопкой​​ на которые производится​ т.д.) на другой​ label25.Text.ToString() : «»;​ class Form1 :​Добавлено через 7 минут​​ что можно сделать​​: Да, есть. Просто​Columns(«E:E»).TextToColumns​Возможно, я его​: Добрый день!​ хотелось бы увидеть.​Рассматриваемая функция чувствительная к​b. RegExpExtract(B2;»d+») – формула,​

​ была переименована в​​Содержимое ячеек будет заменено.​ Excel будет выполнять​«Формат»​ замена, жмем кнопку​ после того, как​ worksheet.Cells[1, «K»] =​ Form { string​Туда же…​ так — что​ не пользовался никогда…​Columns(«F:F»).TextToColumns​ не правильно использую​Очень прошу помощи.​

​ Про 2013 не​​ регистру, то есть​​ выделяющая номер дома​​ Никольскую. Необходимо быстро​Закончив, нажмите Закрыть, чтобы​ поиск во всем​
​, можно установить формат​«Заменить все»​ эти символы будут​ label26.Text != null​ shablon = «C:\Baza\Info\!shablon.xlsx»;​

​=ДВССЫЛ(АДРЕС(A$1;СТРОКА(A3);;;»C:Baza[baza.xlsx]Лист1″)) — не​​ чтобы поменять в​nilem​Columns(«G:G»).TextToColumns​Помогите пожалуйста, подскажите​ Как в Экселе​ знаю, у меня​ строки «Слово» и​ из строки с​ произвести замену названия​ выйти из диалогового​

​ листе. А если​​ идентичный любой выбранной​.​ найдены при помощи​ ? label26.Text.ToString() :​ //имя Excel файла​ работает с закрытым​ этих формулах одно​: У меня так​Columns(«H:H»).TextToColumns​ решение​ заменить значения на​ 2010.​ «слово» не являются​ полным адресом;​ улицы в строке​ окна Найти и​

​ диапазон ячеек, то​​ ячейке на листе,​Процедура выполняется практически моментально.​ специального встроенного инструмента​ «»; worksheet.Cells[1, «N»]​

​ шаблона string baza​​ файлом базы​ значение(цифру, номер строки)​ работает (2010)​

​Columns(«I:I»).TextToColumns​​И прежде чем​

​ 0, но так​​deus_russia​ тождественными.​​c. RegExpExtract(B2;»d+»)&»-Н» – новый​​ данных об адресе​​ заменить.​

​ только внутри этого​​ достаточно только ее​Плюсы этого способа —​ программы.​ = label27.Text !=​ = «C:\Baza\baza.xlsx»; //имя​

​Savra​​можно сделать автоматическую​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Макрос6()​
​Columns(«E:I»).NumberFormat = «#,##0.00″​
​ предложить вариант решения​ чтобы формулы, которые​: Ctrl-H не подходит.​Для решения аналогичных задач​ номер, содержащий исходное​ проживания каждого клиента.​Урок подготовлен для Вас​ диапазона​
​ выделить.​
​ быстрота и удобство.​Кликаем по кнопке​ null ? label27.Text.ToString()​

excelworld.ru

Замена значений (разных) на 0, исключая формулы.

​ Excel файла базы​​: Такое расположение ссылок.​
​ подстановку(замену) этого числа​Workbooks.OpenText Filename:=»D:Загрузки401401-1903.txt», Origin:=1251,​Range(«I10»).Select​ протестируйте его пожалуйста​ прописаны в книге​ Замен слишком много​ по замене части​
​ значение и символы​

​Таблица данных:​​ командой сайта office-guru.ru​​На вкладке Главная воспользуйтесь​​Дополнительным ограничителем поиска может​​ Главный минус состоит​​«Найти и выделить»​​ : «»; worksheet.Cells[3,​
​ string pass =​​ Менять 350 раз​​ в формуле.​ DataType:=xlDelimited, _​

​End Sub​​ на моем примере.​
​ остались.​
​ и делать их​
​ символов текстовой строки​ «-Н».​
​Для выполнения заданного условия​
​Источник: http://www.gcflearnfree.org/office2013/excel2013/7/full​

​ командой Найти и​​ быть указание диапазона​ в том, что​, которая располагается во​ «M»] = label28.Text​ «zzz»; //пароль на​ всё, хлопотно…​

​А вот как​​TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True,​
​nilem​
​Спасибо!​Огромное спасибо!​ приходится постоянно. Насчет​

​ можно использовать функцию​​Результат вычислений:​

planetaexcel.ru

Замена одного значения на другое (перенос знака минус «-» справа от цифры налево)

​ используем формулу:​​Автор/переводчик: Антон Андронов​
​ выделить, а затем​
​ ячеек, среди которых​ вы должны быть​ вкладке​ != null ?​ защиту string text1;​Виктор Михалыч​ написать эту формулу​ Space:=True, _​: Писать особо нечего​nilem​
​Михаил С.​ макроса, я не​ ЗАМЕНИТЬ. Однако, в​Данный способ может показаться​Примечание: в данном примере​Автор: Антон Андронов​ из раскрывающегося списка​ будет производиться поиск​ уверены, что введенные​«Главная»​ label28.Text.ToString() : «»;​ string text2; string​: А чем вам​ не знаю.​FieldInfo:=Array(Array(1, 2), Array(2,​ (раскладка заработала -​: For example (keyboard​:​
​ специалист. Текст макроса​ отличие от функции​ громоздким, однако он​ ПОДСТАВИТЬ также используется​Есть массив чисел, в​ выберите пункт Найти.​ и замена. Для​
​ символы нуждаются в​в блоке настроек​
​ worksheet.Cells[5, «M»] =​
​ str; string name1​
​ не угодил стандартный​Подскажите пожалуйста как​ 2), Array(3, 2),​
​ чудеса​
​ layout does not​CTRL+G​ нашел на сайте​
​ ПОДСТАВИТЬ, для ее​
​ весьма удобен для​ в массиве Ctrl+Shift+Enter.​ нем необходимо одно​
​Появится диалоговое окно Найти​ этого достаточно просто​
​ замене во всех​«Редактирование»​
​ label29.Text != null​ = «C:\Baza\Info»; string​
​ поиск и замена?​ это написать, например​ Array(4, 2), Array(5,​)​
​ switch​

​->​​ и решил попробовать​ использования необходимо явно​ работы с таблицами,​​В результате получим:​
​ определенное значение, часто​
​ и заменить. Введите​ выделить нужный диапазон​
​ ячейках. Если в​. В появившемся после​
​ ? label29.Text.ToString() :​ name2 = ".xlsx";​Виктор Михалыч​
​ поменять(сопоставить) в формуле​
​ 1), Array(6, 1)),​

​For Each r​​)​Выделить​ сам написать. Получилось,​
​ указывать позицию начального​ содержащими большое количество​Пример 3. При составлении​
​ повторяющееся,но не периодично,​ данные, которые необходимо​ вручную.​
​ предыдущих способах была​ этого списке делаем​

​ «»; worksheet.Cells[5, «Q»]​​ private Excel.Application excelapp;​: Еще, можете макросом,​ последние цифры 2​ _​ In .SpecialCells(2)​
​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub ertert()​->​ что сам макрос​

​ символа для замены,​​ данных.​
​ таблицы из предыдущего​
​ заменить на другое​
​ отыскать. В нашем​Не забываем в поля​ возможность найти и​
​ переход по пункту​ = label30.Text !=​ //программа Excel private​
​ через VBA реализовать​ на цифру 3​DecimalSeparator:=».», ThousandsSeparator:=»,», TrailingMinusNumbers:=True​
​If InStr(r, «-«)​Dim r As​

​Константы​​ работает но не​ а также количество​
​Функция ПОДСТАВИТЬ имеет следующую​
​ примера была допущена​ во всех ячейках,​
​ примере мы введем​ «Найти» и «Заменить​
​ отобрать нужные ячейки​«Заменить»​
​ null ? label30.Text.ToString()​ Excel.Workbook workbook; //книга(файл)​ все что вам​
​ из другой ячейки,​Columns("B:C").ColumnWidth = 5.13​ Then r =​ Range​
​CTRL+H​
​ совсем так как​
​ символов, которые необходимо​
​ синтаксическую запись:​
​ ошибка: все номера​
​ где оно находится,​
​ имя сотрудника.​
​ на…» вписать соответствующие​
​ для изменения, то​
​.​
​ : ""; string​
​ Excel private Excel.Worksheet​
​ нужно.​
​ так чтобы итоговая​
​Columns("D").ColumnWidth = 22.25​
​ Val® * -1​
​For Each r​;​
​ хотелось бы.​ заменить. Функция автоматически​
​= ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения])​
​ домов на улице​
​ как это можно​
​Нажмите Найти далее. Если​
​ значения. Когда все​
​ при использовании данного​
​Открывается окно​
​ name = name1​
​ worksheet; //рабочие листы​

​Виктор Михалыч​​ формула имела рабочий,​Columns(«E:I»).ColumnWidth = 15.75​смотрим каждую заполненную​​ In [F:G].SpecialCells(2)​
​Найти — *; заменить​Pelena​
​ выполняет поиск указанной​Описание аргументов:​ Никольская должны быть​
​ сделать сразу для​ данные присутствуют на​ настройки указаны, выбираем​ варианта такая возможность​«Найти и заменить»​ + textBox24.Text +​ public Form1() {​: Можно так попробовать​ исполняемый вид?​End Sub​ ячейку в столбцах​If InStr(r, «-«)​
​ - 0​:​ части строки и​текст – обязательный аргумент,​
​ записаны как «№№-Н»,​ всего массива данных?​ листе, то они​ способ выполнения процедуры.​ исключается.​во вкладке​
​ name2; worksheet.Protect(pass, false,​ InitializeComponent(); } private​​Savra​​За ранее благодарю​В «D» (номера​ F:G, и, если​
​ Then r =​sva​deus_russia​ производит ее замену,​ характеризующий текстовую строку,​ где №№ -​Лев королев​ будут выделены.​

​ Либо жмем на​​Урок: как заменить точку​«Заменить»​ true, false, false,​

​ void Form1_Load(object sender,​​: через поиск дольше​
​Savra​
​ счетов, видимо) -​ в ней (в​ Val(r) * -1​
​: Или так:​
​, попробуйте поменять местами:​ поэтому в большинстве​ в которой необходимо​ номер дома. Как​: Жмите Ctrl+H и​Если Вы еще раз​
​ кнопку «Заменить все»,​ на запятую в​. В поле​

​ false, false, false,​​ EventArgs e) {​ менять чем вручную,​: добавлено: и можно​ текст, в «E:I»​ ячейке) наблюдается минус,​
​Next​Нажимаете F5​ сначала замените KS-200,​
​ случаев предлагает более​

​ выполнить замену части​​ быстро исправить ошибку?​ будет вам счастье.​ нажмете на кнопку​ и замена происходит​ Экселе​«Найти»​​ false, false, false,​
​ excelapp = new​ потому как ссылки​
​ ли будет подставить​ — числа.​ то преобразовываем значение​

​End Sub​​Выделить​ а потом KS-20.​

​ удобный функционал для​​ текста. Может быть​В данном случае для​
​Nemez​
​ Найти далее, то​ автоматически, согласно введенным​
​Кроме того, существует возможность​вводим число, слова​
​ false, false, false,​ Excel.Application(); //открываем программу​ все разные и​ итоговое решение сразу​Может, дело в​
​ ячейки в число​
​alexsampler​
​Константы - галочками​
​А ещё в​
​ работы с текстовыми​
​ задан как текстовая​ поиска числовых значений​: Нажми Ctrl+F, перейди​ увидите следующий вариант​
​ данным, или же​ расширенного поиска и​

​ или символы, которые​​ false, false); //включить​ Excel excelapp.Visible =​ в них нужно​ в основной лист​ настройках каких-нибудь?​

excelworld.ru

В эксель заменить одно значение на другое, а «другое на одно»

​ и умножаем на​: Boroda_, не совсем​ выбираете нужные типы​ методе Replace есть​ строками.​ строка («некоторый текст»)​ номера дома воспользоваться​ на вкладку «Заменить»​ поиска. Вы также​ жмем на кнопку​ замены по дополнительным​
​ требуется отыскать и​ защиту листа workbook.SaveAs(@name);​ false; //выключаем видимость​

​ менять только последнюю​​ (без этого костыля)​Hugo​ -1. Если встречаются​ понял о каком​Вводите 0​ параметр LookAt, который​deus_russia​ или ссылка на​
​ встроенными функциями не​ и вписывай исходное​ можете выбрать Найти​ «Найти все», и​ параметрам.​ заменить. В поле​ workbook.Close(); excelapp.Visible =​ программы Excel }​

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

Добавление(замена) числа в формулах(ссылках) по значению из другой ячейки

​: А если ответите​​ запятые в качестве​
​ текстовике речь?​Нажимаете Ctrl+Enter​ позволяет задать поиск​
​: Здравствуйте, Друзья!​ ячейку, которая содержит​ удастся. Рациональнее всего​
​ значение и на​ все, что бы​ отдельно производим замену​Находясь во вкладке «Заменить»,​
​«Заменить на»​ false; //выключаем видимость​ private void textBox23_TextChanged(object​выделил ячейку -​ «скрытой ячейкой» значение​ на мой вопрос​ разделителей разрядов, то​- в пархиве​
​Ирина​ слова целиком xlWhole​У меня следующая​ текстовые данные.​ использовать регулярные выражения.​ что заменить.​ увидеть все варианты,​ в каждой ячейке​ в окне «Найти​выполняем ввод данных,​ программы Excel workbook​ sender, EventArgs e)​
​ поставил курсор в​ которой меняет все​ — то может​ можно так дополнить​ 401-1903 это входной​: Спасибо большое. А​
​AlexM​ проблема. Составил макрос​стар_текст – часть текстовой​
​ По умолчанию, в​ЗЫ. Ну да…​ которые для Вас​
​ по тому алгоритму,​ и заменить» жмем​ на которые будет​ = excelapp.Workbooks.Open(baza); //открываем​ { text1 =​ строку формул -​ формулы на листе​ быть ещё вариант​200?’200px’:»+(this.scrollHeight+5)+’px’);»>If InStr(r, «-«) Then​
​ файл​

​ как сделать замену​​: Нужно в файле​ для замены одних​ строки, принимаемой в​ Excel отсутствует функция​ или сразу Ctrl+H,​ отыскал Excel.​ о котором писалось​ на кнопку Параметры.​ производиться замена.​ Excel файл базы​

​ textBox23.Text; } private​​ нажал 1, 2,​ «одним кликом»?​ будет…​ r = Val(Replace(r,​- макрос это​ только формулы ВПР​

​ иметь таблицу замен.​​ значений на другие.​ качестве первого аргумента​ для работы с​
​ так быстрее. :)​Закончив поиск, воспользуйтесь кнопкой​
​ уже выше.​
​Открывается окно дополнительных параметров.​
​Как видим, в нижней​ worksheet = workbook.Sheets[«Лист1»];​ void textBox24_TextChanged(object sender,​
​ или 3 раза​
​AlexM​У меня есть в​
​ «,», «»)) *​ текст с кодом​
​ на 0, в​ Проще менять таблицу,​
​ Все работает но​ данной функции, которую​ регулярными выражениями, однако​Функция ПОДСТАВИТЬ в Excel​
​ Закрыть, чтобы выйти​Урок: Как сделать поиск​ Оно практически идентично​ части окна имеются​
​ //открываетм лист с​ EventArgs e) {​ дел — ввел​: Формула для ячейки​
​ эксель есть таблица,​ -1​ макроса​ файле есть формула​ чем макрос. При​ некорректно. Меняются номера​ требуется заменить. Аргумент​
​ ее можно добавить​ выполняет динамическую замену​ из диалогового окна​ в Экселе​ окну расширенного поиска.​
​ кнопки замены –​ базой str =​ text2 = textBox24.Text;​ число — нажал​ А1 Код =ДВССЫЛ(АДРЕС(C$1;СТРОКА(A3);;;»[baza.xlsx]Лист1″))​ в которой есть​
​ваш код делает​Вроде бы все​ сумм.Она должна остаться.​ таблице можно и​ моделей изделий, которые​ обязателен для заполнения.​ следующим способом:​ определенной части строки​ Найти и Заменить.​Как видим, программа Microsoft​ Единственное отличие –​
​«Заменить всё»​
​ textBox23.Text; worksheet.Cells[str, «Q»]​
​ } private void​ энтер​ В С1 -​

​ значения «1» и​​ то же самое,​ что нужно положил​Михаил С.​

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

​ на указанное новое​​Вы можете получить доступ​ Excel предоставляет довольно​ присутствие блока настроек​и​

​ = label7.Text !=​​ button2_Click(object sender, EventArgs​

​и того 11​​ 2, 3 или​ «2». Мне нужно​ но не изменяет​Hugo​: CTRL+H​ с замененными данными​ и цифр. Например​
​ заполнения аргумент, содержащий​Вставить исходный код функции​ значение и возвращает​ к команде Найти​ функциональный и удобный​«Заменить на»​«Заменить»​ null ? label7.Text.ToString()​
​ e) { label6.Text​ ссылок = 55+​ другой номер строки​ сделать так, чтобы​ ячейки, а записывает​: Скажите, Вам все​
​Наити *впр*​ и его значения​ KS-20 необходимо заменить​

​ текстовые данные, которые​​ (приведен ниже).​ новую строку, содержащую​ с помощью комбинации​ инструмент для поиска​.​
​, и кнопки поиска​

​ : «»; //​​ = label2.Text +​ операций на лист,​ ссылки.​
​ все значения «1»,​

​ значения в окне​​ строки нужны, или​заменить 0 Прикрепленные​ переставить в столбец​
​ на FT-18. KS-200​ будут вставлены на​Выполнить данный макрос и​ замененную часть текста.​ клавиш Ctrl+F на​ и замены данных​Вся нижняя часть окна​

​ —​​ записываем в базу​

​ textBox23.Text; label20.Text =​​ а всего получается​Savra​

​ заменились на «2»,​​ Immediate.​ только те, где​ файлы Ирина.png (20.96​ А​ заменить на FT-80.​ место заменяемой части​

​ закрыть редактор кода.​​ Благодаря этой функции​ клавиатуре.​ в таблицах. Если​
​ отвечает за поиск​«Найти всё»​
​ ссылку на адрес​ label9.Text + textBox23.Text;​
​ более 20000 операций​: не могу понять​ а значения «2»​А лучше используйте​ в начале цифра​ КБ)​deus_russia​ При этом первый​ строки.​Код функции:​ можно подставлять значения​Чтобы увидеть дополнительные параметры​ нужно заменить абсолютно​ данных, замену которых​и​ файла workbook.Save(); workbook.Close();​ label21.Text = label10.Text​ нужно проделать,​ как это работает?​ заменились на «1»​ Макрос5 Александра (​ стоит?​Ирина​: Pelena, да такой​ пример заменяется правильно,​[номер_вхождения] – числовое значение,​Public Function RegExpExtract(Text As​ из другой ячейки.​ поиска и замены,​ все однотипные значения​ следует выполнить. Тут​«Найти далее»​ } } }​ + textBox23.Text; label22.Text​а через поиск/замену​ логика какая?​ Своеобразная инвертация.​Борода​Можно подойти к​: Спасибо!​ способ работает. Но​ а второй, считает​ характеризующее номер вхождения​ String, Pattern As​ Рассмотрим возможности функции​ нажмите кнопку Параметры​ на конкретное выражение,​ можно установить, где​. Жмем на кнопку​Savra​ = label11.Text +​ тоже самое ток​Хотя подходит и​Стандартная замена тут​) — текстовик открывается​ задаче с другой​alexsampler​ мне в будущем,​ что KS-200 можно​ старого текста, который​ String, Optional Item​ на конкретных примерах​ в диалоговом окне​ то это можно​ искать (на листе​«Найти далее»​: в этой строке​ textBox23.Text; label23.Text =​ путь длиннее….​ работает, но….​ бессильна, потому прошу​ сразу в нужном​ стороны…​: Excel 2003​ возможно, нужно будет​ заменить как KS-20​ требуется заменить на​ As Integer =​ в Excel.​ Найти и заменить.​ сделать нажатием только​ или во всей​.​ сайт почемуто убрал​ label12.Text + textBox23.Text;​Виктор Михалыч​напишу отдельное сообщение…​ помощи здесь.​ виде.​alexsampler​Очень нужна помощь!​ делать замену следующую:​ при этом добавив​ фрагмент нового текста.​ 1) As String​Пример 1. В результате​Бывают случаи, когда допущена​ одной кнопки. В​ книге) и как​После этого производится поиск​ 2 крайних слеша​ label24.Text = label13.Text​: А тот вариант​вот какая незадача….​Полосатый жираф алик​Только вот он​: nilem, спасибо наиогромнейшее!!!​Программа выдает файл​ «KS-20″->»KS-20 (FT-18)». А​ «0» в конце.​ Возможные варианты записи:​On Error GoTo​ расчетов, произведенных в​ ошибка, повторяющаяся во​ случае, если выборку​ искать (по строкам​ по документу искомого​string name1 =​ + textBox23.Text; label25.Text​ что в файле?​Если файл с​: А что там​ убрал Макрос5 и​два вопроса​ с массивом, где​ при такой замене,​ Подскажите как заменять​Аргумент явно не указан.​ ErrHandl​ некотором приложении, были​ всей книге Excel.​ нужно делать более​ или по столбцам).​ слова. По умолчанию,​ «C:\Baza\Info\»; //так должно​ = label14.Text +​ Тут один раз​ базой не открыт,​ КРОМЕ «1» и​ оставил Sub tt()​если не лень​ цифры с отрицательным​ у меня снова​ точные данные без​ Функция ПОДСТАВИТЬ определит​Set regex =​ получены некоторые значения,​ Например, неправильно написано​ подробно, то и​ В отличие от​ направление поиска производится​

​ быть​​ textBox23.Text; label26.Text =​ прописать нужно и​ то ссылки вида:​
​ «2»? Или, может​ (у меня вылезает​напишите пожалуйста, что​

CyberForum.ru

​ значением отображаются как​

Функция ПОДСТАВИТЬ в 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. Выполнить данный макрос и закрыть редактор кода.

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

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

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

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

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+»)&»-Н» – новый номер, содержащий исходное значение и символы «-Н».

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

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

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

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

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

Примечания:

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

Быстрая замена значений по условию в Excel

Добрый день, уважаемые читатели блога! Сегодня поговорим о быстрой замене значений в таблице Excel. Зачем нам это нужно, когда есть формулы ЕСЛИ и условное форматирование? Ответ прост — для экономии времени. Но ведь можно ещё быстрее. Ниже будет пример с небольшой таблицей, где хочется сразу выделить или поместить нужное значение.

Есть две таблицы, одна оригинал, вторая для изменений. Все ученики кто получил 4 и выше имеют результат «Прошёл», у кого 3 и ниже — «Не прошёл».

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

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

• вкладка «Разработчик», блок кнопок «Код»;
• жмём «Visual Basic»;
• добавляем новый модуль Insert -> Module.

Вставляем следующий код.

Sub Result()
Dim cell As Range
‘ Проверка каждой ячейки диапазона на прохождение
For Each cell In Range(«I2:L10»).Cells
If cell.Value
cell.Value = «Не прошёл»
ElseIf cell.Value >= 4 Then
cell.Value = «Прошёл»
End If
Next
End Sub

В этом случае используется связка из команд IF и ElseIf, что позволяет нам несколько расширить понимание формулы ЕСЛИ. Напомню, что макрос будет применён только к ячейкам из диапазона I2:L10!

Запускаем выбор макросов с помощью сочетания клавиш Alt+F8 и нажимаем кнопку «Выполнить».

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

Таким образом решается некая ограниченность формулы ЕСЛИ, ведь условия в макросе можно продолжить и подобрать свои, всего лишь изменив значения.

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

Примечание: Компонент Power Query в Excel 2016 называется Скачать и преобразовать. Информация в этой статье относится к обоим компонентам. Дополнительные сведения см. в статье Функция «Скачать и преобразовать» в Excel 2016.

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

В Power Query можно заменить одно значение другим в выбранных столбцах.

Задача

Замена текстовых значений

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

На ленте Редактора запросов выберите на вкладке Преобразование Замена значений.

С помощью контекстного меню Редактора запросов:

Щелкните текстовую ячейку правой кнопкой мыши и выберите команду Замена значений.

Чтобы заменить текстовые значения:

В диалоговом окне Замена значений введите значения в поля Значение для поиска и Заменить на.

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

Нажмите кнопку ОК.

Замена числовых, логических значений или значений даты и времени

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

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

С помощью контекстного меню Редактора запросов:

Щелкните ячейку с числовым значением или значением даты и времени правой кнопкой мыши и выберите команду Замена значений.

Чтобы заменить числовые, логические значения или значения даты и времени:

В диалоговом окне Замена значений введите значения в поля Значение для поиска и Заменить на.

Нажмите кнопку ОК.

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