Как заменить значения в ячейках excel

Содержание:

Найти и заменить символы подстановки в Excel
Как заменить звездочку «*» в Excel?
Как работает инструмент замены символов в Excel
Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()
Как работает инструмент замены символов в Excel
Как быстро найти и заменить символ надстрочным в Excel?
- - Найти и заменить на верхний индекс функцией Найти и заменить
  - Найти и заменить надстрочным индексом на VBA
Замена содержимого ячейки в Excel
Фильтрация данных
Массовая замена в !SEMTools
Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
Замена содержимого ячейки в Excel
14 комментариев

Найти и заменить символы подстановки в Excel

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

Давайте возьмем пример данных,

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

Первая попытка: ошибка

Обычно мы просто нажимаем «CTRL + F», вводим «*» в поле «найти» и нажимаем «Найти все». Он покажет все записи в результатах поиска, и в этом нет никакой путаницы. Когда мы ищем «*», он ищет все, и это не то, что мы хотим.

Вторая попытка: ошибка

Теперь давайте попробуем найти с помощью комбинации «*» и текста. Скажем, мы ищем «windows *», и в результатах поиска вы можете увидеть текст, начинающийся с «windows». Это также ожидается при поиске с использованием подстановочных знаков и довольно ясно, что это также не решит нашу проблему.

Третья попытка: успех!

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

Итак, нам нужно, чтобы Excel понял, что мы хотим искать звездочку как обычный текст, а не как подстановочный знак, и это можно сделать. Да, чтобы это произошло, нам нужно использовать специальный символ Тильда (

). Этот специальный символ можно найти на клавиатуре ‘1’ на клавиатуре. Это то, где я мог видеть на своей клавиатуре, и она может быть в другом месте на вашей клавиатуре в зависимости от того, где вы находитесь.

Теперь, когда вы введете « windows

* » в поле поиска и увидите результаты, вы сможете увидеть результаты, содержащие звездочку. Это означает, что мы можем найти звездочку как обычный текстовый символ, и мы добились успеха!

Поэтому, когда мы хотим найти любой подстановочный знак как обычный текст, нам нужно использовать специальный символ Tilde (

) вместе с подстановочным знаком в поле «найти».

Это способ поиска подстановочных знаков в Excel как обычного текста, и это один из простых способов сделать это.

Если у вас есть что добавить или какие-либо другие простые способы, поделитесь с нами комментариями.

Как заменить звездочку «*» в Excel?

Расчет среднего значения в программе microsoft excel

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

*» (явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:

Аналогичная ситуация и при замене или удалении вопросительного знака и тильды. Производя замену

?» (для тильды —

») мы также без проблем сможем заменить или удалить спецсимвол.

Как работает инструмент замены символов в Excel

Как суммировать значения между двумя датами

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

Рассмотрим оба варианта.

Вариант 1: Поиск и замена

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

Во вкладке с инструментами “Главная” нажмите по инструменту “Заменить”. Он находится в группе инструментов “Найти и выделить”.

Запустится окошко “Найти и заменить”. Там переключитесь во вкладку “Заменить”. Введите в поле “Найти” нужный символ или их комбинацию. В поле “Заменить на” укажите символы или комбинацию, на которые нужно выполнить замену. Нажмите кнопку “Найти далее”. Можете также нажать кнопку “Заменить все” в том случае, если точно уверены, что не сделаете лишних замен.</li>Если вы нажали “Найти далее” у вас будет показан первый же найденный результат в документе. Если его требуется заменить, то нажмите соответствующую кнопку, если же нет, то жмите “Найти далее” для перехода к следующему результату.</li>

Проделывайте эти действия, пока не замените все, что необходимо.</li></ol>

Также этот вариант можно реализовать немного иначе:

Введите искомую комбинацию символов и комбинацию для замены. Нажмите “Найти все”.
Все найденные символы будут отображены в виде перечня в нижней части окошка. Там будет указана на каком листе они встречаются и в какой ячейке. Выделите ту комбинацию, которую нужно заменить. Нажмите кнопку “Заменить” для замены на ранее указанную комбинацию.

</ol>

Вариант 2: Автоматическая замена

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

Вызовите окошко поиска и замены символов по аналогии с предыдущей инструкцией.
Пропишите в поля “Найти” и “Заменить на” соответствующие значения.
Нажмите кнопку “Заменить все”.
Обычно процедура выполняется моментально, но если в документе много заменяемых элементов, то процесс может занять пару секунд. По завершении вы получите оповещение об успешной замене.

Настройка дополнительных параметров замены

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

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

Также можно настроить форматирование ячеек после замены. Для этого тоже нажмите на кнопку “Формат”, но только на ту, что расположена напротив строки “Заменить на”.</li>Настройки формата для изменяемых ячеек можно экспортировать из уже имеющихся. Для этого кликните правой кнопкой мыши по кнопке “Формат” и выберите в контекстном меню вариант “Выбрать формат из ячейки”.</li>Укажите ячейку, из которой будут выбраны настройки форматирования.</li></ol>

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

https://exceltable.com/funkcii-excel/primer-funkcii-zamenit
https://ru.extendoffice.com/excel/functions/excel-substitute-function.html
https://office-guru.ru/excel/poisk-i-zamena-v-excel-15.html
https://www.planetaexcel.ru/techniques/7/2775/
https://public-pc.com/instrument-zameny-simvolov-v-tablichnom-redaktore-excel/

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

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

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

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

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

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

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

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

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

Как работает инструмент замены символов в Excel

Рассмотрим оба варианта.

Вариант 1: Поиск и замена

Во вкладке с инструментами “Главная” нажмите по инструменту “Заменить”. Он находится в группе инструментов “Найти и выделить”.

Запустится окошко “Найти и заменить”. Там переключитесь во вкладку “Заменить”. Введите в поле “Найти” нужный символ или их комбинацию. В поле “Заменить на” укажите символы или комбинацию, на которые нужно выполнить замену. Нажмите кнопку “Найти далее”. Можете также нажать кнопку “Заменить все” в том случае, если точно уверены, что не сделаете лишних замен.
Если вы нажали “Найти далее” у вас будет показан первый же найденный результат в документе. Если его требуется заменить, то нажмите соответствующую кнопку, если же нет, то жмите “Найти далее” для перехода к следующему результату.

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

Также этот вариант можно реализовать немного иначе:

Введите искомую комбинацию символов и комбинацию для замены. Нажмите “Найти все”.
Все найденные символы будут отображены в виде перечня в нижней части окошка. Там будет указана на каком листе они встречаются и в какой ячейке. Выделите ту комбинацию, которую нужно заменить. Нажмите кнопку “Заменить” для замены на ранее указанную комбинацию.

Вариант 2: Автоматическая замена

Вызовите окошко поиска и замены символов по аналогии с предыдущей инструкцией.
Пропишите в поля “Найти” и “Заменить на” соответствующие значения.
Нажмите кнопку “Заменить все”.
Обычно процедура выполняется моментально, но если в документе много заменяемых элементов, то процесс может занять пару секунд. По завершении вы получите оповещение об успешной замене.

Настройка дополнительных параметров замены

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

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

Как быстро найти и заменить символ надстрочным в Excel?

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

Найти и заменить на верхний индекс функцией Найти и заменить

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

1. Выберите диапазон, в котором вы хотите найти и заменить работу, и нажмите Ctrl + H для открытия Найти и заменить Диалог.

2. в Найти и заменить диалоговое окно, нажмите Заменять вкладка, а затем введите текст, который хотите найти, в Найти то, что текстовое окно. Смотрите скриншот:

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

снимок экрана:

4. Нажмите Заменить все. Появится диалоговое окно с напоминанием о том, сколько замен было произведено. Смотрите скриншот:

Наконечник: Но с помощью этого метода вы можете указать только номер 2, нажав 0178, и 3 на 0179.

Найти и заменить надстрочным индексом на VBA

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

1. Нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модуль, и вставьте ниже код VBA в скрипт.

VBA: найти и заменить на надстрочный формат

Sub DoConvert()
'UpdatebyExtendoffice20160705
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
    Set xRg = Application.InputBox("Select a range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    For Each xCell In xRg
        If xCell.Value = "YL3" Then
            xCell.Characters(1, 1).Font.Superscript = True
        End If
    Next
End Sub

3. Нажмите F5 нажмите клавишу, чтобы запустить код, и появится диалоговое окно, напоминающее вам о выборе диапазона. Смотрите скриншот:

4. Нажмите OK, конкретный символ в определенной строке был преобразован в формат надстрочного индекса в выделенном фрагменте. Смотрите скриншот:

Чаевые: в коде «YL3”- это строка, из которой вы хотите найти, и (1, 1) указывает на преобразование первого символа строки, если вы хотите преобразовать первые два символа, вы можете изменить его как (1, 1), если вы хотите преобразовать 2 символ из второго символа строки, вы можете изменить его как ( 1, 2).

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

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

На вкладке Главная нажмите команду Найти и выделить, а затем из раскрывающегося списка выберите пункт Заменить.
Появится диалоговое окно Найти и заменить. Введите текст, который Вы ищете в поле Найти.
Введите текст, на который требуется заменить найденный, в поле Заменить на. А затем нажмите Найти далее.
Если значение будет найдено, то содержащая его ячейка будет выделена.
Посмотрите на текст и убедитесь, что Вы согласны заменить его.
Если согласны, тогда выберите одну из опций замены:
- Заменить: исправляет по одному значению зараз.
- Заменить все: исправляет все варианты искомого текста в книге. В нашем примере мы воспользуемся этой опцией для экономии времени.
Появится диалоговое окно, подтверждающее количество замен, которые будут сделаны. Нажмите ОК для продолжения.
Содержимое ячеек будет заменено.
Закончив, нажмите Закрыть, чтобы выйти из диалогового окна Найти и заменить.

Фильтрация данных

Рассмотрим пример. Предположим, что у нас имеется список сотрудников компании и мы хотим отфильтровать только тех сотрудников, у которых фамилии начинаются на конкретную букву (к примеру, на букву «п»):

Для начала добавляем фильтр на таблицу (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L). Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):

Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:

В общем случае при фильтрации данных мы можем использовать абсолютно любые критерии, никак не ограничивая себя в выборе маски поиска (произвольный текст, различные словоформы, числа и т.д.). К примеру, чтобы показать все варианты фамилий, которые начинаются на букву «к» и содержат букву «в», то применим фильтр «к*в*» (т.е. фраза начинается на «к», затем идет произвольный текст, потом «в», а затем еще раз произвольный текст). Или поиск по «п?т*» найдет фамилии с первой буквой «п» и третьей буквой «т» (т.е. фраза начинается на «п», затем идет один произвольный символ, затем «т», и в конце опять произвольный текст).

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

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

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

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

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

Замена списка слов на другой список в !SEMTools

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На вкладке Главная нажмите команду Найти и выделить, а затем из раскрывающегося списка выберите пункт Заменить.
Появится диалоговое окно Найти и заменить. Введите текст, который Вы ищете в поле Найти.
Введите текст, на который требуется заменить найденный, в поле Заменить на. А затем нажмите Найти далее.
Если значение будет найдено, то содержащая его ячейка будет выделена.
Посмотрите на текст и убедитесь, что Вы согласны заменить его.
Если согласны, тогда выберите одну из опций замены:
- Заменить: исправляет по одному значению зараз.
- Заменить все: исправляет все варианты искомого текста в книге. В нашем примере мы воспользуемся этой опцией для экономии времени.
Появится диалоговое окно, подтверждающее количество замен, которые будут сделаны. Нажмите ОК для продолжения.
Содержимое ячеек будет заменено.
Закончив, нажмите Закрыть, чтобы выйти из диалогового окна Найти и заменить.

7696013.04.2017 Скачать пример

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

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

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

где

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

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

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

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

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

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

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

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

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

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

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

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

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

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

14 комментариев

Ренат, огромное спасибо за «Ctrl+J » — я так долго искала, как указать знак переноса в «Найти». Не поленилась написать сообщение, чтобы сказать еще раз спасибо ))

Согласен, хороший трюк, сам пользуюсь)

Спасибо огромное. Ctrl+j — это меша круто! !

Как найти и заменить масив данных к примеру из базы в 200 000 вычистить одно

Как найти и заменить массив данных, к примеру из базы в 200 000 вычистить одной операцией 2 000 контактов?

Спасибо большое! Нигде больше не мог найти описание, что это именно Ctrl+J!

Ctrl+J спас меня.

Добрый день! А не подскажете ли, как найти (и выделить) пустой текст в ячейке? Объясню, чего хочу. В одном файле формулой обрабатываются штрих-коды, в текстовом формате, так как код больше 13 символов. При копировании в другой файл на месте отсутствующих штрих-кодов вставляется пустой текст «». О не видим и в ячейке никак не отображается. Для выгрузки в 1С нужно такие ячейки очистить.

Всем привет, такие вопросы по Эксель: 1. ФИО в нашей базе записано в одну стороку, как разбить на три отдельных, не потеряв данные. 2. Телефон в столбце, имеет разные форматы, пример: 375 29 123456, 8029-223456, 375-29-123456. Как привести в один правильный порядок: +37529123456. Спасибо за ответы.

Андрей, нужно выделить столбец с ФИО, нажать вкладку «данные» на ПИ, «текст по столбцам», «фиксированной ширины», несколько раз «далее», готово!!

Здравствуйте, Я смотрел Ваше руководство по вопросу найти определенное слово в Excel. У меня возникла одна задача и есть к Вам вопрос. А можно это слово которое ищешь по тексту в Excel затем, как нашел все варианты, заменить их на такое же самое слово, но только что бы оно было полужирным курсивом выделено по всей таблице. Я пытался так сделать с помощью найти и заменить, но оно заменяет на жирный формать весь текст, а не заданое слово. Что Вы мне посоветуете? Спасибо Вам зарание

У меня нормально всё получается, специально проделала эту операцию ))

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

д.д. как переставитьместами, что бы не перепечатывать, заранее спасибо! 262-о на О-262 и.тд. 263-о 264-о 265-о 266-о 267-о 268-о 269-о 270-о 271-о 271-о-а 272-о 273-о 274-о 275-о 276-о 277-о 278-о 279-о 280-о 281-о 282-о 283-о 284-о 285-о 286-о 287-о 288-о 289-о 290-о 291-о 291-о-а 292/1-о 292-о 293-о 294-о 295-о 296-о 297-о 297/1-о 298-о 298-а-о 299-о 300-о 301-о 302-о 303-о 304-о 305-о 306-о 307-о 308-о 309-о 310-о 311-о 312-о 313-о 314-о 315-о 316-о 317-о 318-о 319-о 320-о 321-о 322-о 323-о 324-о 326-о 325-о 327-о 329-о 330-о 331-о 332-о 333-о 334-о 335-о 336-о 337-о 338-о 339-о 340-о 342-о 343-о 344-о 345-о 347-о 348-о 349-о