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

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

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

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

Её синтаксис такой:

=ПОДСТАВИТЬ( Ячейка ; Старый_текст ; Новый_текст ; Номер_вхождения )

  • Ячейка — ячейка с текстом, где делается замена
  • Старый_текст — текст, который нужно отыскать и поменять
  • Новый_текст — текст, на который заменяем
  • Номер_вхождения — необязательный аргумент, задающий номер вхождения старенького текста на подмену

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

Направьте внимание, что:

  • Если не указывать крайний аргумент Номер_вхождения, то будут изменены все вхождения старенького текста (в ячейке С1 — обе «Маши» изменены на «Олю»).
  • Если необходимо поменять лишь определенное вхождение, то его номер задается в крайнем аргументе (в ячейке С2 лишь 2-ая «Маша» заменена на «Олю»).
  • Эта функция различает строчные и строчные буковкы (в ячейке С3 замена не сработала, т.к. «маша» написана с малеханькой буковкы)

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

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

При выгрузке данных из 1С, копировании инфы с вебстраниц либо из документов Word нередко приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обыденного пробела, но с остальным внутренним кодом (160 заместо 32). Его не выходит удалить обычными средствами — подменой через диалоговое окно Ctrl + H либо функцией удаления излишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно поменять неразрывный пробел на обыденный либо на пустую текстовую строчку, т.е. удалить:

Удаление неразрывного пробела

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

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

Количество слов

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

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

Если необходимо вынуть из ячейки лишь 1-ые два слова (к примеру ФИ из ФИО), то можно применить формулу:

Как в Excel массово отыскать и поменять несколько значений на остальные

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

Жаркие клавиши

Сочетания кнопок ниже приметно ускорят работу с инвентарем:

  • Для пуска диалогового окна поиска — Ctrl + F
  • Для пуска окна поиска и подмены — Ctrl + H
  • Для выделения всех отысканных ячеек (опосля нажатия клавиши «отыскать все» — Ctrl + A
  • Для чистки всех отысканных ячеек — Ctrl + Delete
  • Для ввода одних и тех же данных во все отысканные ячейки — Ввод текста, Ctrl + Enter

Смотрите gif-примеры: тут мы производим поиск ячеек с предстоящим их редактированием. В отличие от подмены, редактирование отысканных ячеек дозволяет стремительно поменять их содержимое полностью.

Процедура «Отыскать и поменять» не работает

Я сам когда-то не один раз впадал в ступор в схожих ситуациях. Уверен и видишь своими очами, что разыскиваемый паттерн в данных есть, но Excel при выполнении процедуры поиска докладывает:

не удалось ничего найти по вашему запросу в Excel

Не удалось ничего отыскать по вашему запросу

Мы не нашли ничего, что нужно заменить - Excel

Мы не отыскали ничего, что необходимо было поменять

Итак вот, совет надавить клавишу «Характеристики» в обоих этих сообщениях вправду полезен. Там наверное активен чекбокс «Учесть регистр» либо «Ячейка полностью», которые мешают Excel отыскать разыскиваемое. Excel сохраняет конфигурацию крайнего поиска.

Статус опций «Учесть регистр» и «Ячейка полностью» виден опосля нажатия клавиши «Характеристики».

Подстановочные знаки, либо как отыскать «звездочку»

Сухая официальная справка по Excel докладывает, что можно употреблять подстановочные знаки «*» и «?». Что они означают несколько знаков, включая их отсутствие, и один хоть какой знак. И что их можно употреблять для соответственных процедур поиска.

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

  • Отыскивать ячейки, заканчивающиеся на определенный знак, слово либо текст
  • Аналогично, отыскивать ячейки, начинающиеся с определенного знака, слова либо текста
  • Отыскивать непустые ячейки

На примере ниже мы находим все двузначные числа, потом числа, заканчивающиеся и начинающиеся на 7, и, в конце концов, все непустые ячейки. Напомню, выделить все поисковые результаты помогает горячее сочетание кнопок Ctrl + A

Так как отыскать звездочку?

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

(тильда), находится обычно под кнопкой Esc . Это дозволяет экранировать «звездочку», как и вопросительный символ, и не принимать их как служебные знаки.

Интересно почитать:  Заполнение пустых ячеек в excel предыдущим значением

Замена нескольких значений на несколько

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

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

Эта задачка наиболее непростая, чем замена на одно значение. Как ни удивительно, функция «ЗАМЕНИТЬ» тут не подступает — она просит очевидного указания позиции заменяемого текста. Зато может посодействовать функция «ПОДСТАВИТЬ«.

Массовая замена при помощи функции «ПОДСТАВИТЬ»

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

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

Но у решения есть и свои недочеты:

  • Функция ПОДСТАВИТЬ регистрозависимая, что принуждает при подмене 1-го знака употреблять два его варианта — в верхнем и нижнем регистрах. Хотя, в неких вариантах, как пример на картинке выше, это и преимущество.
  • максимум 64 подмены — хоть и много, но все таки ограничение.
  • формально процедура подмены таковым методом будет происходить массово и мгновенно, но, продолжительность написания таковых формул сводит на нет это преимущество. Кроме случаев, когда они будут употребляться неоднократно.

Файл-шаблон с формулой множественной подмены

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

Файл доступен по ссылке, но можно и не закачивать его, а просто скопировать текст формулы ниже и вставить ее в всякую ячейку, не считая спектра A1:B64. Формула подменяет в ячейке C1 значения в столбце A стоящими напротив в столбце B.

функция для замены нескольких букв

Формула в файле-шаблоне для множественной подмены на примере транслитерации

А вот и она сама (тройной клик по хоть какой части текста = выделить всю формулу). Обращается к ячейке D1, делая 64 подмены по правилам, обозначенным в ячейках A1-B64. При всем этом в столбцах можно удалять значения — это не нарушит ее работу.

Поменять несколько значений на одно

При помощи функции «ПОДСТАВИТЬ«

При подмене нескольких значений на одно и то же механика работы формул на базе нескольких уровней вложенности не будет различаться от подмены нескольких на несколько. Просто 3-ий аргумент (на что поменять) на всех уровнях вложенности будет один и этот же. К слову, если бросить его пустым (кавычки без знаков меж ними), то это дозволит удалить определенные знаки. Пример — удалить числа из ячейки методом подмены на пустоту:

При помощи постоянных выражений

Принципиально: постоянные выражения не поставляются в Excel «из коробки», но формулы ниже доступны безвозмездно, если установить надстройку !SEMTools

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

1-ая подменяет на знак «#» все числа, 2-ая — все английские буковкы, а 3-я — все кириллические знаки в верхнем и нижнем регистре. 4-ая подменяет любые пробелы, в т.ч. табуляцию и переносы строк, на нижнее подчеркивание.

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

Массовая замена в !SEMTools

Надстройка для Excel !SEMTools дозволяет в пару кликов создавать подмены на всех уровнях:

  • знаков и их сочетаний
  • паттернов постоянных выражений
  • слов!
  • целых ячеек (В некой степени аналог ВПР)

Массовая замена в Excel с !SEMTools

Меню инструментов массовой подмены в !SEMTools

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

Пример: замена знаков по вхождению

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

Ниже пример с единичными знаками, но паттерны могут быть какими угодно в зависимости от вашей задачки.

Интересно почитать:  Удалить пробел в начале ячейки в excel

Пример: замена перечня слов на иной перечень слов

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

С версии !SEMTools 9.18.18 возникла функция — при подмене перечня слов не учесть пунктуацию в начальных предложениях, а регистр слов сейчас сохраняется:

Инструменты находятся в группе макросов «ИЗМЕНИТЬ» в отдельном меню и для удобства продублированы в меню «Поменять знаки«, «Поменять слова» и «Поменять ячейки«.

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

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

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

Пример 1. В итоге расчетов, сделанных в неком приложении, были получены некие значения, записанные в таблицу Excel. Некие величины высчитать не удалось, и заместо числового представления была сгенерирована ошибка “NaN”. Нужно поменять все значения “NaN” на число 0 в соответственных строчках.

Для подмены и подстановки используем рассматриваемую формулу в качестве массива. Сначала выделим спектр ячеек C2:C9, потом введем формулу через комбинацию Ctrl+Shift+Enter:

Для замены и подстановки.

Функция ЧЗНАЧ делает преобразование приобретенных текстовых строк к числовым значениям. Описание аргументов функции ПОДСТАВИТЬ:

  • B2:B9 – спектр ячеек, в которых требуется выполнить подмену части строчки;
  • “NaN” – фрагмент текста, который будет заменен;
  • 0 – фрагмент, который будет вставлен на пространство заменяемого фрагмента.

Для подстановки значений во всех ячейках нужно надавить Ctrl+Shift+Enter, чтоб функция была выполнена в массиве. Итог вычислений:

подстановка значений во всех ячейках.

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

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

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

Таблица.

Для выполнения данного условия используем формулу:

Примечание: в данном примере ПОДСТАВИТЬ также употребляется в массиве Ctrl+Shift+Enter.

В итоге получим:

В результате.

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

Пример 3. При составлении таблицы из предшествующего примера была допущена ошибка: все номера домов на улице Никольская должны быть записаны как «№№-Н», где №№ — номер дома. Как стремительно поправить ошибку?

Пример 3.

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

  1. Открыть редактор макросов (Ctrl+F11).
  2. Вставить начальный код функции (приведен ниже).
  3. Выполнить данный макрос и закрыть редактор кода.

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

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

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

  1. Функция ЕСЛИОШИБКА употребляется для возврата начальной строчки текста (B2), так как результатом выполнения функции RegExpExtract(B2;»Никольская») будет код ошибки #ЗНАЧ!, если ей не удалось отыскать хотя бы одно вхождение подстроки «Никольская» в строке B2.
  2. Если итог выполнения сопоставления значений RegExpExtract(B2;»Никольская»)=»Никольская» является ИСТИНА, будет выполнена функция ПОДСТАВИТЬ(B2;RegExpExtract(B2;»d+»);RegExpExtract(B2;»d+»)&»-Н»), где:
  • a. B2 – начальный текст, содержащий полный адресок;
  • b. RegExpExtract(B2;»d+») – формула, выделяющая номер дома из строчки с полным адресом;
  • c. RegExpExtract(B2;»d+»)&»-Н» – новейший номер, содержащий начальное значение и знаки «-Н».

Результат подстановки.

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

Excel поменять текст в ячейках

Tekstovie function part2 1 Текстовые функции в Excel. Часть №2

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

Ну вот, давайте познакомимся с 2-ой частью функций, которые работают с текстом и вашему вниманию предоставляю очередные 7 инструментов:

  1. Функция ПОДСТАВИТЬ;
  2. Функция ПРОПИСН;
  3. Функция ПСТР;
  4. Функция СЖПРОБЕЛЫ;
  5. Функция СОВПАД;
  6. Функция СТРОЧН;
  7. Функция ПРОПНАЧ.
Интересно почитать:  Excel скопировать только отфильтрованные ячейки

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

Tekstovie function part2 2 Текстовые функции в Excel. Часть №2

Функция ПОДСТАВИТЬ

Эта функция может поменять определённый текст на новейший вариант. Работа функции весьма идентична с действием функции ЗАМЕНИТЬ, но есть маленькое и принципное отличие. Рассматриваемая функция без помощи других и автоматом находит нужный текст к подмене и меняет его, а функция ЗАМЕНИТЬ, производит подмену лишь обозначенный вручную посимвольно.

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

= ПОДСТАВИТЬ(_текст_; _старый_текст_; _новый_текст_; _[номер_вхождения]_), где:

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

Пример внедрения:

Tekstovie function part2 3 Текстовые функции в Excel. Часть №2

Функция ПРОПИСН

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

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

  • текст — текст либо ссылка на текст который просит конвертировать.

Пример внедрения:

Tekstovie function part2 4 Текстовые функции в Excel. Часть №2

Функция ПСТР

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

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

= ПСТР(_текст_; _начальная_позиция_; _количество_знаков_), где:

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

Пример внедрения:

Tekstovie function part2 5 Текстовые функции в Excel. Часть №2

Функция СЖПРОБЕЛЫ

Весьма обычная и действенная функция, которая может убрать все ненадобные пробелы в тексте: двойные пробелы меж словами, пробелы в начале и конце предложения. Опосля обработки данных данной функцией останутся лишь нужные одиночные пробелы в предложении либо фразе меж словами. Отлично употреблять, когда любой символ имеет значение потому что поля ввода могут быть с лимитом, употреблять в рабочих листах, которые были импортированы из остальных наружных источников либо же просто прекрасно оформить текст в вашей таблице. Весьма часть импортированные тексты содержат различные непечатаемые знаки либо излишние пробелы, при помощи функции ПЕЧСИМВ вы сможете удалить ненадобные знаки.

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

  • текст — текст либо ссылка на текст в котором следует убрать излишние пробелы.

Пример внедрения:

Tekstovie function part2 6 Текстовые функции в Excel. Часть №2

Функция СОВПАД

Главной индивидуальностью функции СОВПАД, это сопоставление 2-ух текстовых ячеек на похожесть и в случае совпадение ворачивается значение ИСТИНА, а если совпадения нет — ЛОЖЬ. Обращаю ваше внимание что функция чувствительна к регистру, но что веселит, что ей без различия какое форматирование содержит ячейка.

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

  • текст № 1, текст № 2— тексты либо ссылка на тексты которые будут сравниваться для получения результата.

Пример внедрения:

Tekstovie function part2 7 Текстовые функции в Excel. Часть №2

Функция СТРОЧН

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

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

  • текст — текст либо ссылка на текст который просит создать строчным.

Пример внедрения:

Tekstovie function part2 8 Текстовые функции в Excel. Часть №2

Функция ПРОПНАЧ

Текстовая функция которая в любом слове первую буковку делает большей, а все другие переделывает в строчные. Совершенно безупречная функция для работников кадровых служб либо бухгалтеров по зарплате, так работа со перечнями ФИО она делает просто на 5+.

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

  • текст — текст либо ссылка на текст который просит начать с большей буковкы.

Пример внедрения:

Tekstovie function part2 9 Текстовые функции в Excel. Часть №2

И опять я не буду с вами прощаться потому что о всех текстовых функция в Excel, я еще не сказал и впереди еще одна, заключительная, часть. А если вы начали читать описание функций с данной статьи и не отыскали подходящей, поглядите часть 1 и часть 3, по данной теме.

Ссылка на основную публикацию
Adblock
detector