Excel замена по шаблону

Одной из самых трудоемких и неприятных задач при работе с текстом в Excel является парсинг — разбор буквенно-цифровой «каши» на составляющие и извлечение из нее нужных нам фрагментов. Например:

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

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

• Использовать встроенные текстовые функции Excel для поиска-нарезки-склейки текста: ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), СЦЕПИТЬ (CONCATENATE) и ее аналоги, ОБЪЕДИНИТЬ (JOINTEXT), СОВПАД (EXACT) и т.д. Этот способ хорош, если в тексте есть четкая логика (например, индекс всегда в начале адреса). В противном случае формулы существенно усложняются и, порой, дело доходит даже до формул массива, что сильно тормозит на больших таблицах.
• Использование оператора проверки текстового подобия Like из Visual Basic, обернутого в пользовательскую макро-функцию. Это позволяет реализовать более гибкий поиск с использованием символов подстановки (*,#,? и т.д.) К сожалению, этот инструмент не умеет извлекать нужную подстроку из текста — только проверять, содержится ли она в нем.

Кроме вышеперечисленного, есть еще один подход, очень известный в узких кругах профессиональных программистов, веб-разработчиков и прочих технарей — это регулярные выражения (Regular Expressions = RegExp = «регэкспы» = «регулярки»). Упрощенно говоря, RegExp — это язык, где с помощью специальных символов и правил производится поиск нужных подстрок в тексте, их извлечение или замена на другой текст. Регулярные выражения — это очень мощный и красивый инструмент, на порядок превосходящий по возможностям все остальные способы работы с текстом. Многие языки программирования (C#, PHP, Perl, JavaScript…) и текстовые редакторы (Word, Notepad++…) поддерживают регулярные выражения.

Microsoft Excel, к сожалению, не имеет поддержки RegExp по-умолчанию «из коробки», но это легко исправить с помощью VBA. Откройте редактор Visual Basic с вкладки Разработчик (Developer) или сочетанием клавиш Alt+F11. Затем вставьте новый модуль через меню Insert — Module и скопируйте туда текст вот такой макрофункции:

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

Теперь можно закрыть редактор Visual Basic и, вернувшись в Excel, опробовать нашу новую функцию. Синтаксис у нее следующий:

=RegExpExtract( Txt ; Pattern ; Item )

где

• Txt — ячейка с текстом, который мы проверяем и из которого хотим извлечь нужную нам подстроку
• Pattern — маска (шаблон) для поиска подстроки
• Item — порядковый номер подстроки, которую надо извлечь, если их несколько (если не указан, то выводится первое вхождение)

Самое интересное тут, конечно, это Pattern — строка-шаблон из спецсимволов «на языке» RegExp, которая и задает, что именно и где мы хотим найти. Вот самые основные из них — для начала:

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

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

Извлекаем числа из текста

Для начала разберем простой случай — нужно извлечь из буквенно-цифровой каши первое число, например мощность источников бесперебойного питания из прайс-листа:

Логика работы регулярного выражения тут простая: d — означает любую цифру, а квантор + говорит о том, что их количество должно быть одна или больше. Двойной минус перед функцией нужен, чтобы «на лету» преобразовать извлеченные символы в полноценное число из числа-как-текст.

Почтовый индекс

На первый взгляд, тут все просто — ищем ровно шесть цифр подряд. Используем спецсимвол d для цифры и квантор {6} для количества знаков:

Однако, возможна ситуация, когда левее индекса в строке стоит еще один большой набор цифр подряд (номер телефона, ИНН, банковский счет и т.д.) Тогда наша регулярка выдернет из нее первых 6 цифр, т.е. сработает некорректно:

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

Телефон

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

ИНН

Тут чуть сложнее, т.к. ИНН (в России) бывает 10-значный (у юрлиц) или 12-значный (у физлиц). Если не придираться особо, то вполне можно удовлетвориться регуляркой d{10,12}, но она, строго говоря, будет вытаскивать все числа от 10 до 12 знаков, т.е. и ошибочно введенные 11-значные. Правильнее будет использовать два шаблона, связанных логическим ИЛИ оператором | (вертикальная черта):

Обратите внимание, что в запросе мы сначала ищем 12-разрядные, и только потом 10-разрядные числа. Если же записать нашу регулярку наоборот, то она будет вытаскивать для всех, даже длинных 12-разрядных ИНН, только первые 10 символов. То есть после срабатывания первого условия дальнейшая проверка уже не производится:

Это принципиальное отличие оператора | от стандартной экселевской логической функции ИЛИ (OR), где от перестановки аргументов результат не меняется.

Артикулы товаров

Во многих компаниях товарам и услугам присваиваются уникальные идентификаторы — артикулы, SAP-коды, SKU и т.д. Если в их обозначениях есть логика, то их можно легко вытаскивать из любого текста с помощью регулярных выражений. Например, если мы знаем, что наши артикулы всегда состоят из трех заглавных английских букв, дефиса и последующего трехразрядного числа, то:

Логика работы шаблона тут проста. [A-Z] — означает любые заглавные буквы латиницы. Следующий за ним квантор {3} говорит о том, что нам важно, чтобы таких букв было именно три. После дефиса мы ждем три цифровых разряда, поэтому добавляем на конце d{3}

Денежные суммы

Похожим на предыдущий пункт образом, можно вытаскивать и цены (стоимости, НДС…) из описания товаров. Если денежные суммы, например, указываются через дефис, то:

Паттерн d с квантором + ищет любое число до дефиса, а d{2} будет искать копейки (два разряда) после.

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

Автомобильные номера

Если не брать спецтранспорт, прицепы и прочие мотоциклы, то стандартный российский автомобильный номер разбирается по принципу «буква — три цифры — две буквы — код региона». Причем код региона может быть 2- или 3-значным, а в качестве букв применяются только те, что похожи внешне на латиницу. Таким образом, для извлечения номеров из текста нам поможет следующая регулярка:

Время

Для извлечения времени в формате ЧЧ:ММ подойдет такое регулярное выражение:

После двоеточия фрагмент [0-5]d, как легко сообразить, задает любое число в интервале 00-59. Перед двоеточием в скобках работают два шаблона, разделенных логическим ИЛИ (вертикальной чертой):

• [0-1]d — любое число в интервале 00-19
• 2[0-3] — любое число в интервале 20-23

К полученному результату можно применить дополнительно еще и стандартную Excel’евскую функцию ВРЕМЯ (TIME), чтобы преобразовать его в понятный программе и пригодный для дальнейших расчетов формат времени.

Проверка пароля

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

Проверку можно организовать с помощью вот такой несложной регулярки:

По сути, таким шаблоном мы требуем, чтобы между началом (^) и концом ($) в нашем тексте находились только символы из заданного в квадратных скобках набора. Если нужно проверить еще и длину пароля (например, не меньше 6 символов), то квантор + можно заменить на интервал «шесть и более» в виде {6,}:

Город из адреса

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

Давайте разберем этот шаблон поподробнее.

Если вы прочитали текст выше, то уже поняли, что некоторые символы в регулярных выражениях (точки, звездочки, знаки доллара и т.д.) несут особый смысл. Если же нужно искать сами эти символы, то перед ними ставится обратная косая черта (иногда это называют экранированием). Поэтому при поиске фрагмента «г.» мы должны написать в регулярке г. если ищем плюсик, то + и т.д.

Следующих два символа в нашем шаблоне — точка и звездочка-квантор — обозначают любое количество любых символов, т.е. любое название города.

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

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

Имя файла из полного пути

Еще одна весьма распространенная ситуация — вытащить имя файла из полного пути. Тут поможет простая регулярка вида:

Тут фишка в том, что поиск, по сути, происходит в обратном направлении — от конца к началу, т.к. в конце нашего шаблона стоит $, и мы ищем все, что перед ним до первого справа обратного слэша. Бэкслэш заэкранирован, как и точка в предыдущем примере.

P.S.

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

Для анализа и разбора чужих регулярок или отладки своих собственных есть несколько удобных онлайн-сервисов: RegEx101, RegExr и др.

К сожалению, не все возможности классических регулярных выражений поддерживаются в VBA (например, обратный поиск или POSIX-классы) и умеют работать с кириллицей, но и того, что есть, думаю, хватит на первое время, чтобы вас порадовать.

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

Ссылки по теме

• Замена и зачистка текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
• Поиск и подсветка символов латиницы в русском тексте
• Поиск ближайшего похожего текста (Иванов = Ивонов = Иваноф и т.д.)

You can…

1. Exit Excel
2. Make a copy of your Excel file and add to the extension
   (i.e. .xls or .xlsm) .xml.zip. For example, test.xls
   becomes test.xls.xml.zip.
3. Unzip the .zip file. Yes, Excel files
   are really .zip files.

This will give you a directory named test.xls.xml (or test.xlsm.xml) that contains the following directory structure: xlworksheets.

In the worksheets directory are your Excel worksheets, in XML format. They are not formatted for readability, so use an editor that is capable of «pretty» formatting the XML (I use EditPlus or XMLSpy. There are many out there.). Now that you can read the XML, you can use the regular expression feature of the editor to make changes.

When you are done, go to the test.xls.xml (or test.xlsm.xml) directory. You will see the _rels, docProps and xl subdirectories and the [Content_Types].xml file. Select everything, right click and choose Send to > Compressed (.zip) folder. Make sure there are no .bak files left over by your text editor (in the worksheets subdirectory). If they’re there, delete them before creating the new .zip file. Now rename the extension of this new .zip file to .xls (or .xlsm).

You can now open this file in Excel. Excel won’t mind that you’ve «pretty» formatted the XML.

I have done this exact procedure many times, never with any problems (unless I accidently left the .bak files laying around).

One word of warning, Excel cells that contain just text (i.e. no formulas) store the text in a different file and reference them from within the worksheet file, so you won’t see that text there. Any text contained in a formula (i.e. CONCAT("ABC", "DEF")) is preserved in the worksheet.

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

​Смотрите также​ авто; | «C»2″​ — вкладка «главная»​ (160 вместо 32).​и​ новом слове, в​ ошибок.​ отдельные из них,​«Заменять»​ вообще.​ а вместо «€»​ примере мы воспользуемся​ и заменить.​

​ и замена происходит​ по тем значениям,​

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

​.​«Найти и заменить»​Бывают ситуации, когда в​ — авто; |​ — бокс «Редактирование»​ Его не получается​ЗАМЕНИТЬ (REPLACE)​ качестве нового текста​Автор: Максим Тютюшев​ можно отключить, просто​указываем набор символов,​Переходим во вкладку​ — (e). К​ командой Заменить, чтобы​В нашем примере мы​ автоматически, согласно введенным​ которые указываются в​Замена значения будет выполнена,​во вкладке​ документе нужно заменить​

​ «C»3» — моторы;​ — «найти и​ удалить стандартными средствами​. Эти функции во​ установили функцию ПРАВСИМВ​Функция ЗАМЕНИТЬ, входит в​ сняв галочки около​

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

​ который будет восприниматься​«Файл»​ счастью, в Microsoft​ исправить список адресов​ воспользуемся командой Найти,​ данным, или же​ строке формул при​ а пользователь может​«Заменить»​ один символ (или​ | «C»4» -​

1. ​ выделить» — выпадающее​​ — заменой через​​ многом похожи, но​ с параметрами ячейки​​ состав текстовых функций​​ соответствующих параметров и​​ программой как ошибочный.​​;​ Excel имеется функция​ электронной почты.​​ чтобы отыскать требуемое​​ жмем на кнопку​

   

2. ​ выделении ячейки. Кроме​​ дальше продолжать в​​. В поле​​ группу символов) на​​ авто; | «C»5″​​ меню — заменить…​​ диалоговое окно Ctrl+H​ имеют и несколько​ А3 и взятием​ MS Excel и​​ нажав на кнопку​​ В поле​Выбираем раздел​ автозамены, которая автоматически​

   ​На вкладке Главная нажмите​ имя в длинном​ «Найти все», и​​ того, тут же,​​ поисковой выдаче искать​​«Найти»​​ другой. Причин может​ — моторы;​​Сергей шишлов​​ или функцией удаления​​ принципиальных отличий и​​ последних двух символов​​ предназначена для замены​​«OK»​

   

3. ​«На»​«Параметры»​ заменяет вышеописанные примеры​ команду Найти и​ списке сотрудников.​ отдельно производим замену​ путем установки или​ нужные ему результат​вводим число, слова​ быть множество, начиная​​Идентичный пример прикрепляю​​: Вот макрос​

   

4. ​ лишних пробелов​ плюсов-минусов в разных​ «ок».​​ конкретной области текстовой​​.​пишем слово или​.​

​ на правильные соответствия,​ выделить, а затем​

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

   

2. ​ от банальной ошибки,​ файлом excel во​Поиск и замена​СЖПРОБЕЛЫ (TRIM)​ ситуациях. Давайте подробно​Далее нажимаем на кнопку​ строки, в которой​Кроме того, функция автозамены​ символ, на которое​Далее переходим в подраздел​ а также исправляет​ из раскрывающегося списка​​ Найти Вы выделите​​ по тому алгоритму,​

   

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

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

​. Поможет наша функция​ и на примерах​ «ОК» и получаем​ находится исходный текст​ имеет собственный словарь​ будет происходить замена.​«Правописание»​​ наиболее распространенные ошибки​​ выберите пункт Заменить.​

​ одну ячейку, то​

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

​ Excel будет выполнять​ уже выше.​ букв, искать ли​

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

​ кнопки. Для этого​«Заменить на»​ пробелов. Давайте выясним,​название категорий​

1. ​Dim cell As​, которой можно заменить​ из них.​Пример 2. Рассмотрим еще​

   

2. ​ текста (новый текст).​ располагаются те слова​«Добавить»​Кликаем по кнопке​Скачать последнюю версию​​ и заменить. Введите​​ поиск во всем​

   

   ​Урок: Как сделать поиск​ точное соответствие в​ после ввода заменяемых​выполняем ввод данных,​ как быстро заменить​в​ Range​ неразрывный пробел на​Её синтаксис таков:​ один небольшой пример.​С целью детального изучения​ и символы, заменять​.​«Параметры автозамены»​ Excel​ текст, который Вы​ листе. А если​ в Экселе​ ячейках.​ значений, и значений,​ на которые будет​ символы в программе​колонке «C»​’ Просмотр всех​ обычный или на​=ПОДСТАВИТЬ(Ячейка; Старый_текст; Новый_текст; Номер_вхождения)​ Предположим у нас​

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

   

   ​Как видим, программа Microsoft​Также, можно указать среди​ на которые производится​ производиться замена.​

   

   ​ Microsoft Excel.​, на​ ячеек диапазона G1:K20​ пустую текстовую строку,​где​ в ячейках табличного​ рассмотрим один из​ даже если в​

   

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

   

   ​ID категории​ и замена искомого​ т.е. удалить:​​Ячейка​​ процессора Excel имеются​ простейших примеров. Предположим​ генеральных настройках включено​ варианты.​ ищем пункт​

   

   ​ ошибки в написании​Введите текст, на который​ диапазона​ функциональный и удобный​ будет производиться поиск.​«Заменить все»​ части окна имеются​ Excel​

3. ​из колонки​ текста​Если нужно подсчитать количество​- ячейка с​ столбцы с мужскими​ у нас имеется​ правило, обозначающее, что​Кроме того, в этом​«Заменять при вводе»​ слов. Каждому такому​ требуется заменить найденный,​На вкладке Главная воспользуйтесь​ инструмент для поиска​ Для этого нужно​.​ кнопки замены –​Конечно, наиболее простым способом​»B»​

​For Each cell​ слов в ячейке,​

​ текстом, где производится​ фамилиями и именами​ несколько слов в​ данное слово или​ же окне существует​. Снимаем с него​ слову подобрано правильное​ в поле Заменить​ командой Найти и​ и замены данных​ кликнуть по кнопке​Процедура выполняется практически моментально.​«Заменить всё»​ заменить один символ​, соответствующей названию​ In [G1:K20]​ то можно применить​ замена​ сотрудников некоторой фирмы.​

​ разных столбцах, необходимо​

lumpics.ru

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

​ выражение подлежит замене.​ вкладка​ галочку и жмем​ соответствие. Если пользователь​ на. А затем​ выделить, а затем​ в таблицах. Если​ «Формат» напротив параметра​Плюсы этого способа —​и​ на другой является​колонке «A»​If cell.Value Like​ простую идею: слов​Старый_текст​

​ Необходимо заменить их​ получить новые слова​Для перехода в этот​«Автозамена математическими символами»​ на кнопку​ вводит неправильный вариант,​ нажмите Найти далее.​ из раскрывающегося списка​ нужно заменить абсолютно​ «Найти».​ быстрота и удобство.​«Заменить»​ ручное редактирование ячеек.​.​ «*Доход*» Then​ на единицу больше,​- текст, который​ буквы в определенных​ используя исходные. Для​ словарь жмем на​

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

​. Тут представлен список​«OK»​ вследствие опечатки или​Если значение будет найдено,​ выберите пункт Найти.​

​ все однотипные значения​После этого откроется окно,​ Главный минус состоит​, и кнопки поиска​ Но, как показывает​MrRuslanBB​cell.Value = «Выручка»​ чем пробелов (при​ надо найти и​

1. ​ местах так, чтобы​ данного примера помимо​ кнопку​ значений, при вводе​.​
2. ​ ошибки, то он​ то содержащая его​Появится диалоговое окно Найти​ на конкретное выражение,​ в котором можно​ в том, что​
3. ​ —​ практика, далеко не​: Для конкретного примера​cell.Interior.Color = RGB(255,​
4. ​ условии, что нет​ заменить​ преобразовать их в​ основной нашей функции​«Исключения…»​ заменяемых на математические​Для того, чтобы опять​ автоматически заменяется приложением​ ячейка будет выделена.​ и заменить. Введите​
5. ​ то это можно​ указать формат ячеек​ вы должны быть​«Найти всё»​

​ всегда этот способ​ Код =ВПР(C1;$A$1:$B$2;2;0)​ 255, 0)​ лишних пробелов). Соответственно,​Новый_текст​

​ женские.​ ЗАМЕНИТЬ используем также​.​ символы, в том​ включить автозамену, соответственно,​

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

​ на верный. В​Посмотрите на текст и​ данные, которые необходимо​ сделать нажатием только​ для поиска.​ уверены, что введенные​и​ самый легкий в​MrRuslanBB​Else​ формула для расчета​- текст, на​Создадим на листе рабочей​ функцию ПРАВСИМВ –​Открывается окно исключений. Как​ числе на те,​

1. ​ устанавливаем галочку обратно​ этом и состоит​ убедитесь, что Вы​ отыскать. В нашем​ одной кнопки. В​
2. ​Единственной настройкой значения для​ символы нуждаются в​«Найти далее»​ масштабных таблицах, где​: Не получается.​
3. ​cell.Interior.Color = RGB(255,​ будет простой:​ который заменяем​ книги Excel табличку​ данная функция служит​
4. ​ видим, в нем​ которые используются в​ и снова жмем​
5. ​ основная суть автозамены.​ согласны заменить его.​ примере мы введем​
6. ​ случае, если выборку​ вставки будет являться​ замене во всех​
   • ​. Жмем на кнопку​ количество однотипных символов,​
   • ​Результат прикрепляю во​ 255, 255)​Если предполагается, что в​Номер_вхождения​ со фамилией и​ для возврата определенного​

   

7. ​ имеется две вкладки.​ формулах Excel. Действительно,​ на кнопку​К основным ошибкам, которые​
8. ​Если согласны, тогда выберите​
9. ​ имя сотрудника.​ нужно делать более​ все тот же​ ячейках. Если в​

​«Найти далее»​ которые требуется изменить,​
​ вложенном файле.​
​End If​

​ ячейке могут находиться​

office-guru.ru

Функция автозамены в Microsoft Excel

​- необязательный аргумент,​ именем, как показано​ числа знаков от​ В первой из​ не каждый пользователь​«OK»​ устраняет данная функция,​ одну из опций​Нажмите Найти далее. Если​ подробно, то и​ формат ячеек. Для​ предыдущих способах была​.​ может достигать очень​P. S. Этот​Next​ и лишние пробелы,​ задающий номер вхождения​ на рисунке:​ конца строки текста.​ них расположены слова,​ сможет ввести знак​.​ относятся следующие: начало​ замены:​ данные присутствуют на​

​ эта возможность в​ выбора формата вставляемого​

Принципы работы автозамены

​ возможность найти и​После этого производится поиск​ большого количества. Даже​ способ подойдёт для​End Sub​ то формула будет​ старого текста на​Далее на этом же​ То есть, например,​ после которых точка,​ α (альфа) на​Бывают случаи, когда пользователь​ предложения со строчной​

​Заменить: исправляет по одному​ листе, то они​ полной мере предоставлена​ значения жмем на​ отобрать нужные ячейки​ по документу искомого​ на поиск нужных​​ присваивания ID в​​Ми​ чуть посложнее, но​ замену​

Отключение и включение автозамены

​ листе рабочей книги​ у нас есть​ ещё не означает​ клавиатуре, но каждый​ вводит число с​ буквы, две прописные​ значению зараз.​ будут выделены.​ в данном табличном​ одноименную кнопку напротив​ для изменения, то​ слова. По умолчанию,​ ячеек может быть​ 19 тыс. ячеек?​: в Меню: Главная​ идея — та​Обратите внимание, что:​ подготовим область для​ два слова: молоко​ конец предложения, и​ сможет ввести значение​ точками, а оно​ буквы в слове​Заменить все: исправляет все​Если Вы еще раз​ процессоре.​ параметра «Заменить на…».​ при использовании данного​ направление поиска производится​ потрачено значительное количество​MrRuslanBB​

1. ​ _ Найти и​​ же.​​Если не указывать последний​

   

2. ​ размещения нашего результата​​ и каток, в​​ то, что следующее​

   

3. ​ «alpha», которое автоматически​​ автоматически исправляется на​​ подряд, неправильная раскладка​

   

4. ​ варианты искомого текста​​ нажмете на кнопку​​Автор: Максим Тютюшев​

   

5. ​Открывается точно такое же​ варианта такая возможность​​ построчно. Курсор останавливается​​ времени, не говоря​: В первом файле​ выделить_Заменить​​Если нужно вытащить из​​ аргумент​

​ – измененных фамилий​ результате мы должны​ слово должно начинаться​ преобразится в нужный​ дату, хотя это​​Caps Lock​​ в книге. В​

Проблема с автозаменой даты

​ Найти далее, то​Поиск и замена в​ окно, как и​ исключается.​ на первом же​ уже о временных​ пример был не​MrRuslanBB​ ячейки только первые​Номер_вхождения​ и имен. Заполним​ получить слово молоток.​ с прописной буквы.​ символ. По аналогии​​ ему не нужно.​​, целый ряд других​​ нашем примере мы​​ увидите следующий вариант​ Excel – это​ в предыдущем случае.​​Урок: как заменить точку​​ результате, который совпал.​

​ затратах на редактирование​ такой как во​: Добрый день!​

Редактирование списка автозамены

​ два слова (например​, то будут заменены​ ячейки двумя типами​​ Это, главным образом,​ пишется бета (beta),​ В этом случае,​ типичных опечаток и​ воспользуемся этой опцией​ поиска. Вы также​

1. ​ достаточно мощный и​ Тут устанавливается, как​
2. ​ на запятую в​​ Для замены содержимого​​ каждой из них.​ втором. Вариант для​У меня есть​ ФИ из ФИО),​​ все вхождения старого​​ формул как показано​Создадим на листе рабочей​ различные сокращения (например,​ и другие знаки.​​ совсем не обязательно​​ ошибок.​

​ для экономии времени.​ можете выбрать Найти​ удобный инструмент, который​

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

​ список каждый пользователь​ Чтобы исправить это,​ умолчанию автозамена всегда​ количество замен, которые​ увидеть все варианты,​ при необходимости и​ данных. Можно установить​​ расширенного поиска и​​«Заменить»​

​ «Найти и заменить»,​

Основные параметры

​MrRuslanBB​Колонка «A»​У нее простая логика:​ «Маши» заменены на​ формуле мы используем​ словами, как показано​Во второй вкладке располагаются​ может добавить свои​ выделяем область ячеек,​ включена. Поэтому, если​ будут сделаны. Нажмите​​ которые для Вас​​ заменять, информацию на​ выравнивание, числовые форматы,​ замены по дополнительным​.​ который поможет быстро​: Пример я создал​- содержит​​заменяем второй пробел на​​ «Олю»).​

Исключения

​ оператор «&» для​ на рисунке:​ исключения, в которых​ собственные соответствия, так​ в которой собираемся​ вы постоянно или​ ОК для продолжения.​ отыскал Excel.​ рабочем листе. В​ цвет ячеек, границы,​ параметрам.​

​Чтобы продолжить поиск данных,​ отыскать нужные ячейки,​ на быструю руку,​​ID категории​​ какой-нибудь необычный символ​

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

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

​ очень большого размера.​2.​ПОДСТАВИТЬ (SUBSTITUTE)​ его номер задается​ чтобы преобразовать ее​ для размещения нашего​ подряд. По умолчанию,​ основном словаре.​«Главная»​ то её нужно​ выйти из диалогового​ из диалогового окна​ поиск в заданной​ пункт из выпадающего​

​ и заменить» жмем​

lumpics.ru

Примеры работы с текстовой функцией ЗАМЕНИТЬ в Excel

​«Найти далее»​Простая замена с поиском​Вторая функция помогла)​Колонка «B»​ищем позицию символа #​ в последнем аргументе​ в женскую. Для​ результата – полученного​ единственным словом, которое​

Как работает функция ЗАМЕНИТЬ в Excel?

​Удалить любое соответствие в​ищем блок настроек​ принудительно отключить. Например,​ окна Найти и​ Найти и Заменить.​ области документа Excel,​ списка под кнопкой​ на кнопку Параметры.​. Тем же способом​ предполагает замену одного​Благодарю, Вас!​- содержит​ функцией​ (в ячейке С2​ решения данной задачи​ слова «молоток», как​ представлено в данном​ этом словаре тоже​«Число»​ это может быть​ заменить.​Вы можете получить доступ​ а также изменять​

​«Формат»​

Функция заменить в Excel и примеры ее использования

1. ​Открывается окно дополнительных параметров.​ меняем следующий результат,​ последовательного и фиксированного​AlexM​название категории​
2. ​НАЙТИ (FIND)​ только вторая «Маша»​ можно было бы​ показано ниже на​ разделе словаря, является​ очень просто. Выделяем​. В выпадающем списке,​ вызвано тем, что​Урок подготовлен для Вас​ к команде Найти​
3. ​ найденную информацию на​, можно установить формат​

​ Оно практически идентично​ и т.д.​ набора символов (числа,​: Доброе утро,​;​вырезаем все символы от​ заменена на «Олю»).​ использовать функцию =СЦЕПИТЬ(B3;»а»)​ рисунке. Установим курсор​ «CCleaner». Но, можно​ тот элемент, автозамена​ расположенном в данном​ вам часто приходится​ командой сайта office-guru.ru​ с помощью комбинации​ требуемое значение.​ идентичный любой выбранной​ окну расширенного поиска.​Можно найти все удовлетворяющие​ слова, знаки, и​Fairuza​———​

​ начала строки до​Эта функция различает строчные​ вместо формулы =B3&»а»​

Как заменить часть текста в ячейке Excel?

​ в ячейке А6​ добавлять неограниченное количество​ которого нам не​ блоке, устанавливаем параметр​ умышленно писать слова​Источник: http://www.gcflearnfree.org/office2013/excel2013/7/full​ клавиш Ctrl+F на​При работе с большим​ ячейке на листе,​ Единственное отличие –​ запросу результаты сразу.​ т.д.) на другой​!​

1. ​Эти колонки дополняют друг​ позиции # функцией​ и прописные буквы​ – результат идентичный.​ и вызовем функцию​
2. ​ других слов и​ нужна, и жмем​«Текстовый»​ с ошибками, или​Автор/переводчик: Антон Андронов​ клавиатуре.​ количеством данных в​ достаточно только ее​ присутствие блока настроек​

​После ввода поискового запроса​ после того, как​Благодарю Вас, за​ друга​ЛЕВСИМВ (LEFT)​ (в ячейке С3​ Но сегодня настоятельно​ ЗАМЕНИТЬ:​ выражений, в качестве​ на кнопку​.​ указывать символы, которые​Автор: Антон Андронов​Чтобы увидеть дополнительные параметры​ Excel, иногда достаточно​ выделить.​«Заменить на»​ и заменяющих символов​ эти символы будут​ ниже напечатанную формулу:​;​

exceltable.com

Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

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

​ изображены на рисунке:​

​ же способом, о​

​.​

• ​ заменяться на даты​​ ошибочные, а автозамена​ различных документах можно​ нажмите кнопку Параметры​
• ​ конкретную информацию. И,​​ быть указание диапазона​Вся нижняя часть окна​«Найти все»​
• ​ специального встроенного инструмента​​ формула отлично сработала​Колонка «C»​
• ​ ошибку вместо орфографическую.​​ с маленькой буквы)​ как она имеет​Выбор данных параметров поясним​ котором шла речь​

​Удаление будет выполнено моментально.​

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

​ будет производиться поиск​ данных, замену которых​​Производится поиск всех релевантных​​Кликаем по кнопке​

Замена или удаление неразрывных пробелов

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

Подсчет количества слов в ячейке

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

​; — этих позиций​Ottvett​для наглядности.​ с простым и​ качестве нач_поз установили​ который помогает автоматически​ По умолчанию включены​

Извлечение первых двух слов

​ наоборот помогать ему.​ вам нужен, то​ клавиатуре просто отсутствуют,​ всей книге Excel.​ поиска. Вы можете​ выделить нужный диапазон​

​ искать (на листе​

1. ​ значение и адрес​ вкладке​ на названия, -​​ будет в разы​
2. ​: Нажмите Ctrl+H -​При выгрузке данных из​​ удобным оператором амперсанд.​
3. ​ число 5, так​ исправлять ошибки или​ следующие функции: исправление​​ Кроме перечня выражений,​

planetaexcel.ru

Можно ли в EXEL заменить сервисно одно слово на другое?

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

​ каждой ячейки, открывается​​«Главная»​ вычисляет не корректно.​ больше чем категорий!!!​

​ появится окно Замена.​​ 1С, копировании информации​Замена одного текста на​ как именно с​

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

​ книге) и как​​ в нижней части​
​в блоке настроек​В сообщение во​
​Пример:​
​ Там разберетесь​ с вебстраниц или​
​ другой внутри заданной​ пятой позиции слова​ вводе слов, символов​ подряд, установка первой​
​ автозамены по умолчанию,​ Но, если таких​
​ ими пользоваться, не​ необходимо изменить определенное​
​ в рабочей книге​
​ «Найти» и «Заменить​ искать (по строкам​
​ окна. Теперь можно​
​«Редактирование»​ вложении прикрепляю файл​
​1. «A»1» -​
​Дмитрий иоффик​
​ из документов Word​

​ текстовой строки -​​ «Молоко» мы символы​ или выражений в​ буквы в предложении​

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

​ каждый пользователь сам​​ вводимых вами данных​
​ каждый знает. Поэтому,​ слово или фразу​
​ Excel с помощью​​ на…» вписать соответствующие​​ или по столбцам).​​ кликнуть по любой​​. В появившемся после​
​ образец.​​ авто; | «A»2″​​: Там есть вроде​​ часто приходится иметь​​ весьма частая ситуация​
​ не берем для​​ Экселе. При правильной​ прописной, название дней​​ может добавлять собственные​
​ много, то прописывая​​ пользователи заменяют подобные​​ на другую. Вы​​ команды Найти, которая​ значения. Когда все​​ В отличие от​ из ячеек, в​ этого списке делаем​
​Подскажите пожалуйста в​
​ — моторы;​ фигня такая. Найти​ дело с неразрывным​
​ при работе с​ нашего итогового слова,​ настройке эта функция​
​ недели с прописной,​ варианты.​ их дважды, вы​ знаки наиболее очевидными,​ можете воспользоваться инструментом​ к тому же​ настройки указаны, выбираем​
​ обычного поиска, поиск​ которой мы хотим​ переход по пункту​
​ чем-же дело?​​2. «B»1» -​​ слово подпункт.​​ пробелом — спецсимволом,​​ данными в Excel.​​ число_знаков установили равным​​ станет хорошим помощником,​​ исправление случайного нажатия​​Открываем знакомое уже нам​​ теряете время. В​​ по их мнению,​

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

​ для замены можно​​ выполнить замену, и​
​«Заменить»​Пример некорректной работы​
​ 1234; | «B»2″​Sub-zero​ неотличимым от обычного​ Реализовать подобное можно​

​ 2, так как​​ и позволит значительно​Caps Lock​ окно параметров автозамены.​ этом случае лучше​ аналогами. Например, вместо​ чтобы быстро внести​

​ изменить данные с​​ Либо жмем на​ производить исключительно по​ нажать на кнопку​.​
​ формулы.Пробуйте так Код​
​ — 185255;​

​: у вас какой​​ пробела, но с​​ двумя функциями:​​ именно это число​
​ сэкономить время на​. Но, все эти​
​В поле​ временно отключить автозамену​ «©» пишут «(с)»,​ исправления. В нашем​ помощью инструмента Найти​ кнопку «Заменить все»,​ формулам, то есть,​«Заменить»​
​Открывается окно​ =ПРОСМОТР(2;1/(C1=$B$1:$B$1610);$A$1:$A$1610)​3. «C»1» -​
​ Офис? В 2007​ другим внутренним кодом​

​ПОДСТАВИТЬ (SUBSTITUTE)​ не учитывается в​ проверке и исправлении​

CyberForum.ru

​ функции, как и​