Excel как заменить несколько слов одним

Хитрости »

12 Август 2015              83643 просмотров

Массовая замена слов

Предположим, что нам необходимо заменить все «ул.» на листе на «улица «. Или русское слово «дом» на английское «house». Или еще интереснее: все английские буквы на русские. Например, английская «а» должна быть заменена на русскую «a», английская «c» на русскую «с», английская «H» на русскую «Н» и т.д. А такое тоже нередко бывает и доставляет проблемы. Ведь если в одной таблице будут русские буквы, а в другой английские — то применение большинства встроенных функций поиска(та же ВПР) просто не найдут соответствия.
Если подобную замену надо сделать для одного сочетания, то все просто: жмем Ctrl+H и указываем что заменить и на что. Но если таких замен надо сделать 20? Или 120? Это надо будет 120 раз нажать и ввести что заменять и на что. А если это надо сделать еще и не в одном документе — то…Думаю сами справитесь с умножением количества замен на количество файлов, в которых это надо сделать. И вроде бы простая операция превращается в ваш личный ад на работе.
Недавно на форуме участнику потребовалось автоматизировать именно такую штуку. Т.к. код несложный — решил написать и чуть дополнив выложить для всех кому код может потребоваться:

Option Explicit
Sub Replace_Mass()
    Dim s As String
    Dim lCol As Long
    Dim avArr, lr As Long
    Dim lLastR As Long
    Dim lToFindCol As Long, lToReplaceCol As Long, lLookAt As Long
 
    'запрашиваем направление перевода - с русского на англ. или наоборот
    lCol = Val(InputBox("Укажите направление перевода:" & vbNewLine & _
                    "   1 - ru-en" & vbNewLine & _
                    "   2 - en-ru", "Запрос", 1))
    If lCol = 0 Then Exit Sub
    'запрашиваем по части ячейки искать или по всему тексту
    'по умолчанию - по части
    lLookAt = Val(InputBox("Искать соответствие по части ячейки или по всему тексту:" & vbNewLine & _
                    "   1 - по всему тексту" & vbNewLine & _
                    "   2 - по части ячейки", "Запрос", 2))
    If lLookAt = 0 Then Exit Sub
 
    Select Case lCol
    Case 1
        lToFindCol = 1
        lToReplaceCol = 2
    Case 2
        lToFindCol = 2
        lToReplaceCol = 1
    End Select
 
    Application.ScreenUpdating = 0
    'Получаем с листа Соответствия значения, которые надо заменить в выделенном диапазоне
    With ThisWorkbook.Sheets("Соответствия")
        lLastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        avArr = .Cells(1, 1).Resize(lLastR, 2)
    End With
    'заменяем
    For lr = 1 To UBound(avArr, 1)
        s = avArr(lr, lToFindCol)
        If Len(s) Then 'если значение для замены не пустое
            Selection.Replace s, avArr(lr, lToReplaceCol), lLookAt
        End If
    Next lr
    Application.ScreenUpdating = 1
End Sub

Как это работает. В книге есть специальный лист с именем «Соответствия». На нем в столбце А записаны слова, которые необходимо заменить, а в столбце В — на что эти слова заменить. Если в столбце А пусто — то замена не будет произведена. Если в столбце В пусто — то значение из столбца А будет просто удалено.
Замены производятся исключительно в выделенных на листе ячейках. Ячейки могут быть несмежными.

Итак, необходимо сделать много замен. Скачиваете файл:

  Массовая замена слов (54,5 KiB, 9 782 скачиваний)

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

На лист «Соответствия» записываете в столбец А — что заменять, в столбец В — на что заменять. Переходите на лист книги, в котором необходимо произвести замену. Выделяете ячейки, значения в которых надо найти и заменить. После чего жмете Alt+F8 и выбираете макрос «Tips_Macro_ReplaceMASS.xls!Replace_Mass»(или просто «Replace_Mass», если код в той же книге, что и ячейки для поиска и замены).

Первым появится

окно с запросом направления перевода. По умолчанию 1(ru-en). Т.е. будет браться слово из столбца А и заменяться словом из столбца В. Но если указать 2 — то будет браться слово из столбца В и заменяться словом из столбца А. Т.е. аналог переводчика — с рус. на англ. и наоборот. Либо из А в В, либо из В в А.

Вторым появится

запрос на метод просмотра данных:

• если указать «1 — по всему тексту» — данные из столбца А будут заменять только в том случае, если ячейка в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа «Соответствия». Например, в любой из выделенных ячеек записано «На столе книга», а на листе «Соответствия» в столбце А есть только слово «книга». Замена не будет произведена, т.к. необходимо, чтобы в столбце А было так же «На столе книга».
• если указать «2 — по части ячейки» — данные из столбца А будут заменять в случае, если ячейка в выделенном для замены диапазоне содержит любое слово из столбца А листа «Соответствия». На том же примере — «На столе книга». Если выбрать 2, то в тексте «На столе книга» слово книга будет заменено на слово из столбца В — «book».

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

Как видно, здесь присутствую только номера статей, но нет их расшифровки. Зато расшифровка есть в отдельном листе «Справочник»:

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

Скачать файл с примером и кодом:

  Массовая замена слов — статьи.xls (91,5 KiB, 2 647 скачиваний)

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

Достаточно выделить столбец со статьями на листе с реализацией и запустить код(либо нажатием кнопки заменить значения, либо нажав Alt+F8 и выбрав из списка макросов макрос

Replace_Mass

).
После нажатия на кнопку будут запрошены следующие параметры:

1. указать номер столбца значений в листе «Справочник», в котором искать соответствия номерам статей(в нашем случае это столбец 1(А))
2. указать номер столбца, значениями которого заменять найденные в таблице реализации значения(это может быть один из трех столбцов справочника: Группа затрат, Статья затрат, Подстатьи затрат). Логичнее всего указать столбец 4, т.к. он наиболее детализирован и конкретнее указывает расшифровку статьи
3. далее будет предложено указать точность поиска:
   • если указать «1 — по всему тексту» — данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа «Справочник». Т.е. если бы у нас в таблице реализации был бы записан только номер статьи(1.01), тогда можно было бы указать именно 1
   • если указать «2 — по части ячейки» — данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне содержит любое значение из столбца А листа «Справочник». Это больше подходит к описанному случаю, т.к. нам необходимо заменить исключительно номер статьей на их расшифровку, оставив при этом текст «Статья затрат: «

Если все указано корректно, то на листе будут произведены все необходимые замены.
Возможные ошибки, которые предусмотрены кодом и о которых будет сообщено соответствующим сообщением(код прервется, замены не будут произведены):

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

Особое внимание хочу уделить случаю, когда выбирается замена по части ячейки. В этом случае лучше список на листе Справочник отсортировать по длине текста по тому столбцу, в котором будут значения для поиска. Зачем это надо: т.к. значение по части ячейки будет заменять не полное соответствие, то есть вероятность неверных замен. Например, есть текст «Статья затрат: 1.011». В то же время на листе Справочник есть статьи «1.01» и «1.011». Т.к. «1.01» идет раньше в большинстве случаев, то текст будет заменен некорректно: «Статья затрат: ТВ1«.
Чтобы получить длину строки текста можно использовать функцию ДЛСТР(LEN):
=ДЛСТР(A2)
=LEN(A2)

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

Так же см.:
Замена значений по списку в PowerQuery
Найти в ячейке любое слово из списка
Замена ссылок в формулах на их значения
Как удалить строки по условию?

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Массовая замена текста формулами

Предположим, что у вас имеется список, в котором с разной степенью «пряморукости» записаны исходные данные — например, адреса или названия компаний:

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

А теперь представьте, что к вам такие кривые данные попадают регулярно, т.е. это не единичная история из серии «исправил вручную, забыл», а проблема на регулярной основе и в большом количестве ячеек.

Что же делать? Не заменять же вручную 100500 раз кривой текст на правильный через окошко «Найти и заменить» или нажимая Ctrl+H?

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

К сожалению, при очевидной распространенности подобной задачи, в Microsoft Excel не существует простых встроенных способов для её решения. Для начала, давайте разберёмся, как это делать формулами, без привлечения «тяжелой артиллерии» в виде макросов на VBA или Power Query.

Случай 1. Массовая полная замена

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

Предположим, что у нас есть две таблицы:

В первой — исходные разномастные названия компаний. Во второй — справочник соответствия. Если находим в названии компании в первой таблице любое слово из столбца Найти, то нужно полностью заменить это кривое название на правильное — из столбца Заменить второй таблицы-справочника.

Для удобства:

• Обе таблицы преобразованы в динамические («умные») с помощью сочетания клавиш Ctrl+T или командой Вставка — Таблица (Insert — Table).
• На появившейся вкладке Конструктор (Design) первой таблице присвоено имя Данные, а второй таблице-справочнику — Замены.

Чтобы объяснить логику формулы зайдём чуть издалека.

Взяв в качестве примера первую компанию из ячейки A2 и забыв временно про остальные компании, попробуем определить какой именно вариант из столбца Найти там встречается. Для этого выделим любую пустую ячейку в свободной части листа и введём туда функцию НАЙТИ (FIND):

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

Хитрость тут в том, что поскольку первым аргументом мы указали не одно, а несколько значений — эта функция будет возвращать в качестве результата тоже не одно значение, а массив из 3 элементов. Если у вас не последняя версия Office 365 с поддержкой динамических массивов, то после ввода этой формулы и нажатия на Enter вы этот массив увидите прямо на листе:

Если же у вас предыдущие версии Excel, то после нажатия на Enter мы увидим только первое значение из массива результатов, т.е. ошибку #ЗНАЧ! (#VALUE!).

Пугаться не стоит На самом деле наша формула работает и увидеть весь массив результатов всё равно можно, если выделить введённую функцию в строке формул и нажать клавишу F9(только не забудьте потом нажать Esc, чтобы вернуться обратно к формуле):

Полученный массив результатов означает, что в исходном кривом названии компании (ГК Морозко ОАО) из всех значений в столбце Найти нашлось только второе (Морозко), причём начиная с 4-го по счёту символа.

Теперь добавим к нашей формуле функцию ПРОСМОТР (LOOKUP):

У этой функции три аргумента:

1. Искомое значение — можно использовать любое достаточно большое число (главное, чтобы оно превышало длину любого текста в исходных данных)
2. Просматриваемый_вектор — тот диапазон или массив, где мы ищем искомое значение. Здесь это введённая ранее функция НАЙТИ, возвращающая массив {#ЗНАЧ!:4:#ЗНАЧ!}
3. Вектор_результатов — диапазон, откуда мы хотим вернуть значение, если искомое значение найдено в соответствующей ячейке. Здесь это правильные названия из столбца Заменить нашей таблицы-справочника.

Главная и неочевидная фишка тут в том, что функция ПРОСМОТР при отсутствии точного совпадения всегда ищет ближайшее наименьшее (предыдущее) значение. Поэтому, указав в качестве искомого значения любое здоровенное число (например 9999), мы заставим ПРОСМОТР находить ячейку с ближайшим наименьшим числом (4) в массиве {#ЗНАЧ!:4:#ЗНАЧ!} и выдавать соответствующее ей значение из вектора результатов, т.е. правильное название компании из столбца Заменить.

Второй нюанс заключается в том, что, технически, наша формула является формулой массива, т.к. функция НАЙТИ возвращает в качестве результатов не одно, а массив из трёх значений. Но поскольку функция ПРОСМОТР поддерживает массивы «из коробки», то нам не придётся вводить эту формулу как классическую формулу массива — с помощью сочетания клавиш Ctrl+Shift+Enter. Достаточно будет простого Enter.

Вот и всё. Надеюсь вы ухватили логику.

Осталось перенести готовую формулу первую ячейку B2 столбца Исправлено — и наша задача решена!

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

Случай 2. Массовая частичная замена

Этот случай чуть похитрее. Снова имеем две «умных» таблицы:

Первая таблица с криво записанными адресами, которые нужно исправить (я назвал её Данные2). Вторая таблица — справочник, по которому нужно произвести частичную замену подстроки внутри адреса (я назвал эту таблицу Замены2).

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

Готовая формула будет выглядеть так (для удобства восприятия я разделил её на насколько строк с помощью Alt+Enter):

Основную работу здесь выполняет стандартная Excel’евская текстовая функция ПОДСТАВИТЬ (SUBSTITUTE), у которой 3 аргумента:

1. Исходный текст — первый кривой адрес из столбца Адрес
2. Что ищем — тут мы используем трюк с функцией ПРОСМОТР (LOOKUP) из предыдущего способа, чтобы вытащить значение из столбца Найти, которое входит как фрагмент в кривой адрес.
3. На что заменить — аналогичным образом находим соответствующее ему правильное значение из столбца Заменить.

Вводить эту формулу с Ctrl+Shift+Enter здесь тоже не нужно, хотя она и является, по-сути, формулой массива.

И хорошо видно (см. ошибки #Н/Д на предыдущей картинке), что такая формула, при всей её элегантности, обладает и парой недостатков:

• Функция ПОДСТАВИТЬ является регистрочувствительной, поэтому «Спб» в предпоследней строке так и не нашлось в таблице замен. Для решения этой проблемы можно либо использовать функцию ЗАМЕНИТЬ (REPLACE), либо предварительно привести обе таблицы к одному регистру.
• Если текст изначально правильный или в нём нет ни одного фрагмента на замену (последняя строка), то наша формула выдает ошибку. Этот момент можно нейтрализовать перехватом и заменой ошибок с помощью функции ЕСЛИОШИБКА (IFERROR):

  

• Если в исходном тексте встречается сразу несколько фрагментов из справочника, то наша формула заменяет только последний (в 8-й строке Лиговский «проспект« заменился на «пр-т», а вот «С-Пб» на «Санкт-Петербург» уже нет, т.к. «С-Пб» стоит выше в справочнике). Эту проблему можно решить повторным прогоном нашей же формулой, но уже по столбцу Исправлено:

  

Не идеально и, местами, громоздко, но гораздо лучше, чем однообразная замена вручную, правда?

P.S.

В следующей статье разберёмся, как реализовать подобную массовую подстановку с помощью макросов и Power Query.

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

• Как работает функция ПОДСТАВИТЬ (SUBSTITUTE) для замены текста
• Поиск точного совпадения текста с помощью функции СОВПАД (EXACT)
• Поиск и подстановка с учётом регистра (регистрочувствительный ВПР)

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

​Смотрите также​ вот пример. Заменить​ его словом из​ заменить в ячейке​ параметры автозамены, как​ работы с буфером​ «кр», или «к1″​Если нужно вытащить из​ или функцией удаления​ только вторая «Маша»​Старый_текст​Замена одного текста на​Найти далее​ «ID-567(ASD)», необходимо перед​

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

​ Запишем формулы:​​введена строка Продажи​

​Число_знаков​​Функция ЗАМЕНИТЬ(), английский вариант​ нужно AGAWA​ другой ячейки. Ячейки​
​ первое слово (с​​ советует Андрей Белоконь.​​ обмена. помнит несколько​​ «к2» «к3», а​ ячейки только первые​​ лишних пробелов​​ заменена на «Олю»).​
​- текст, который​​ другой внутри заданной​​, пока не перейдете​​ текстом ASD вставить​​=ЗАМЕНИТЬ(A2;10;6;»февраль»)​​ (январь). Чтобы заменить​
​  — число знаков в​​ REPLACE(), замещает указанную​Юрий М​​ находятся в одной​​ маленькой буквы и​

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

​Либо третий вариант,​ копирований. потом просто​​ выводит соответственно всё​​ два слова (например​СЖПРОБЕЛЫ (TRIM)​Эта функция различает строчные​ надо найти и​​ текстовой строки -​​ к вхождению, которое​ новый текст Micro,​

​=ПОДСТАВИТЬ(A2; «январь»;»февраль»)​ слово январь, на​Исходном_тексте​ часть знаков текстовой​

​: Формулы/макросы — чем​​ строке. Можно как​​ последняя буква в​если фраза идёт​ вставляешь​ название.​

​ ФИ из ФИО),​
​. Поможет наша функция​

​ и прописные буквы​ заменить​ весьма частая ситуация​ вы хотите изменить.​ чтобы получилось «ID-567(MicroASD)».​получим в первом​ февраль, запишем формулы:​, которые заменяются текстом​

​ строки другой строкой​ нужно?​ то, это автоматизировать?​ слове другая)… пример​ сначала только всё​Евгений евгеньевич​Если можно то​ то можно применить​
​ПОДСТАВИТЬ​ (в ячейке С3​​Новый_текст​​ при работе с​Нажмите кнопку​ Для этого напишем​
​ случае строку Продажи​
​=ЗАМЕНИТЬ(A2;10;6;»февраль»)​
​новый_текст​ текста. «Указанную часть​JayBhagavan​Юрий М​ прилагается… как это​ время про козу,​
​: можно просто так​ как и/или что​ формулу:​, которой можно заменить​

​ замена не сработала,​- текст, на​ данными в Excel.​Заменить​ простую формулу:​ (февраль), прибыль (январь),​=ПОДСТАВИТЬ(A2; «январь»;»февраль»)​.​ знаков» означает, что​

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

​: =ПОДСТАВИТЬ($B1;$A1;»AGAWA»)​: Можно.​ можно сделать? спасибо​ а потом всё​ и писать, а​ ещё есть для​У нее простая логика:​ неразрывный пробел на​ т.к. «маша» написана​ который заменяем​ Реализовать подобное можно​
​. Чтобы обновить все​

excel2.ru

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

​=ЗАМЕНИТЬ(A2;8;0;»Micro»).​ во втором -​т.е. для функции ЗАМЕНИТЬ()​Новый_текст​ нужно указать начальную​

1. ​Skweb​​Skweb​​ огромное заранее!!))))​​ время про корову​​ потом — Заменить:​
   ​ облегчения работы?​заменяем второй пробел на​

   

2. ​ обычный или на​​ с маленькой буквы)​​Номер_вхождения​ двумя функциями:​

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

4. ​ потребовалось вычислить начальную​​  — текст, который заменяет​​ позицию и длину​: Что нибудь что​: Это, я глупый​

5. ​Serge_007​​ и т. д.​​ к1 на Корова​Андрей белоконь​ какой-нибудь необычный символ​ пустую текстовую строку,​​Давайте разберем пару примеров​​- необязательный аргумент,​

​ПОДСТАВИТЬ (SUBSTITUTE)​​ на каждом из​ и замена» можно​ (февраль).​ позицию слова январь​​ знаки в​​ заменяемой части строки.​​ позволяет это сделать​​ вопрос задал? или​

​: Например так:​​ , то можно​ серая, в пункте​: В 2003 Екселе​​ (например #) функцией​ ​ т.е. удалить:​

support.office.com

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

​ использования функции​ задающий номер вхождения​и​ них, нажмите кнопку​ найти слово или​Записав формулу =ПОДСТАВИТЬ(A2;​ (10) и его​Исходном_тексте​​ Функция используется редко,​​ быстрее чем Ctrl​​ мне надо что​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СТРОЧН(ЗАМЕНИТЬ(B5;ПОИСК(» «;B5)-1;1;»е»))​ набрать её один​ Правка или справа​ делается так​ПОДСТАВИТЬ (SUBSTITUTE)​Если нужно подсчитать количество​ПОДСТАВИТЬ​ старого текста на​ЗАМЕНИТЬ (REPLACE)​

​Заменить все​

​ фразу и заменить​

​ «январь»;»февраль»;2) получим строку​

• ​ длину (6). Это​​.​ но имеет плюс:​ C + Ctrl​
• ​ то уточнить? Или​​mefisto​ раз, СКОПИРОВАТЬ (она​ в главной вкладке​
• ​Сервис > Параметры​​ищем позицию символа #​ слов в ячейке,​
• ​для наглядности.​​ замену​. Эти функции во​.​ их.​

​ Продажи (январь), прибыль​

• ​ не удобно, функция​Функция ПОДСТАВИТЬ() используется, когда​​ позволяет легко вставить​​ V​ формулы подсказывают только​: БРАВО!!!! блин вроде​ будет в буфере,​ есть такой пункт​ авто замены​
• ​ функцией​ то можно применить​При выгрузке данных из​Обратите внимание, что:​ многом похожи, но​Совет:​На вкладке​
• ​ (февраль).​ ПОДСТАВИТЬ() справляется с​ нужно заменить​ в указанную позицию​JayBhagavan​ тем кто давно​

​ для Вас это​ в «уме» у​​Zaretskii kostya​​И вводишь слева​

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

​НАЙТИ (FIND)​ простую идею: слов​ 1С, копировании информации​Если не указывать последний​ имеют и несколько​ Чтобы найти только вхождения​Главная​Кроме того, функция ПОДСТАВИТЬ()​ задачей гораздо проще.​определенный​ строки новый текст.​: Skweb, . Прежде​ тут на форуме?​ так просто… раз​ компьютера) и потом​: В Экселе, как​КБ заменить на​​вырезаем все символы от​​ на единицу больше,​​ с вебстраниц или​​ аргумент​ принципиальных отличий и​ в верхнем или​нажмите кнопку​ чувствительна к РЕгиСТру.​

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

​Кроме того, функция ЗАМЕНИТЬ()​текст в текстовой​ЗАМЕНИТЬисходный_текстнач_позчисло_знаковновый_текст​ чем задавать вопрос,​JayBhagavan​ и все!!! а​ по клавишам Shift+Ins​ и в Ворде,​ Коза белая​ начала строки до​

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

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

​ заменяет по понятным​ строке; функция ЗАМЕНИТЬ()​)​ следует задействовать поиск​: Skweb, прочитать правила​ для меня как​

​ просто вставлять её​

1. ​ есть поиск с​Только короткое название​ позиции # функцией​​ условии, что нет​
2. ​ часто приходится иметь​, то будут заменены​​ ситуациях. Давайте подробно​
3. ​ кнопку​.​ получим строку без​​ причинам только одно​

planetaexcel.ru

Можно ли в Excel заменять слова для ускорения работы?

​ используется, когда нужно​Исходный_текст​ по форуму, что​ и приложить файл-пример.​ китайская грамота)) СПАСИБО!!!​ в нужном месте.​ заменой. Если Вы​ Вводи Заглавными буквами,​ЛЕВСИМВ (LEFT)​ лишних пробелов). Соответственно,​ дело с неразрывным​ все вхождения старого​ и на примерах​
​Больше​Кроме того, можно​ изменений Продажи (январь),​ вхождение строки, функция​

​ заменить​​  — текст, в котором​ сделали за Вас.​
​Юрий М​jakim​
​mefisto​
​ в тексте оставите​ иначе будет производится​
​Например мне нужно​ формула для расчета​ пробелом — спецсимволом,​ текста (в ячейке​ разберем сначала первую​

​и установите флажок​​ нажать клавиши CTRL+H.​ прибыль (январь), т.к.​ ПОДСТАВИТЬ() может заменить​
​любой​ требуется заменить некоторые​Skweb​: Каков вопрос -​: Можно и так.​: Здравствуйте… на сколько​

​ короткий код, то​​ автозамена в средине​ написать «Корова серая»,​ будет простой:​ неотличимым от обычного​ С1 — обе​ из них.​Учитывать регистр​В поле​

​ для функции ПОДСТАВИТЬ()​​ все вхождения или​текст начиная с​ знаки.​:​ таков ответ. Показали​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=LOWER(LEFT(B5))&MID(B5;2;222)​ я понял здесь​ потом запустив «Найти​ слова​ «Корова бурая», «Коза​Если предполагается, что в​ пробела, но с​
​ «Маши» заменены на​Её синтаксис таков:​.​
​Найти​
​ «ЯНВАРЬ» не тоже​ только первое, только​ определенной позиции.​Нач_поз​Ребят, ну вы​ бы свой файл-пример,​mefisto​ есть можно сказать​ и заменить» (Ctrl+H​Yustas​ рогатая»… и так​ ячейке могут находиться​ другим внутренним кодом​ «Олю»).​=ПОДСТАВИТЬ(Ячейка; Старый_текст; Новый_текст; Номер_вхождения)​

Как заменить первое слово в ячейке? (Как заменить первое слово в ячейке?)

​Совет.​​введите искомые слово​ самое, что «январь».​ второе и т.д.​При замене определенного текста​  — позиция знака в​ и зануды. Спасибо​ согласно п. 2.3​: СПАСИБО!!!​ гуру этой программы!!​ английское «эйч» или​: Делается макрос. На​ раз по 30​ и лишние пробелы,​ (160 вместо 32).​Если нужно заменить только​где​

​Видео не на​​ или фразу.​
​Функцию ЗАМЕНИТЬ() удобно использовать​

​Поясним на примере.​​ функцию ЗАМЕНИТЬ() использовать​Исходном_тексте​ что помогли.​ Правил — ответ​Skweb​ браво!! сколько ответов​

​ латинское «аш») замените​​ комбинацию клавиш вешается​
​ каждую, могу ли​

​ то формула будет​​ Его не получается​

excelworld.ru

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

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

​ был бы другой.​​: Здравствуйте! Нужно найти​

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

​ выбрать​​Заменить на​ строку нового текста.​

​А2​​ воспользоваться функцией ПОДСТАВИТЬ().​ знаки заменяются текстом​: Skweb, ну, Вы​Skweb​ слово в одной​ вопросы!! еще один​

​ на соответствующую фразу.​​Либо в офисе​ запрограммированный код например​ идея — та​

​ — заменой через​​ в последнем аргументе​ текстом, где производится​

​Скрытые субтитры​​введите новый текст.​

​ Например, имеется перечень​​введена строка Продажи​Пусть в ячейке​новый_текст​ и лентяй. Спасибо​: Спасибо что научили,​

​ ячейке и заменить​​ есть)).. мне необходимо​Либо сразу настраивать​ есть средство для​ «кс» «кб» и​ же.​

​ диалоговое окно Ctrl+H​​ (в ячейке С2​​ замена​.​Нажимайте кнопку​

​ артикулов товаров вида​​ (январь), прибыль (январь).​А2​.​

planetaexcel.ru

​ за спасибо.​

Как в excel заменить одно слово на другое во всем тексте

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

​Смотрите также​ вот пример. Заменить​ его словом из​ заменить в ячейке​ параметры автозамены, как​ работы с буфером​ «кр», или «к1″​Если нужно вытащить из​ или функцией удаления​ только вторая «Маша»​Старый_текст​Замена одного текста на​Найти далее​ «ID-567(ASD)», необходимо перед​

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

​ Запишем формулы:​​введена строка Продажи​

​Число_знаков​​Функция ЗАМЕНИТЬ(), английский вариант​ нужно AGAWA​ другой ячейки. Ячейки​
​ первое слово (с​​ советует Андрей Белоконь.​​ обмена. помнит несколько​​ «к2» «к3», а​ ячейки только первые​​ лишних пробелов​​ заменена на «Олю»).​
​- текст, который​​ другой внутри заданной​​, пока не перейдете​​ текстом ASD вставить​​=ЗАМЕНИТЬ(A2;10;6;»февраль»)​​ (январь). Чтобы заменить​
​ — число знаков в​​ REPLACE(), замещает указанную​Юрий М​​ находятся в одной​​ маленькой буквы и​

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

​Либо третий вариант,​ копирований. потом просто​​ выводит соответственно всё​​ два слова (например​СЖПРОБЕЛЫ (TRIM)​Эта функция различает строчные​ надо найти и​​ текстовой строки -​​ к вхождению, которое​ новый текст Micro,​

​=ПОДСТАВИТЬ(A2; «январь»;»февраль»)​ слово январь, на​Исходном_тексте​ часть знаков текстовой​

​: Формулы/макросы — чем​​ строке. Можно как​​ последняя буква в​если фраза идёт​ вставляешь​ название.​

​ ФИ из ФИО),​
​. Поможет наша функция​

​ и прописные буквы​ заменить​ весьма частая ситуация​ вы хотите изменить.​ чтобы получилось «ID-567(MicroASD)».​получим в первом​ февраль, запишем формулы:​, которые заменяются текстом​

​ строки другой строкой​ нужно?​ то, это автоматизировать?​ слове другая). пример​ сначала только всё​Евгений евгеньевич​Если можно то​ то можно применить​
​ПОДСТАВИТЬ​ (в ячейке С3​​Новый_текст​​ при работе с​Нажмите кнопку​ Для этого напишем​
​ случае строку Продажи​
​=ЗАМЕНИТЬ(A2;10;6;»февраль»)​
​новый_текст​ текста. «Указанную часть​JayBhagavan​Юрий М​ прилагается. как это​ время про козу,​
​: можно просто так​ как и/или что​ формулу:​, которой можно заменить​

​ замена не сработала,​- текст, на​ данными в Excel.​Заменить​ простую формулу:​ (февраль), прибыль (январь),​=ПОДСТАВИТЬ(A2; «январь»;»февраль»)​.​ знаков» означает, что​

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

​: =ПОДСТАВИТЬ($B1;$A1;»AGAWA»)​: Можно.​ можно сделать? спасибо​ а потом всё​ и писать, а​ ещё есть для​У нее простая логика:​ неразрывный пробел на​ т.к. «маша» написана​ который заменяем​ Реализовать подобное можно​
​. Чтобы обновить все​

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

​=ЗАМЕНИТЬ(A2;8;0;»Micro»).​ во втором -​т.е. для функции ЗАМЕНИТЬ()​Новый_текст​ нужно указать начальную​

​Skweb​​Skweb​​ огромное заранее!!))))​​ время про корову​​ потом — Заменить:​
​ облегчения работы?​заменяем второй пробел на​

​ обычный или на​​ с маленькой буквы)​​Номер_вхождения​ двумя функциями:​

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

​ потребовалось вычислить начальную​​ — текст, который заменяет​​ позицию и длину​: Что нибудь что​: Это, я глупый​

​Serge_007​​ и т. д.​​ к1 на Корова​Андрей белоконь​ какой-нибудь необычный символ​ пустую текстовую строку,​​Давайте разберем пару примеров​​- необязательный аргумент,​

​ПОДСТАВИТЬ (SUBSTITUTE)​​ на каждом из​ и замена» можно​ (февраль).​ позицию слова январь​​ знаки в​​ заменяемой части строки.​​ позволяет это сделать​​ вопрос задал? или​

​: Например так:​​ , то можно​ серая, в пункте​: В 2003 Екселе​​ (например #) функцией​ ​ т.е. удалить:​

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

​ использования функции​ задающий номер вхождения​и​ них, нажмите кнопку​ найти слово или​Записав формулу =ПОДСТАВИТЬ(A2;​ (10) и его​Исходном_тексте​​ Функция используется редко,​​ быстрее чем Ctrl​​ мне надо что​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СТРОЧН(ЗАМЕНИТЬ(B5;ПОИСК(» «;B5)-1;1;»е»))​ набрать её один​ Правка или справа​ делается так​ПОДСТАВИТЬ (SUBSTITUTE)​Если нужно подсчитать количество​ПОДСТАВИТЬ​ старого текста на​ЗАМЕНИТЬ (REPLACE)​

​ фразу и заменить​

​ «январь»;»февраль»;2) получим строку​

• ​ длину (6). Это​​.​ но имеет плюс:​ C + Ctrl​
• ​ то уточнить? Или​​mefisto​ раз, СКОПИРОВАТЬ (она​ в главной вкладке​
• ​Сервис > Параметры​​ищем позицию символа #​ слов в ячейке,​
• ​для наглядности.​​ замену​. Эти функции во​.​ их.​

​ Продажи (январь), прибыль​

• ​ не удобно, функция​Функция ПОДСТАВИТЬ() используется, когда​​ позволяет легко вставить​​ V​ формулы подсказывают только​: БРАВО. блин вроде​ будет в буфере,​ есть такой пункт​ авто замены​
• ​ функцией​ то можно применить​При выгрузке данных из​Обратите внимание, что:​ многом похожи, но​Совет:​На вкладке​
• ​ (февраль).​ ПОДСТАВИТЬ() справляется с​ нужно заменить​ в указанную позицию​JayBhagavan​ тем кто давно​

​ для Вас это​ в «уме» у​​Zaretskii kostya​​И вводишь слева​

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

​НАЙТИ (FIND)​ простую идею: слов​ 1С, копировании информации​Если не указывать последний​ имеют и несколько​ Чтобы найти только вхождения​Главная​Кроме того, функция ПОДСТАВИТЬ()​ задачей гораздо проще.​определенный​ строки новый текст.​: Skweb, . Прежде​ тут на форуме?​ так просто. раз​ компьютера) и потом​: В Экселе, как​КБ заменить на​​вырезаем все символы от​​ на единицу больше,​​ с вебстраниц или​​ аргумент​ принципиальных отличий и​ в верхнем или​нажмите кнопку​ чувствительна к РЕгиСТру.​

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

​Кроме того, функция ЗАМЕНИТЬ()​текст в текстовой​ЗАМЕНИТЬисходный_текстнач_позчисло_знаковновый_текст​ чем задавать вопрос,​JayBhagavan​ и все. а​ по клавишам Shift+Ins​ и в Ворде,​ Коза белая​ начала строки до​

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

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

​ заменяет по понятным​ строке; функция ЗАМЕНИТЬ()​)​ следует задействовать поиск​: Skweb, прочитать правила​ для меня как​

​ просто вставлять её​

1. ​ есть поиск с​Только короткое название​ позиции # функцией​​ условии, что нет​
2. ​ часто приходится иметь​, то будут заменены​​ ситуациях. Давайте подробно​
3. ​ кнопку​.​ получим строку без​​ причинам только одно​

Можно ли в Excel заменять слова для ускорения работы?

​ используется, когда нужно​Исходный_текст​ по форуму, что​ и приложить файл-пример.​ китайская грамота)) СПАСИБО. ​ в нужном месте.​ заменой. Если Вы​ Вводи Заглавными буквами,​ЛЕВСИМВ (LEFT)​ лишних пробелов). Соответственно,​ дело с неразрывным​ все вхождения старого​ и на примерах​
​Больше​Кроме того, можно​ изменений Продажи (январь),​ вхождение строки, функция​

​ заменить​​ — текст, в котором​ сделали за Вас.​
​Юрий М​jakim​
​mefisto​
​ в тексте оставите​ иначе будет производится​
​Например мне нужно​ формула для расчета​ пробелом — спецсимволом,​ текста (в ячейке​ разберем сначала первую​

​и установите флажок​​ нажать клавиши CTRL+H.​ прибыль (январь), т.к.​ ПОДСТАВИТЬ() может заменить​
​любой​ требуется заменить некоторые​Skweb​: Каков вопрос -​: Можно и так.​: Здравствуйте. на сколько​

​ короткий код, то​​ автозамена в средине​ написать «Корова серая»,​ будет простой:​ неотличимым от обычного​ С1 — обе​ из них.​Учитывать регистр​В поле​

​ для функции ПОДСТАВИТЬ()​​ все вхождения или​текст начиная с​ знаки.​:​ таков ответ. Показали​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=LOWER(LEFT(B5))&MID(B5;2;222)​ я понял здесь​ потом запустив «Найти​ слова​ «Корова бурая», «Коза​Если предполагается, что в​ пробела, но с​
​ «Маши» заменены на​Её синтаксис таков:​.​
​Найти​
​ «ЯНВАРЬ» не тоже​ только первое, только​ определенной позиции.​Нач_поз​Ребят, ну вы​ бы свой файл-пример,​mefisto​ есть можно сказать​ и заменить» (Ctrl+H​Yustas​ рогатая». и так​ ячейке могут находиться​ другим внутренним кодом​ «Олю»).​=ПОДСТАВИТЬ(Ячейка; Старый_текст; Новый_текст; Номер_вхождения)​

Как заменить первое слово в ячейке? (Как заменить первое слово в ячейке?)

​Совет.​​введите искомые слово​ самое, что «январь».​ второе и т.д.​При замене определенного текста​ — позиция знака в​ и зануды. Спасибо​ согласно п. 2.3​: СПАСИБО. ​ гуру этой программы!!​ английское «эйч» или​: Делается макрос. На​ раз по 30​ и лишние пробелы,​ (160 вместо 32).​Если нужно заменить только​где​

​Видео не на​​ или фразу.​
​Функцию ЗАМЕНИТЬ() удобно использовать​

​Поясним на примере.​​ функцию ЗАМЕНИТЬ() использовать​Исходном_тексте​ что помогли.​ Правил — ответ​Skweb​ браво!! сколько ответов​

​ латинское «аш») замените​​ комбинацию клавиш вешается​
​ каждую, могу ли​

​ то формула будет​​ Его не получается​

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

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

​ был бы другой.​​: Здравствуйте! Нужно найти​

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

​ выбрать​​Заменить на​ строку нового текста.​

​А2​​ воспользоваться функцией ПОДСТАВИТЬ().​ знаки заменяются текстом​: Skweb, ну, Вы​Skweb​ слово в одной​ вопросы!! еще один​

​ на соответствующую фразу.​​Либо в офисе​ запрограммированный код например​ идея — та​

​ — заменой через​​ в последнем аргументе​ текстом, где производится​

​Скрытые субтитры​​введите новый текст.​

​ Например, имеется перечень​​введена строка Продажи​Пусть в ячейке​новый_текст​ и лентяй. Спасибо​: Спасибо что научили,​

​ ячейке и заменить​​ есть)).. мне необходимо​Либо сразу настраивать​ есть средство для​ «кс» «кб» и​ же.​

​ диалоговое окно Ctrl+H​​ (в ячейке С2​ ​ замена​.​Нажимайте кнопку​

​ артикулов товаров вида​​ (январь), прибыль (январь).​А2​.​

Функции замены в Экселе: ПОДСТАВИТЬ, ЗАМЕНА

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

1. Функция ПОДСТАВИТЬ(Исходный текст; Что искать; На что заменить;[номер вхождения]).

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

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

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

Примеры применения функции ПОДСТАВИТЬ

Эту функцию применяют, когда вы знаете какой текст нужно заменить, но не знаете, где он расположен.

1. Функция ЗАМЕНИТЬ(Исходный текст; Начальная позиция; Количество знаков; Новый текст)

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

• Исходный текст – строка или ссылка, в которой нужно произвести замену
• Начальная позиция – порядковый номер первого символа для замены
• Количество знаков – количество символов, которые нужно заменить, начиная с начальной позиции
• Новый текст – строка, которую нужно вставить вместо исходной

Например, запишем формулу: =ЗАМЕНИТЬ(A1;79;4;«2016»)

Она заменит в тексте ячейки А1 символы №79-82 (4 шт) на строку «2016».

Функция ЗАМЕНИТЬ в Эксель

Комбинируйте функции замены с функциями ДЛСТР, ПОИСК, НАЙТИ и другими, чтобы добиться наилучших результатов. Изучите правила написания формул, использования функций, чтобы правильно применять описанные функции.

В следующем посте мы рассмотрим преобразование числа в текст и наоборот. А пока жду ваших вопросов и комментариев!

Microsoft Excel. Как в Экселе найти и заменить?

Эта функция в Экселе точно такая же как в Ворде. Необходимо вызвать функцию поиска (Contrl + F) в строке «Найти» ввести искомое слово а в строке «Заменить» ввести нужное слово и нажать «заменить». Если искомое слово не одно а несколько раз повторяется то можно нажать «заменить все»

То, что спрашивается в этом вопросе делается очень легко, последовательность не отличается от Ворда.

Первым делом нажимаем на «Ctrl» + «F», есть и другой вариант, но это более быстрый.

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

Как видно выше все достаточно очень просто.

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

В Microsoft Excel поиск нужного слова и его замену можно осуществить двумя способами. Легче воспользоваться горячим клавишами, которые привязаны к функции поиска — Ctrl + F. Набираем ее с клавиатуры и получаем следующее окно.

Вводим в окне Заменить нужное слово, ниже — вариант его замены и подтверждаем действие. Или просто ищем слово в разделе Найти и меняем его вручную.

Второй вариант — найти нужную кнопку в меню. Она находится в меню Главная — Редактирование. Кнопка скрыта, обычно отображается лишь кнопка Найти и выделить. Нажимаем на небольшую стрелочку под кнопкой и из появившегося меню выбираем пункт Заменить. И попадаем в аналогичное меню.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поиск или замена текста и чисел на листе

В этом курсе:

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

Чтобы найти текст или числа, нажмите клавиши CTRL + Fили перейдите на вкладку главная > редактирование> найти & выберите > найти.

В поле найти введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле найти и выберите в списке последний элемент поиска.

В условиях поиска можно использовать подстановочные знаки, например вопросительный знак (?) и звездочку (*).

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

Вопросительный знак заменяет один любой знак. Например, если ввести г?д, то будут найдены слова «гад», «гид» и «год».

Совет: Для поиска на листе звездочек, вопросительных знаков и знаков тильды (

) перед ними следует ввести в поле Найти знак тильды. Например, чтобы найти данные, содержащие слово «?», введите в качестве условия поиска строку

? . Для ввода тильды нажмите клавиши SHIFT +

? . Для ввода тильды нажмите клавиши SHIFT +

Функция ЗАМЕНИТЬ, входит в состав текстовых функций MS Excel и предназначена для замены конкретной области текстовой строки, в которой находится исходный текст на указанную строку текста (новый текст).

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

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



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

1. Создадим на листе рабочей книги табличного процессора Excel табличку со словами, как показано на рисунке:



2. Далее на листе рабочей книги подготовим область для размещения нашего результата – полученного слова «молоток», как показано ниже на рисунке. Установим курсор в ячейке А6 и вызовем функцию ЗАМЕНИТЬ:



3. Заполняем функцию аргументами, которые изображены на рисунке:

Выбор данных параметров поясним так: в качестве старого текста выбрали ячейку А2, в качестве нач_поз установили число 5, так как именно с пятой позиции слова «Молоко» мы символы не берем для нашего итогового слова, число_знаков установили равным 2, так как именно это число не учитывается в новом слове, в качестве нового текста установили функцию ПРАВСИМВ с параметрами ячейки А3 и взятием последних двух символов «ок».

Далее нажимаем на кнопку «ОК» и получаем результат:

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

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

1. Создадим на листе рабочей книги Excel табличку со фамилией и именем, как показано на рисунке:



2. Далее на этом же листе рабочей книги подготовим область для размещения нашего результата – измененных фамилий и имен. Заполним ячейки двумя типами формул как показано на рисунке:

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

Обратите внимание! Во второй формуле мы используем оператор «&» для добавления символа «а» к мужской фамилии, чтобы преобразовать ее в женскую. Для решения данной задачи можно было бы использовать функцию =СЦЕПИТЬ(B3;»а») вместо формулы =B3&»а» – результат идентичный. Но сегодня настоятельно рекомендуется отказываться от данной функции так как она имеет свои ограничения и более требовательна к ресурсам в сравнении с простым и удобным оператором амперсанд.