Excel заменить часть текста

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

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

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

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

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

Обратите внимание, что:

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

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

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

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

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

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

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

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

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

У нее простая логика:

1. заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
2. ищем позицию символа # функцией НАЙТИ (FIND)
3. вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ЗАМЕНИТЬ

Функция ЗАМЕНИТЬ замещает указанную часть знаков текстовой строки другой строкой текста.

Старый_текст — текст, в котором желательно заменить некоторые знаки.

Нач_поз — позиция знака в тексте старый_текст, начиная с которой знаки заменяются текстом нов_текст.

Число_знаков — число знаков в тексте старый_текст, которые заменяются текстом новый_текст.

Новый_текст — текст, который заменяет знаки в тексте старый_текст.

Здесь функция ЗАМЕНИТЬ:
— в строке 2 находит 6-й знак в тексте и начиная с него заменяет 3 знака на «*»;
— в строке 3 находит 3-й знак в тексте и начиная с него заменяет 2 знака на «11»;
— в строке 4 находит 1-й знак в тексте и начиная с него заменяет 3 знака на «@».

Если положение искомых знаков и количество заменяемых знаков заранее неизвестно, то функцию ЗАМЕНИТЬ() можно использовать вместе с функциями НАЙТИ() и ДЛСТР(). Допустим из списка электронных адресов нам нужно выделить левые части адресов до @ — «собаки». Т.к. длина левой части может быть разной, надо с помощью функции НАЙТИ() искать позицию @ и начиная с этой позиции все заменить на «» — пусто.

Здесь функция ЗАМЕНИТЬ:
— в строке 2 с помощью функции НАЙТИ(«@»;A2) находит позицию @ и начиная с нее, «тупо» 255 знаков (а сколько еще может быть в текстовой строке), заменяет на «» — пусто;
— в строке 3 с помощью функции НАЙТИ(«@»;A2) находит позицию @, с помощью функции ДЛСТР(A3) определяет общую длину текстовой строки, разница между этими значениями +1, дает количество знаков, которые надо заменить на «» — пусто. ДЛСТР(A3)-НАЙТИ(«@»;A3)+1.

Возможны и другие варианты комбинирования функций.

Отображение полезного совета при запуске программы Office

Некоторые сведения в этом разделе могут быть неприменимы к отдельным языкам.
1. Откройте программу Microsoft Office.
2. Убедитесь, что помощник по Office отображен.

В меню Справка выберите команду Показать помощника.
3. Щелкните изображение помощника Office правой кнопкой мыши.
4. Выберите команду Параметры.
5. На вкладке Параметры установите флажок Отображать полезный совет при запуске.

Просмотр всплывающих подсказок о конкретных командах, кнопках и диалоговых окнах

Для просмотра всплывающей подсказки о команде меню, кнопке панели инструментов или области экрана, выберите в меню Справка команду Что это такое?, а затем щелкните интересующий элемент.
Для просмотра всплывающей подсказки о параметре в диалоговом окне нажмите кнопку с вопросительным знаком
в правом верхнем углу окна, а затем щелкните интересующий параметр. (Если кнопка с вопросительным знаком не отображается, выделите интересующий параметр, а затем нажмите клавиши SHIFT+F1.)
Чтобы узнать название кнопки панели инструментов, наведите на нее указатель мыши; через некоторое время будет отображено название кнопки.

Использование справочной системы без помощника по Office

1. Для получения справочных сведений без использования помощника Office необходимо отключить помощника.

1. В выноске помощника нажмите кнопку Параметры.
Если выноска помощника по Office не отображается, щелкните изображение помощника.
2. На вкладке Параметры снимите флажок Использовать помощника.

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

Помощника можно удалить полностью. Для этого следует переустановить Microsoft Office, не устанавливая помощника по Office.
2. В меню Справка выберите команду Справка по Microsoft имя приложения.
Если вкладки Содержание, Мастер ответов и Указатель не отображаются, нажмите кнопку Показать
3. В окне справки выполните следующие действия.
Откройте вкладку Содержание, чтобы просмотреть содержание справки.
Откройте вкладку Мастер ответов, чтобы ввести вопрос о приложении Microsoft Office, в котором ведется работа. Введите слова, которые требуется найти, а затем нажмите кнопку Найти.
Откройте вкладку Указатель, чтобы выполнить поиск конкретных слов или фраз, либо выбрать ключевые слова из списка.
Список найденных разделов справки можно изменять, добавляя или удаляя ключевые слова из условий поиска. Таким образом можно сужать область поиска до тех пор, пока в списке не останутся только нужные разделы и ключевые слова.

Примечание. Для некоторых языков мастер ответов не поддерживается.

Использование средств Microsoft Office в Интернете

Выберите в меню Сервис команду Средства в Интернете.

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

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

Добрый день уважаемый читатель!

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

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

1. Функция ПОДСТАВИТЬ;
2. Функция ПРОПИСН;
3. Функция ПСТР;
4. Функция СЖПРОБЕЛЫ;
5. Функция СОВПАД;
6. Функция СТРОЧН;
7. Функция ПРОПНАЧ.

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

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

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

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

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

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

Пример применения:

Дополнительно ознакомится с примерами применения функции можно в статье «5 быстрых способов как заменить точки на запятые в Excel».

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

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

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

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

Пример применения:

Функция ПСТР

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

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

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

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

Пример применения:

Формулу можно усложнить, сделать ее более гибкой добавив интеграцию с функциями НАЙТИ и ДЛСТР. Более детально, с описаниями и сложными примерами этой функцией можно ознакомится в статье «Функция ПСТР в Excel».

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

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

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

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

Пример применения:

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

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

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

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

Пример применения:

Если же регистр текста для вас не важен, то вполне можно использовать формулу сверки ячеек: =B2=C2 для проверки единичности значений. В пункте 4 вы можете визуально наблюдать что текстовые значения одинаковы, но формула выдает значение ЛОЖЬ, это связано с тем что в ячейке содержатся невидимые пробелы, возникает ошибка в результатах, для ее избежания достаточно ввести в формулу функцию СЖПРОБЕЛЫ, и проблема будет решена.

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

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

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

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

Пример применения:

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

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

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

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

Пример применения:

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

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

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

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

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

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

Если перед использованием команды Найти Вы выделите одну ячейку, то Excel будет выполнять поиск во всем листе. А если диапазон ячеек, то только внутри этого диапазона

1. На вкладке Главная воспользуйтесь командой Найти и выделить, а затем из раскрывающегося списка выберите пункт Найти.
2. Появится диалоговое окно Найти и заменить. Введите данные, которые необходимо отыскать. В нашем примере мы введем имя сотрудника.
3. Нажмите Найти далее. Если данные присутствуют на листе, то они будут выделены.
4. Если Вы еще раз нажмете на кнопку Найти далее, то увидите следующий вариант поиска. Вы также можете выбрать Найти все, что бы увидеть все варианты, которые для Вас отыскал Excel.
5. Закончив поиск, воспользуйтесь кнопкой Закрыть, чтобы выйти из диалогового окна Найти и Заменить.

Вы можете получить доступ к команде Найти с помощью комбинации клавиш Ctrl+F на клавиатуре.

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

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

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

1. На вкладке Главная нажмите команду Найти и выделить, а затем из раскрывающегося списка выберите пункт Заменить.
2. Появится диалоговое окно Найти и заменить. Введите текст, который Вы ищете в поле Найти.
3. Введите текст, на который требуется заменить найденный, в поле Заменить на. А затем нажмите Найти далее.
4. Если значение будет найдено, то содержащая его ячейка будет выделена.
5. Посмотрите на текст и убедитесь, что Вы согласны заменить его.
6. Если согласны, тогда выберите одну из опций замены:
   • Заменить: исправляет по одному значению зараз.
   • Заменить все: исправляет все варианты искомого текста в книге. В нашем примере мы воспользуемся этой опцией для экономии времени.

Появится диалоговое окно, подтверждающее количество замен, которые будут сделаны. Нажмите ОК для продолжения.

Содержимое ячеек будет заменено.

Закончив, нажмите Закрыть, чтобы выйти из диалогового окна Найти и заменить.