Excel замена символа на цифру - Word и Excel - помощь в работе с программами

Формула ЗАМЕНИТЬ и ПОДСТАВИТЬ для текста и чисел

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

Как работает функция ЗАМЕНИТЬ
Использование функции ЗАМЕНИТЬ с числами
Как заменить часть даты
Как заменить сразу несколько букв или слов
Как заменить текст в разных местах
Заменить заглавные буквы на строчные и наоборот
Описание функции ПОДСТАВИТЬ
Замена нескольких значений одной формулой
Что лучше использовать — ЗАМЕНИТЬ или ПОДСТАВИТЬ?

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

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

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

ЗАМЕНИТЬ(старый_текст; начальная_позиция; число_знаков, новый_текст)

Как видите, функция ЗАМЕНИТЬ имеет 4 аргумента, и все они обязательны для заполнения.

Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором вы хотите поменять некоторые символы.
Начальная_позиция — позиция первого символа в старый_текст, начиная с которого вы хотите сделать замену.
Число_знаков — количество символов, которые вы хотите заместить новыми.
Новый_текст – текст замены.

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

=ЗАМЕНИТЬ(«кит»;2;1;»о»)

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

=ЗАМЕНИТЬ(А2;2;1;»о»)

Примечание. Если аргументы начальная_позиция или число_знаков отрицательные или не являются числом, формула замены возвращает ошибку #ЗНАЧ!.

Использование функции ЗАМЕНИТЬ с числами

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

=ЗАМЕНИТЬ(A1; 9; 4; «2023»)

Обратите внимание, что мы заключаем «2023» в двойные кавычки, как вы обычно делаете с текстовыми значениями.

Аналогичным образом вы можете заменить одну или несколько цифр в числе. Например формула:

=ЗАМЕНИТЬ(A1;3;2;»23″)

И снова вы должны заключить значение замены в двойные кавычки («23»).

Примечание. Формула ЗАМЕНИТЬ всегда возвращает текстовую строку, а не число. На скриншоте выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B1 и сравните его с исходным числом, выровненным по правому краю в A1. А поскольку это текст, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в статье Как преобразовать текст в число.

Как заменить часть даты

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

Например, у вас есть дата в A3, скажем, 15 июля 1992г., и вы хотите изменить «июль» на «май». Итак, вы пишете формулу ЗАМЕНИТЬ(A3; 4; 3; «Май»), которая предписывает Excel поменять 3 символа в ячейке A3, начиная с четвертого. Мы получили следующий результат:

Почему так? Потому что «15-июл-92» — это только визуальное представление базового серийного номера (33800), представляющего дату. Итак, наша формула замены заменяет цифры начиная с четвертой (а это два нуля) в указанном выше числе на текст «Май» и возвращает в результате текстовую строку «338Май».

Чтобы заставить функцию ЗАМЕНИТЬ правильно работать с датами, вы должны сначала преобразовать даты в текстовые строки, используя функцию ТЕКСТ. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент старый_текст функции ЗАМЕНИТЬ:

=ЗАМЕНИТЬ(ТЕКСТ(A3; «дд-ммм-гг»); 4; 3; «Май»)

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

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A3; «дд-ммм-гг»); 4; 3; «Май»))

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

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

Рассмотрим следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на привычные нам телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить «123456789» в «123-456-789».

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

=ЗАМЕНИТЬ(A3;4;0;»-«)

Результат приведенной выше формулы замены выглядит следующим образом:

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

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;4;0;»-«);8;0;»-«)

В результате вы получаете номера телефонов в нужном формате:

Аналогичным образом вы можете использовать вложенные функции ЗАМЕНИТЬ, чтобы текстовые строки выглядели как даты, добавляя косую черту (/) там, где это необходимо:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»)

Кроме того, вы можете преобразовать текстовые строки в реальные даты, обернув приведенную выше формулу ЗАМЕНИТЬ функцией ДАТАЗНАЧ:

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»))

И, естественно, вы не ограничены в количестве функций, которые вы можете последовательно, как матрёшки, вложить друг в друга в одной формуле (современные версии Excel позволяют использовать до 8192 символов и до 64 вложенных функций в одной формуле).

Например, вы можете попробовать 3 вложенные функции ЗАМЕНИТЬ, чтобы число отображалось как дата и время:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»);9;0;» «);12;0;»:»)

Как заменить текст в разных местах

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

Предположим, у вас есть список адресов электронной почты в столбце A. И название одной компании изменилось с «ABC» на, скажем, «BCA». Изменилось и название их почтового домена. Таким образом, вы должны соответствующим образом обновить адреса электронной почты всех клиентов и заменить три буквы в адресах электронной почты, где это необходимо.

Но проблема в том, что имена почтовых ящиков имеют разную длину, и поэтому нельзя указать, с какой именно позиции начинается название домена. Другими словами, вы не знаете, какое значение указать в аргументе начальная_позиция функции Excel ЗАМЕНИТЬ. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию, с которой начинается доменное имя в адресе электронной почты:

=НАЙТИ(«@abc»; A3)

Затем вставьте указанную выше функцию НАЙТИ в аргумент начальная_позиция формулы ЗАМЕНИТЬ:

=ЗАМЕНИТЬ(A3; НАЙТИ(«@abc»;A3); 4; «@bca»)

Примечание. Мы включаем символ «@» в нашу формулу поиска и замены Excel, чтобы избежать случайных ошибочных замен в именах почтовых ящиков электронной почты. Конечно, вероятность того, что такие совпадения произойдут, очень мала, и все же вы можете перестраховаться.

Как вы видите на скриншоте ниже, у формулы нет проблем, чтобы поменять символы в разных позициях. Однако если заменяемая текстовая строка не найдена и менять в ней ничего не нужно, формула возвращает ошибку #ЗНАЧ!:

И мы хотим, чтобы формула вместо ошибки возвращала исходный адрес электронной почты без изменения. Для этого заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A3; НАЙТИ(«@abc»;A3); 4; «@bca»);A3)

И эта доработанная формула прекрасно работает, не так ли?

Заменить заглавные буквы на строчные и наоборот

Еще один полезный пример – заменить первую строчную букву в ячейке на прописную (заглавную). Всякий раз, когда вы имеете дело со списком имен, товаров и т.п., вы можете использовать приведенную ниже формулу, чтобы изменить первую букву на ЗАГЛАВНУЮ. Ведь названия товаров могут быть записаны по-разному, а в списках важно единообразие.

Таким образом, нам нужно заменить первый символ в тексте на заглавную букву. Используем формулу

=ЗАМЕНИТЬ(СТРОЧН(A3);1;1;ПРОПИСН(ЛЕВСИМВ(A3;1)))

Как видите, эта формула сначала заменяет все буквы в тексте на строчные при помощи функции СТРОЧН, а затем первую строчную букву меняет на заглавную (прописную).

Быть может, это будет полезно.

Описание функции ПОДСТАВИТЬ

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

Синтаксис формулы ПОДСТАВИТЬ в Excel следующий:

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

Первые три аргумента являются обязательными, а последний – нет.

Текст – исходный текст, в котором вы хотите заменить слова либо отдельные символы. Может быть тестовой строой, ссылкой на ячейку или же результатом вычисления другой формулы.
Старый_текст – что именно вы хотите заменить.
Новый_текст – новый символ или слово для замены старого_текста.
Номер_вхождения — какой по счёту экземпляр старый_текст вы хотите заменить. Если этот параметр опущен, все вхождения старого текста будут заменены новым текстом.

Например, все приведенные ниже формулы подставляют вместо «1» – цифру «2» в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:

=ПОДСТАВИТЬ(A3;»1″;»2″;1) — Заменяет первое вхождение «1» на «2».

=ПОДСТАВИТЬ(A3;»1″;»2″;2) — Заменяет второе вхождение «1» на «2».

=ПОДСТАВИТЬ(A3;»1″;»2″) — Заменяет все вхождения «1» на «2».

На практике формула ПОДСТАВИТЬ также используется для удаления ненужных символов из текста. Вы просто меняете их на пустую строку “”.

Например, чтобы удалить пробелы из текста, замените их на пустоту.

=ПОДСТАВИТЬ(A3;» «;»»)

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

=ПОДСТАВИТЬ(A3;»Х»;»Y»)

Замена нескольких значений одной формулой

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

Предположим, у вас есть текстовая строка типа « пр1, эт1, з1 » в ячейке A3, где «пр» означает «Проект», «эт» означает «этап», а «з» означает «задача». Вы хотите заместить три этих кода их полными эквивалентами. Для этого вы можете написать 3 разные формулы подстановки:

=ПОДСТАВИТЬ(A3;»пр»;»Проект «)

=ПОДСТАВИТЬ(A3;»эт»;»Этап «)

=ПОДСТАВИТЬ(A3;»з»;»Задача «)

А затем вложить их друг в друга:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3;»пр»;»Проект «); «эт»;»Этап «);»з»;»Задача «)

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

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

Замена неразрывных пробелов в ячейке Excel обычными
Убрать пробелы в числах
Удалить перенос строки в ячейке
Подсчитать определенные символы в ячейке

Что лучше использовать – ЗАМЕНИТЬ или ПОДСТАВИТЬ?

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

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

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

Источник

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

Поиск

Чтобы найти что-то, нажмите клавиши CTRL+F или перейдите в раздел Главная > Редактирование > найти & Выберите > Найти.

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

В поле Найти что: введите текст или цифры, которые вы хотите найти, или щелкните стрелку в поле Найти что: и выберите последний элемент поиска из списка.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
Нажмите кнопку Найти все или Найти далее , чтобы выполнить поиск.

Совет: При нажатии кнопки Найти все будут перечислены все вхождения условий, которые вы ищете, а щелкнув определенное вхождение в списке, будет выбрана его ячейка. Вы можете отсортировать результаты поиска Найти все , щелкнув заголовок столбца.
Щелкните Параметры>> , чтобы при необходимости определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните Формулы, Значения, Заметки или Примечания.
  
  Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти . На вкладкеЗаменить доступны только формулы.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
Если вы хотите найти текст или цифры с определенным форматированием, нажмите кнопку Формат, а затем выберите нужные значения в диалоговом окне Поиск формата .

Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с кнопкой Формат, выберите пункт Выбрать формат из ячейки, а затем щелкните ячейку с форматированием, которое требуется найти.

Заменить

Чтобы заменить текст или цифры, нажмите клавиши CTRL+H или перейдите в раздел Главная > Редактирование > Найти & Выберите > Заменить.

В поле Найти что: введите текст или цифры, которые вы хотите найти, или щелкните стрелку в поле Найти что: и выберите последний элемент поиска из списка.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
В поле Заменить на введите текст или числа, которые нужно использовать для замены текста поиска.
Нажмите Заменить все или Заменить.

Совет: При нажатии кнопки Заменить все все вхождения условий, которые вы ищете, будут заменены, в то время как Replace будет обновлять одно вхождение за раз.
Щелкните Параметры>> , чтобы при необходимости определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните Формулы, Значения, Заметки или Примечания.
  
  Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти . На вкладкеЗаменить доступны только формулы.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
Если вы хотите найти текст или цифры с определенным форматированием, нажмите кнопку Формат, а затем выберите нужные значения в диалоговом окне Поиск формата .

Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с кнопкой Формат, выберите пункт Выбрать формат из ячейки, а затем щелкните ячейку с форматированием, которое требуется найти.

Существует два разных метода поиска или замены текста или чисел на компьютере Mac. Во-первых, используется диалоговое окно «Найти & заменить «. Во-вторых, используется панель поиска на ленте.

Диалоговое окно «Поиск & замена «

Панель поиска и параметры

Нажмите клавиши CTRL+F или выберите Главная > Найти & Выберите > Найти.
В поле Найти: введите текст или цифры, которые нужно найти.
Нажмите кнопку Найти далее , чтобы выполнить поиск.
Вы можете дополнительно определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните Формулы, Значения, Заметки или Примечания.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .

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

Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».

Нажмите клавиши CTRL+F или выберите Главная > Найти & Выберите > Найти.
В поле Найти: введите текст или цифры, которые нужно найти.
Выберите Найти все , чтобы выполнить поиск всех вхождений.

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

Примечание: Вы можете изменить содержимое выделенной ячейки.

Нажмите клавиши CTRL+H или перейдите на главную страницу > Найти & выберите > Заменить.
В поле Найти введите текст или цифры, которые требуется найти.
Вы можете дополнительно определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
  Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
  - Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
  - Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
  - Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
В поле Заменить на введите текст или числа, которые вы хотите использовать для замены текста поиска.
Выберите Заменить или Заменить все.
Советы:
- При нажатии кнопки Заменить все все вхождения ищемого условия будут заменены.
- При нажатии кнопки Заменить можно заменить один экземпляр за раз, нажав кнопку Далее , чтобы выделить следующий экземпляр.

Выберите любую ячейку для поиска по всему листу или выберите определенный диапазон ячеек для поиска.
Нажмите клавиши COMMAND+F или выберите лупу, чтобы развернуть панель поиска и ввести текст или число, которые нужно найти в поле поиска.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
Нажмите клавишу RETURN.
Примечания:
- Чтобы найти следующий экземпляр элемента, который вы ищете, снова нажмите клавишу RETURN или в диалоговом окне Найти и выберите Найти далее.
- Чтобы указать дополнительные параметры поиска, выберите увеличительное стекло и выберите Поиск на листе или Поиск в книге. Можно также выбрать параметр Дополнительно , который открывает диалоговое окно Поиск .
Совет: Вы можете отменить выполняемый поиск, нажав клавишу ESC.

Поиск

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

В поле Найти что: введите текст или числа, которые нужно найти.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
Нажмите кнопку Найти далее или Найти все , чтобы выполнить поиск.

Совет: При нажатии кнопки Найти все будут перечислены все вхождения условий, которые вы ищете, а щелкнув определенное вхождение в списке, будет выбрана его ячейка. Вы можете отсортировать результаты поиска Найти все , щелкнув заголовок столбца.
Щелкните > Параметры поиска , чтобы при необходимости дополнительно определить поиск:
- В: Чтобы найти данные в пределах определенного выделенного фрагмента, нажмите кнопку Выбор. Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Направление: Вы можете выполнить поиск вниз (по умолчанию) или вверх.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти .

Заменить

В поле Найти что: введите текст или числа, которые нужно найти.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
В поле Заменить на введите текст или числа, которые нужно использовать для замены текста поиска.
Щелкните Заменить или Заменить все.

Совет: При нажатии кнопки Заменить все все вхождения условий, которые вы ищете, будут заменены, в то время как Replace будет обновлять одно вхождение за раз.
Щелкните > Параметры поиска , чтобы при необходимости дополнительно определить поиск:
- В: Чтобы найти данные в пределах определенного выделенного фрагмента, нажмите кнопку Выбор. Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Направление: Вы можете выполнить поиск вниз (по умолчанию) или вверх.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти .

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Смотрите также 0 0 0 — =ПОДСТАВИТЬ(B2;»+7″;8;1)количество номеров должно: не совсем так.: пример кинул. во втором столбце можно было бы не берем для текста (новый текст).REPLACEREPLACE чисел на листе поле пустым, чтобы нажмите кнопку. стрелку в полеПримечание:…..эксельпользователь быть ровно столько

Любой первый символнадеюсь на вашу номере телефонов начинаются использовать функцию =СЦЕПИТЬ(B3;»а») нашего итогового слова,С целью детального изучения

(ЗАМЕНИТЬ), Вы можете(ЗАМЕНИТЬ) имеет вот также можно использовать ничем не заменятьФорматДля поиска данных вНайти
Мы стараемся как можнонужно что бы
- : не владею такими сколько в базе! заменится на « помощь!
- на восьмерку! вместо формулы =B3&»а» число_знаков установили равным работы данной функции использовать функцию
такой синтаксис: функции ПОИСК и знаки), а затеми выберите нужные строках или столбцахи нажмите кнопку оперативнее обеспечивать вас они стали: премудростями.а при сложении

8Задача чтобы убратьКак сделать так, – результат идентичный. 2, так как
- рассмотрим один изFINDREPLACE(old_text,start_num,num_chars,new_text) НАЙТИ. нажмите кнопку параметры в диалоговом выберите в поле
- последнего поиска в актуальными справочными материалами0 1 0Gilvanov количества номеров из» . повторяющиеся значения.
  
   чтоб в обоих Но сегодня настоятельно именно это число простейших примеров. Предположим(НАЙТИ), чтобы обнаружитьЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)Вчера в марафонеНайти далее окнеПросматривать списке. на вашем языке. 0 0 0
: Попробуй так - этих двух столбцовэксельпользовательфактически в примере столбцах значения начинались
- рекомендуется отказываться от не учитывается в у нас имеется определённую строку текстаold_text30 функций Excel заилиНайти форматвариантВ условиях поиска можно
- Эта страница переведена0 1 0 =ЕСЛИ(ЛЕВСИМВ(E13;1)<>»8″;ЕСЛИ(ЛЕВСИМВ(E13;1)<>»7″;ЗАМЕНИТЬ(E13;1;2;8);ЗАМЕНИТЬ(E13;1;1;8))) после убирания повторяющихся: тогда остается проверить, обе строки с на одну и данной функции так новом слове, в
- несколько слов в или символ. В(старый_текст) – текстовая 30 днейНайти все.по строкам использовать подстановочные знаки, автоматически, поэтому ее 0 0 10Nic70y
  
   значений получается намного во всех ли повторяющимися значениями, но ту же цифру, как она имеет качестве нового текста разных столбцах, необходимо этом примере мы строка, в котороймы использовали функцию.Совет:или например вопросительный знак текст может содержать
- 0 1 0: =ЕСЛИ(ДЛСТР(A1)=10;7&ПСТР(A1;2;9);7&A1) больше чем в номерах проставлен код для этого им
- чтоб реально совпадающие свои ограничения и установили функцию ПРАВСИМВ получить новые слова хотим заменить первый будут заменены символы.INDEX
Примечание: Чтобы найти ячейки, точнопо столбцам (?) и звездочку неточности и грамматические 0 3 0Если городской номер самой базе. страны 7 мешает отличие в

 значения по номеру более требовательна к с параметрами ячейки используя исходные. Для пробел, встречающийся вstart_num(ИНДЕКС), чтобы вернуть Если поле соответствующие определенному формату,. (*). ошибки. Для нас0 1 5 без 8 ичто не так?эксельпользователь первой цифре, поскольку
телефона могли быть ресурсам в сравнении
- А3 и взятием данного примера помимо текстовой строке, на(нач_поз) – начальная значение ячейки, расположеннойЗаменить на
  
   можно удалить всеДля поиска данных сЗвездочка используется для поиска важно, чтобы эта 0 0 0 скобок.эксельпользователь: проверил. в первом столбце исключены? с простым и последних двух символов основной нашей функции
- двоеточие с пробелом. позиция старых символов. на пересечении заданныхнедоступно, откройте вкладку условия в поле конкретными свойствами выберите любой строки знаков. статья была вамт.е. третий символZ: а именно ввсё пучком. номера телефонов начинаютсяLVL
  
   удобным оператором амперсанд. «ок». ЗАМЕНИТЬ используем также=REPLACE(B3,FIND(» «,B3,1),1,»: «)num_chars строки и столбца.
  
  ЗаменитьНайти в поле
Например, если ввести полезна. Просим вас стал «1»: Планетяне! двух столбцах номеровспасибо огромное! на одну цифру,: Пример выложите

Советы

ximki-vinkiДалее нажимаем на кнопку функцию ПРАВСИМВ –=ЗАМЕНИТЬ(B3;НАЙТИ(» «;B3;1);1;»: «)(число_знаков) – количество25-й день марафона мы., а затем выбратьОбласть поискаг*д уделить пару секундabcd84Есть предложение сделать больше оказалось чемэксельпользователь а в другома вообще F1: Нужно заменить символ «ОК» и получаем данная функция служитФункцию старых символов. посвятим изучению функцииПри необходимости поиск можно ячейку с нужным
вариант, то будут найдены и сообщить, помогла: Вариант формулой в затяжную паузу в

support.office.com

30 функций Excel за 30 дней: ЗАМЕНИТЬ (REPLACE)

в базе в: вопрос решен, но столбце-на другую функция заменить() «, на . результат: для возврата определенногоREPLACEnew_text

REPLACE отменить, нажав клавишу форматированием в качествеформулы слова «год» и ли она вам, другой столбец Код оказании халяво-поддержки для

1,35 раза!!! не до конца.понимаете?ZНо при использованииПример 2. Рассмотрим еще числа знаков от(ЗАМЕНИТЬ) разрешается вкладывать(новый_текст) – текст,

Функция 25: REPLACE (ЗАМЕНИТЬ)

(ЗАМЕНИТЬ), которая относится ESC. примера. Щелкните стрелку, «город». с помощью кнопок =ПОДСТАВИТЬ(C1;»0″;»1″;2)

Как можно использовать функцию REPLACE (ЗАМЕНИТЬ)?

того, чтобы ТСZрезультат не достигается,iba2004: Вопрос: как вам

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

Синтаксис REPLACE (ЗАМЕНИТЬ)

значенияВопросительный знак заменяет один внизу страницы. ДляВот так наверное

основательно занялся самообразованием
: "Все не так,

 поскольку повторяющийся номер: Ловите! задали/требуется и как ЗАМЕНИТЬ, выдает ошибку,
 Предположим у нас То есть, например, таким образом в
 вместо старых символов. Она заменяет определённое все совпадения с
Форматили любой знак. Например, удобства также приводим

Ловушки REPLACE (ЗАМЕНИТЬ)

вернее)) Код =ЗАМЕНИТЬ(A1;3;1;»1″) по освоению азбучных ребята… » (с) телефона пропадает, ноТолько, видимо, под вы сами пытались т.к. используются в в ячейках табличного у нас есть исходном тексте можноФункция количество символов текстовой введенным текстом, нажмите, выберите пункт

Пример 1: Меняем код города в телефонном номере

примечания если ввести ссылку на оригиналКласс. работает, спасибо премудростей XL…Файл — до остается городской номер себя малёк придётся сделать?!. этих функциях процессора Excel имеются два слова: молоко делать множественные замены.REPLACE

строки другим текстом.
кнопку

Пример 2: Заменяем пробел на двоеточие с пробелом

Выбрать формат из ячейки.г?д (на английском языке). большоеКто «За!»?.. 100 кб - телефона и адрес заточить!ps На форумеBema столбцы с мужскими и каток, в В этом примере

(ЗАМЕНИТЬ) заменяет определённое
Итак, давайте внимательно посмотрим

Пример 3: Вложенные функции REPLACE (ЗАМЕНИТЬ) для вставки нескольких дефисов

Заменить, а затем щелкнитеПримечание:, то будут найденыФункции поиска и заменыКонстантин жps эксельпользователь! А в реальной структуре и поэтому неУдачи! есть раздел «Приемы»: Пример можете показать? фамилиями и именами результате мы должны телефонные номера должны количество символов, начиная информацию и примерыили ячейку с форматированием, слова «гад», «гид» в Excel используются

: ответ на вот
вы все внимательно

и форматах - понятно был ли
эксельпользователь
и неплохо работает

Сергей

office-guru.ru

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

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

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

для поиска в эту вот часть читаете?!. что есть - помимо городского номера: немного не то, поиск.: надо так =ПОДСТАВИТЬ(A1;»»»»;».») Необходимо заменить их первой тройки символов Чтобы заменить определённуюREPLACE.Выполните одно из указанных,Совет: книге необходимой информации, вопроса:vikttur как надо… телефона у клиента надо чтобы повторяющиеся

эксельпользователь

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

ximki-vinki буквы в определенныхСоздадим на листе рабочей и после второй. строку текста в
(ЗАМЕНИТЬ), а еслиMicrosoft Excel сохраняет параметры ниже действий.значения Звездочки, знак вопроса и например определенного числа»заменить первые два: Зато премудростью цитироватьэксельпользователь еще и мобильный.
значения были скрыты,: БАЗУ ДАННЫХ КЛИЕНТОВ

: Да , все местах так, чтобы книги табличного процессора Используя любом месте текста у Вас есть форматирования, которые можноЧтобы найти текст илии символы тильда (~) или текстовой строки. символа в каждой без толку и: я кажется нашелвообщем невозможно наряду поскольку дальше надо ВЫЛОЖИТЬ??? работает, спасибо преобразовать их в Excel табличку соноль оригинала, Вы можете

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

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

примечания можно найти вНа вкладке ячейке» БЕЗДУМНО владеете с причину. с повторяющимся номером будет распечатать иа пример такойЮрий М женские. словами, как показано, как количество символов

использовать функцию примеры, прошу делиться еще раз выполнитьНайти вседоступны только на
данных листа передГлавнаяЕсли у Вас избытком.просто во втором мобильного телефона убрать скопировать в другой8123456789 7123456789: Можно ведь на

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

exceltable.com

формула замены символа «, в ячейке

Функция данные и неНайти далее
Поиск в полеРедактирование начиная с ячейки цитирование вычищено. Хуже?

 повторяющиеся номера телефонов, поскольку адрес только

 повторяющиеся значениядумаю всем наглядно

 формул): Ctrl+H со фамилией и книги подготовим область

 что ни один рассмотрим позже вREPLACE удается найти символы,
.; на вкладке «
Найти

planetaexcel.ru

Как заменить первую цифру в ячейке???

нажмите кнопку A2 и ниже,rom850425-эксельпользователь
поэтому сумма двух
для первого столбцаэксельпользователь понятно о чемВ верхнем поле
именем, как показано для размещения нашего
из символов телефонного рамках нашего марафона.(ЗАМЕНИТЬ) заменяет символы которые вы знаетеСовет:Заменить. Например, чтобы найтиНайти и выделить то, чтобы заменить
, тема прикрыта до столбцов больше чем есть.: Михаил С., спасибо я говорю! ~* на рисунке: результата – полученного номера не будет

При помощи функции внутри текста, основываясь
содержал сведения, может При нажатии кнопки

» доступны только данные, которые содержат. первые два символа получения модератором сообщения
количество номеров ввижу решение как Вам огромное!!!заранее спс!

В нижнем точка.Далее на этом же слова «молоток», как
удалён, лишь добавятся
REPLACE
на заданном количестве
потребоваться снимите нужныеНайти всеформулы
«?», вы введите

Выполните одно из указанных того, что находится о том, какой базе указывал выше другойспасли от огромнойLVLrom850425 листе рабочей книги показано ниже на 2 дефиса.(ЗАМЕНИТЬ) Вы можете символов и начальной

 параметры форматирования из, каждого экземпляра условия,

.~?
ниже действий. в этой ячейке
пункт правил Выэксельпользователь
пользователь через выделение бумажной работы!!!: Ваша база данных: Добрый день уважаемые подготовим область для рисунке. Установим курсор=REPLACE(REPLACE(B3,4,0,»-«),8,0,»-«) изменить первые три позиции. предыдущего поиска. В которые вы ищете
Для поиска данных с

как критерии поиска.Чтобы найти текст или
на что-то другое, игнорируете.: а как заменить
цветом, а потом

правильно я понял здесь ни кому специалисты! размещения нашего результата в ячейке А6=ЗАМЕНИТЬ(ЗАМЕНИТЬ(B3;4;0;»-«);8;0;»-«) цифры в телефонномФункция диалоговом окне

 указываются и щелкнув учетом регистра установитеНажмите кнопку
числа, выберите пункт например, на «ЗА»
Вы думаете, приятно +7 на 8 сделать фильтрацию и и попробовал, что не нажна, обЗадачка следующего характера. – измененных фамилий
и вызовем функциюУрок подготовлен для Вас
номере, например, когда
REPLACE
Поиск и замена
нужное вхождение в

 флажокПараметрыНайти достаточно применить формулу: за Вами бегать ?

 просто убрать цветные в вашей формуле этом пишут каждыйИмеется два столбца и имен. Заполним

 ЗАМЕНИТЬ: командой сайта office-guru.ru
задан новый код
(ЗАМЕНИТЬ) может заменять

перейдите на вкладку списке сделает ячейкиУчитывать регистр
, чтобы определить дополнительные.=»ЗА»&ПСТР (A2;3;1000) с веником иМихаил С. строки столбец с начинающимися день. Сделайте файл с количеством значений ячейки двумя типами
Заполняем функцию аргументами, которыеИсточник: http://blog.contextures.com/archives/2011/01/26/30-excel-functions-in-30-days-25-replace/ города. В нашем символы в текстовойПоиск active. Можно сортировать.
условия поиска приЧтобы найти и заменитьто есть эта мусор убирать?: =ЗАМЕНИТЬ(A1;1;2;»8″)зыэксельпользователь семерками заменился на

 с примером из более 1000 в формул как показано изображены на рисунке:Перевел: Антон Андронов случае новый код строке, например:

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

Автор: Антон Андронов города введён вИзменить код города вПараметрыНайти только символы, введенные :cry:

Для поиска данных на выберите пункт первых двух символов
: Есть столбец с: лень посмотреть?iba2004 восьмерками? похожими данными иНадо удалить совпадающие

Обратите внимание! Во второй так: в качествеФункция ЗАМЕНИТЬ, входит в
столбце C, а телефонном номере., чтобы открыть параметрывсе, щелкнув заголовок в поле листе или воЗаменить

 вот это вот0 0 0Z, можно такую же

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

 старого текста выбрали состав текстовых функций откорректированные телефонные номераЗаменить пробел на двоеточие форматирования. Щелкните стрелку столбца.
Найти всей книге выберите

. «ЗА», а остальное 0 0 0

: Как вариант - формулу, но уже всё получилось!!!

 что Вам помогут»Удалить дубликаты» в
оператор «&» для ячейку А2, в MS Excel и

 отображаются в столбце с пробелом.
рядом с полемЧтобы заменить текст или, установите флажок в полеВ поле оставит как было…0 0 0
изучать функции XL,
если не надохороший сайт!!! возрастает в несколько

 «работа с данными» добавления символа «а» качестве нач_поз установили предназначена для замены D.
Использовать вложенную функциюФормат
 числа, введите знакиЯчейка целикомИскатьНайтиа вообще, совет 0 0 10
вызывать ч/з F1 первое число приравниватьспецы супер! раз

planetaexcel.ru

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

 не помогает, поскольку к мужской фамилии,
число 5, так конкретной области текстовой
=REPLACE(B3,1,3,C3)REPLACE
и нажмите кнопку для замены в
.вариант
введите текст или
на будущее…0 0 0
справку и внимательноэксельпользователь
откуда такие знания?Михаил С.
в первом столбце чтобы преобразовать ее
как именно с строки, в которой
=ЗАМЕНИТЬ(B3;1;3;C3)(ЗАМЕНИТЬ) для вставки

Очистить полеЕсли вы хотите найтина листе
числа, которые нужноПожалуйста, формулируйте вопрос

0 3 0 читать, изучать форум……..

CyberForum.ru

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

: Товарищи!где их получали?: =ЗАМЕНИТЬ(B1;1;1;»8″) номера телефонов начинаются
в женскую. Для пятой позиции слова находится исходный текст
Чтобы определить начальную позицию нескольких дефисов..Заменить на текст или числаили искать, или щелкните более четко.0 0 5Может быть подойдетЧто-то не получается!!!
Михаил С.
эксельпользователь на семерку, а решения данной задачи «Молоко» мы символы на указанную строку для функции
ФункцияДля поиска текста или
(или оставьте это с определенным форматированием,

в книге

Источник

Заменой остальных символов
Извлечение цифр из ячейки формулой
С помощью пользовательских функций (UDF)
С помощью синтаксиса регулярных выражений
Извлечь цифры из текста в 1 клик
Видеоинструкция

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

Заменой остальных символов

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

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

Извлечение цифр из ячейки формулой

Такая формула массива будет работать только в сборках Excel, в которых поддерживается функция СЦЕП (аналог JOIN в Google Spreadsheets) – это некоторые сборки Excel 2016, 2019, и все релизы Excel 2021. Отличие СЦЕП от СЦЕПИТЬ в том, что она может принимать на вход диапазоны и массивы и возвращать при этом единое значение.

Так будет выглядеть формула, извлекающая только цифры из ячейки A1:

=СЦЕП(ЕСЛИОШИБКА(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)+0;""))

Механика её работы:

Сначала измеряется длина текстового значения ячейки функцией ДЛСТР
Далее создается текстовое выражение диапазона строк с помощью конкатенации амперсандом (&)
Это текстовое выражение преобразуется в реальный диапазон функцией ДВССЫЛ
Функция СТРОКА возвращает массив чисел от 1 до числа, соответствующего концу этого диапазона
Функция ПСТР извлекает по этому массиву чисел соответствующие символы по порядку
К каждому символу прибавляется 0 – это позволяет отличить цифры от остальных символов, т.к. цифры не вернут ошибку, а все остальные вернут
Функция ЕСЛИОШИБКА заменяет все ошибки на пустые строки
В финале функция СЦЕП объединяет массив в единую строку

С помощью пользовательских функций (UDF)

Пример пользовательской функции, которая поможет с извлечением чисел из текста:

Function extrNum(x As String) As Long   
   For n = 1 To Len(x)   
       If Mid(x, n, 1) Like "#" Then extrNum = extrNum & Mid(x, n, 1)   
   Next n   
End Function

Как применить данный код:

внедрить его в новый модуль книги (используемой сейчас или в личной книге макросов),
применить функцию на листе в подобном виде: =extrNum(A1).

С помощью синтаксиса регулярных выражений

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

Как в платной, так и в бесплатной версии !SEMTools есть функции:

regexReplace, берущая на вход 3 аргумента — строку с данными, текст регулярного выражения и строку для замены;
regexExtract с двумя аргументами — строкой и паттерном для извлечения.

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

=REGEXREPLACE(A1;"[^d]";"")

Выражение для извлечения первой сплошной последовательности цифр:

Что потребуется:

установить !SEMTools,
применить функции.

Извлечь цифры из текста в 1 клик

Пользователям платной версии !SEMTools доступна возможность извлекать цифры моментально прямо “на месте”, не прописывая никаких функций.

Макрос находится в группе «ИЗВЛЕЧЬ» в меню “Извлечь символы”.

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

Если необходимости в этом нет, можно постфактум просто заменить пробел на «пустоту».

Видеоинструкция

Удалить текст, а цифры оставить — именно такова механика алгоритма !SEMTools. Смотрите короткий видеопример:

Извлечение цифр из ячеек со смешанным содержимым в !SEMTools

Нужно извлечь числа из текста в Excel?
В !SEMTools есть это и сотни других готовых решений!!

Источник

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

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

Далее вызываем Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F. Откроется диалоговое окно под названием Найти и заменить.

В единственном поле указывается информация (комбинация символов), которую требуется найти. Если не использовать подстановочные символы или т.н. джокеры (см. ниже), то Excel будет искать строгое совпадение заданных символов. Для вывода результатов поиска предлагается два варианта: выводить все результаты сразу – кнопка Найти все; либо выводить по одному найденному значению – кнопка Найти далее.

После запуска поиска программа Excel быстро-быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия искомой комбинации символов. Если такая комбинация обнаружена, то в случае нажатия кнопки Найти все Excel вываливает все найденные ячейки.

Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.

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

Поиск нестрогого соответствия символов

Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах. Под джокерами могут скрываться различные символы, и Excel видит лишь их относительное расположение в поисковой фразе. Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Есть еще один джокер – знак «?». Под ним может скрываться только один неизвестный символ. К примеру, указав для поиска критерий 1?6, Excel найдет все ячейки содержащие последовательность 106, 116, 126, 136 и т.д. А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).

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

Продвинутый поиск

Мало, кто обращается к кнопке Параметры в диалоговом окне Найти и заменить. А зря. В ней скрыто много полезностей, которые помогают решить проблемы поиска. После нажатия кнопки Параметры добавляются дополнительные поля, которые еще больше углубляют и расширяют условия поиска.

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

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

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

Поиск формата – это хорошо, но чаще искать приходится конкретные значения. И тут Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.

Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.

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

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т.е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.

Поиск и замена данных

Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.

Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.

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

По аналогии с простым поиском, менять можно и формат.

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

Напоследок рассмотрим один классный трюк с поиском и заменой. Многие знают, что в ячейку можно вставить разрыв строк с помощью комбинации Alt+Enter.

А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Жмем Ok. Все переносы строк заменились пробелами.

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

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

Поделиться в социальных сетях:

Источник

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

Использование функции ЗАМЕНИТЬ с числами

Как заменить часть даты

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

Как заменить текст в разных местах

Заменить заглавные буквы на строчные и наоборот

Описание функции ПОДСТАВИТЬ

Замена нескольких значений одной формулой

Что лучше использовать – ЗАМЕНИТЬ или ПОДСТАВИТЬ?

Поиск

Заменить

Поиск

Заменить

Дополнительные сведения

Рекомендуемые статьи

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

Автоматическая замена

Дополнительные параметры

Еще статьи по данной теме:

Помогла ли Вам статья?

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

Советы

30 функций Excel за 30 дней: ЗАМЕНИТЬ (REPLACE)

Функция 25: REPLACE (ЗАМЕНИТЬ)

Как можно использовать функцию REPLACE (ЗАМЕНИТЬ)?

Синтаксис REPLACE (ЗАМЕНИТЬ)

Ловушки REPLACE (ЗАМЕНИТЬ)

Пример 1: Меняем код города в телефонном номере

Пример 2: Заменяем пробел на двоеточие с пробелом

Пример 3: Вложенные функции REPLACE (ЗАМЕНИТЬ) для вставки нескольких дефисов

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

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

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

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

формула замены символа «, в ячейке

Как заменить первую цифру в ячейке???

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

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

Заменой остальных символов

Извлечение цифр из ячейки формулой

С помощью пользовательских функций (UDF)

С помощью синтаксиса регулярных выражений

Извлечь цифры из текста в 1 клик

Видеоинструкция

Поиск нестрогого соответствия символов

Продвинутый поиск

Поиск и замена данных

А вот еще интересные статьи: