Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.
Замена или удаление неразрывных пробелов
При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:
Подсчет количества слов в ячейке
Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:
Если предполагается, что в ячейке могут находиться и лишние пробелы, то формула будет чуть посложнее, но идея — та же.
Извлечение первых двух слов
Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:
У нее простая логика:
- заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
- ищем позицию символа # функцией НАЙТИ (FIND)
- вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)
Ссылки по теме
- Зачистка текста от лишних пробелов, непечатаемых символов и т.д.
- Как подсчитать количество слов в ячейке
- Преобразование чисел-как-текст в полноценные числа
ЗАМЕНИТЬ, ЗАМЕНИТЬБ (функции ЗАМЕНИТЬ, ЗАМЕНИТЬБ)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование ЗАМЕНИТЬ и ЗАМЕНИТЬБ в Microsoft Excel.
Описание
Функция ЗАМЕНИТЬ заменяет указанное число символов текстовой строки другой текстовой строкой.
Функция ЗАМЕНИТЬ заменяет часть текстовой строки, соответствующую заданному числу байтов, другой текстовой строкой.
- Эти функции могут быть доступны не на всех языках.
- Функция ЗАМЕНИТЬ предназначена для языков с однобайтовой кодировкой, а ЗАМЕНИТЬБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.
- Функция ЗАМЕНИТЬ всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.
- Функция ЗАМЕНИТЬБ считает каждый двухбайтовый символ за два, если включена поддержка ввода на языке с двухбайтовой кодировкой, а затем этот язык назначен языком по умолчанию. В противном случае функция ЗАМЕНИТЬБ считает каждый символ за один.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ описаны ниже.
- Стар_текст Обязательный. Текст, в котором требуется заменить некоторые символы.
- Начальная_позиция Обязательный. Позиция символа в старом тексте, начиная с которого требуется выполнить замену новым текстом.
- Число_знаков Обязательный. Число символов в старом тексте, которые требуется ЗАМЕНИТЬ новым текстом.
- Число_байтов Обязательный. Число байтов старого текста, который требуется ЗАМЕНИТЬБ новым текстом.
- Нов_текст Обязательный. Текст, который заменит символы в старом тексте.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Описание (результат)
Поиск и замена значений в таблице
Дополнительные сведения о планах и их возможностях см. на странице «Расценки».
Поиск и замена значений в таблице
PLANS
- Smartsheet
- Pro
- Business
- Enterprise
For more information about plan types and included capabilities, see the Smartsheet Plans page.
С помощью функции поиска и замены можно находить в таблице определённые значения и заменять их по одному или все сразу.
Чтобы воспользоваться функцией поиска и замены, откройте нужную таблицу и нажмите клавиши CTRL+F (в Windows) или COMMAND+F (на компьютере Mac), после чего откроется окно поиска и замены.
Чтобы выполнить поиск и замену лишь в определённой части таблицы, выделите требуемые столбцы, строки или ячейки, прежде чем нажимать соответствующие клавиши. Чтобы использовать эту функцию на всём листе, перед открытием окна должна была выделена только одна ячейка (или не выделено ни одной).
Чтобы найти значения, выполните указанные ниже действия.
- Введите искомое значение в поле Найти.
- Чтобы перейти к следующему или предыдущему экземпляру значения, нажмите кнопку Далее или Предыдущий.
Чтобы заменить значения, выполните указанные ниже действия.
- Введите значение, которое требуется заменить, в поле Найти.
- Введите значение, которым хотите заменить искомое, в поле Чем заменить.
- Выберите один из указанных ниже вариантов.
Чтобы заменить значения по одному, нажимайте кнопку Далее или Предыдущий, а затем — кнопку Заменить.
Количество значений, которые приложение Smartsheet заменило, отобразится под полем Найти.
Исключения в работе функции поиска и замены
Искомые значения в скрытых столбцах и строках не будут заменены. Вместо этого появится сообщение о том, что соответствующие значения есть в скрытых строках. Если вы захотите их заменить, вам потребуется показать соответствующие столбцы или снять фильтр и отобразить строки.
Дополнительные сведения об использовании фильтров см. в статье Основы работы с фильтрами.
Заменить можно значение в любой ячейке, на редактирование которой у вас есть разрешение, кроме следующих:
- ячейки с формулами;
- ячейки в столбцах логического типа с символами (флажок, звездочка или флаг);
- ячейки в столбце «Дата окончания» таблицы проекта с включёнными зависимостями;
- ячейки в столбце «Дата начала» таблицы проекта с включёнными зависимостями, если они зависят от предшественников;
- ячейки с гиперссылками и входящими ссылками.
Советы и рекомендации по поиску и замене
С помощью поиска и замены вы можете легко заменить значения в столбце Список контактов, чтобы переназначить задачу (передать её от одного лица другому). Найденные значения будут соответствовать имени или адресу электронной почты, указанному в поле Найти. Например, если вы ищете sally.smart@smartsheet.com, то найдёте все имена из столбца «Список контактов», связанные с адресом sally.smart@smartsheet.com.
С помощью поиска и замены также можно заменять большинство значений в столбцах с символами (кроме флажков, звездочек и флагов, как указывалось выше). Чтобы увидеть текстовые значения, связанные с каждым из символов, щёлкните стрелку раскрывающегося списка в любой ячейке такого столбца. Например, можно найти значение «Жёлтый» и заменить его значением «Зелёный» (с учётом регистра), чтобы все жёлтые круги состояния изменились на зелёные.
Дополнительные сведения о типах столбцов см. в статье Выбор подходящего типа столбца для данных.
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Инструмент Найти и заменить в Excel
Опубликовано 09.06.2013 Автор Ренат Лотфуллин
Excel обладает мощным инструментом поиска и замены, которая позволяет быстро находить необходимую информацию на листах или во всей рабочей книге. Ко всему прочему существует возможность поиска текста, и замены его другим.
Чтобы воспользоваться диалоговым окном Найти и заменить, выделите диапазон ячеек, в котором вы хотите произвести поиск (если будет выделена только одна ячейка, Excel будет искать во всем листе). Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Найти (или нажмите сочетание клавиш Ctrl + F).
В появившемся диалоговом окне Найти и заменить, вы можете воспользоваться вкладкой Найти, если вам просто требуется найти какую-нибудь информацию, либо вкладкой Заменить, если вы хотите заменить текст. Обратите внимание, что вы можете переключиться в расширенный режим поиска, нажав на кнопку Параметры.
Поиск информации
Введите искомую информацию в поле Найти и укажите требуемые параметры поиска:
- Выберите место поиска в выпадающем меню Искать (на листе, в книге)
- В выпадающем меню Просматривать, укажите Excel вариант просмотра по строкам или по столбцам.
- Задайте Областьпоиска в формулах, значениях или примечаниях
- С помощью опций Учитывать регистр и Ячейка целиком, вы можете указать, следует ли производить поиск с учетом регистра символов и ищется ли все содержимое ячейки или только фрагмент.
Кнопка Найти далее будет находить и выделять по одной совпавшей ячейке за одно нажатие. Если вы нажмете кнопку Найти все, Excel отобразить список с адресами всех найденных ячеек. Выделяя элементы в этом списке, будут выделяться соответствующие им ячейки на листе.
Замена информации
Чтобы заменить текст в ячейке Excel другим текстом, воспользуйтесь вкладкой Заменить в диалоговом окне Найти и заменить. В поле Найти введите текст, который необходимо заменить, а в поле Заменить на текст, на который требуется поменять. При необходимости, воспользуйтесь расширенными параметрами замены, описанными выше.
Щелкните по кнопке Найти далее, чтобы Excel выделил первую совпавшую ячейку, затем Заменить для замены. После щелчка, Excel выделит следующую совпавшую ячейку. Чтобы пропустить замещение, щелкните Найти далее. Для замены всех элементов без проверки, щелкните Заменить все. Если замещение пошло не по плану, вы можете отменить действие с помощью сочетания клавиш Ctrl + Z.
Поиск форматов
Помимо текста вы можете найти ячейки отформатированные определенным образом. Как вариант, вы также можете заменить форматирование ячейки на другое. Предположим, что необходимо определить все ячейки с жирным текстом и заменить их на жирный и курсив. Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Заменить (или нажмите сочетание клавиш Ctrl + H). В появившемся диалоговом окне перейдите во вкладку Заменить и щелкните по кнопке Параметры, чтобы отобразить расширенные опции замены. Щелкните по верхней кнопке Формат.
В появившемся диалоговом окне, перейдите во вкладку Шрифт и выберите Полужирное начертание. Щелкните ОК.
Те же шаги проделайте для замещающего формата (нижняя кнопка Формат), отличием будет только выбор начертания Полужирный курсив в диалоговом окне Найти формат.
Щелкните по кнопке Заменить все, Excel заменит текст с жирным начертанием на жирный курсив.
Хитрости использования Найти и заменить Excel
Замена переноса строки (Alt+Enter) в ячейке
Предположим, у вас имеется таблица с ячейками, данные в которых перенесены по строкам с помощью сочетания клавиш Alt+Enter и требуется заменить символ переноса строк (каретку) на пробел.
Для этого вызываем диалоговое окно Найти и заменить сочетанием клавиш Ctrl+H. В поле Найти вводим Ctrl+J (внешне поле останется пустым), в поле Заменить на вводим Пробел (или любой другой символ).
Щелкаем Заменить все и, о чудо, все наши каретки превратились в пробелы!
Вам также могут быть интересны следующие статьи
- Поиск и выделение конкретных значений
- Как отфильтровать список элементов из длинного-длинного списка
- Что если отобразить скрытые строки в Excel не работает
- Седьмой урок обучающего курса — Основы Excel — Управление несколькими рабочими листами
- Пятый урок курса по основам Excel — Печать в программе
- Шестой урок онлайн курса по основам Excel — Управление рабочим листом
- Четвертый урок курса по основам Excel — Изменение ячеек
- Третий урок курса по основам Excel — Форматирование рабочих листов
- Второй урок обучающего курса по Excel «Основы Excel»
- Запуск новой рубрики — Школа Excel
Рубрика: Ввод данных, Основы | Метки: Найти и заменить | 16 комментариев | Permalink
16 комментариев
Ренат, огромное спасибо за «Ctrl+J » — я так долго искала, как указать знак переноса в «Найти». Не поленилась написать сообщение, чтобы сказать еще раз спасибо ))