Excel замена символа пробела

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

​Смотрите также​​ нажатии кнопки «Параметры»​: символ знак вопроса​0 0 0​ чем пробелов (при​ использования функции​ заменить​ на «2» («Квартал​ в тексте заменяются​ вместо значения аргумента​ еще раз выполнить​ числа, введите знаки​, а затем выбрать​; на вкладке «​или​ «город».​.​Примечание:​ поискового окна). А​ является служебным символом​ 0 0 10​

​ условии, что нет​ПОДСТАВИТЬ​Новый_текст​ 1, 2012 г.»)​ фрагментом «нов_текст».​ «стар_текст» в текстовой​

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

   

2. ​ Мы стараемся как можно​ именно — необходимо​

   • ​ — И естественно,​0 0 0​​ лишних пробелов). Соответственно,​​для наглядности.​

   • ​- текст, на​Квартал 1, 2012 г.​Скопируйте образец данных из​​ строке. Функция ПОДСТАВИТЬ​​ данные и не​

3. ​ поле​​ форматированием в качестве​​» доступны только​.​ любой знак. Например,​ ниже действий.​​ оперативнее обеспечивать вас​​ поставить/снять галочку у​ при попытке заменить​ 0 3 0​

   ​ формула для расчета​При выгрузке данных из​ который заменяем​Замена одного текста на​ следующей таблицы и​

   • ​ используется, когда нужно​ удается найти символы,​Заменить на​​ примера. Щелкните стрелку​​формулы​Для поиска данных в​ если ввести​

   • ​Чтобы найти текст или​ актуальными справочными материалами​ параметра «Ячейка целиком​​ на знак вопроса​​0 0 5​ будет простой:​ 1С, копировании информации​

     ​Номер_вхождения​​ другой внутри заданной​ вставьте их в​ заменить определенный текст​ которые вы знаете​(или оставьте это​ рядом с кнопкой​​.​​ строках или столбцах​г?д​ числа, выберите пункт​​ на вашем языке.​​ «. В рассматриваемом​

4. ​ у Excel заменят​​ 0 0 0​​Если предполагается, что в​ с вебстраниц или​- необязательный аргумент,​

   • ​ текстовой строки -​ ячейку A1 нового​ в текстовой строке;​ содержал сведения, может​​ поле пустым, чтобы​​Формат​​Для поиска данных с​​ выберите в поле​​, то будут найдены​​Найти​

   • ​ Эта страница переведена​ случае эту галочку​ ВСЕ данные в​​…..​​ ячейке могут находиться​​ из документов Word​​ задающий номер вхождения​​ весьма частая ситуация​​ листа Excel. Чтобы​

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

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

   • ​Abram pupkin​ ввиду обычную замену​ они стали:​​ то формула будет​​ дело с неразрывным​

   • ​ замену​ данными в Excel.​ выделите их и​​ любой текст начиная​​ предыдущего поиска. В​​ нажмите кнопку​​, а затем щелкните​

5. ​Учитывать регистр​по строкам​Совет:​ текст или числа,​​ неточности и грамматические​​: Зибин, благодарю.​ — (Excel 2003:​0 1 0​​ чуть посложнее, но​​ пробелом — спецсимволом,​

   ​Обратите внимание, что:​​ Реализовать подобное можно​ нажмите клавишу F2,​ с определенной позиции.​ диалоговом окне​​Найти далее​​ ячейку с форматированием,​.​или​ Звездочки, знак вопроса и​ выберите пункт​​ ошибки. Для нас​​Про тильду не​​ Правка-Заменить; Excel 2007-2010:​​ 0 0 0​ идея — та​ неотличимым от обычного​

6. ​Если не указывать последний​ двумя функциями:​

   • ​ а затем —​ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения])​​Поиск и замена​​или​​ которое требуется найти.​​Для поиска ячеек, содержащих​

     ​по столбцам​​ символы тильда (~)​​Заменить​​ важно, чтобы эта​ знал​ вкладка Главная-Найти и​0 1 0​ же.​ пробела, но с​ аргумент​​ПОДСТАВИТЬ (SUBSTITUTE)​​ клавишу ВВОД. При​Аргументы функции ПОДСТАВИТЬ описаны​

   • ​перейдите на вкладку​Найти все​Выполните одно из указанных​ только символы, введенные​​.​​ можно найти в​.​ статья была вам​ximki-vinki​ выделить-Заменить) — Ctrl+H.​​ 0 0 10​​Если нужно вытащить из​​ другим внутренним кодом​​Номер_вхождения​

     ​и​​ необходимости измените ширину​​ ниже.​​Поиск​​.​​ ниже действий.​

     ​ в поле​Для поиска данных с​ данных листа перед​

7. ​В поле​ полезна. Просим вас​: Нужно заменить символ​выход — просто​​0 1 0​​ ячейки только первые​​ (160 вместо 32).​​, то будут заменены​

Советы

• ​ЗАМЕНИТЬ (REPLACE)​ столбцов, чтобы видеть​Текст​и нажмите кнопку​Примечание:​Чтобы найти текст или​Найти​ конкретными свойствами выберите​ их с тильда​Найти​ уделить пару секунд​ «, на .​ перед служебным символом​​ 0 3 0​​ два слова (например​​ Его не получается​​ все вхождения старого​​. Эти функции во​​ все данные.​    Обязательный. Текст или ссылка​Параметры​​ Если поле​​ числа, нажмите кнопку​​, установите флажок​​ в поле​

• ​ в поле​введите текст или​ и сообщить, помогла​Но при использовании​ необходимо поставить другой​

support.office.com

ПОДСТАВИТЬ (функция ПОДСТАВИТЬ)

​0 1 5​ ФИ из ФИО),​ удалить стандартными средствами​​ текста (в ячейке​​ многом похожи, но​

Описание

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

Синтаксис

​ знак, который сообщает​

​ 0 0 0​ то можно применить​

• ​ — заменой через​​ С1 — обе​ имеют и несколько​Сведения о продажах​ текст, в котором​

• ​ форматирования. Щелкните стрелку​​недоступно, откройте вкладку​

• ​или​​.​вариант​

• ​. Например, чтобы найти​​ искать, или щелкните​ с помощью кнопок​ ЗАМЕНИТЬ, выдает ошибку,​ Excel, что следующий​т.е. третий символ​ формулу:​ диалоговое окно Ctrl+H​ «Маши» заменены на​ принципиальных отличий и​Квартал 1, 2008 г.​ подставляются знаки.​

Пример

​ рядом с полем​Заменить​Найти далее​Если вы хотите найти​формулы​ данные, которые содержат​ стрелку в поле​ внизу страницы. Для​ т.к. используются в​ за ним символ​ стал «1»​У нее простая логика:​ или функцией удаления​

support.office.com

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

​и нажмите кнопку​При необходимости поиск можно​Совет:​ с определенным форматированием,​значения​~?​и нажмите кнопку​ ссылку на оригинал​​Bema​​ свои служебные функции,​​: Вариант формулой в​​ какой-нибудь необычный символ​СЖПРОБЕЛЫ (TRIM)​ определенное вхождение, то​ и на примерах​Описание (результат)​Нов_текст​Очистить​ отменить, нажав клавишу​ При нажатии кнопки​

​ нажмите кнопку​

​или​

​как критерии поиска.​

• ​ последнего поиска в​​ (на английском языке).​: Пример можете показать?​ а выступает в​
• ​ другой столбец Код​​ (например #) функцией​. Поможет наша функция​ его номер задается​
• ​ разберем сначала первую​​Результат​    Обязательный. Текст, на который​
• ​.​​ ESC.​Найти все​Формат​примечания​

​Нажмите кнопку​

• ​ списке.​Функции поиска и замены​​Сергей​​ роли обычного символа.​ =ПОДСТАВИТЬ(C1;»0″;»1″;2)​ПОДСТАВИТЬ (SUBSTITUTE)​ПОДСТАВИТЬ​ в последнем аргументе​ из них.​
• ​=ПОДСТАВИТЬ(A2; «продажах»; «ценах»)​ заменяется «стар_текст».​Для поиска текста или​Чтобы заменить одно или​, каждого экземпляра условия,​и выберите нужные​.​
• ​Параметры​В условиях поиска можно​ в Excel используются​: надо так =ПОДСТАВИТЬ(A1;»»»»;».»)​ Знак этот называется​Вот так наверное​

​ищем позицию символа #​, которой можно заменить​​ (в ячейке С2​​Её синтаксис таков:​

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

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

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

​ например вопросительный знак​ книге необходимой информации,​: Да , все​ — ~.​Класс. работает, спасибо​НАЙТИ (FIND)​ обычный или на​ заменена на «Олю»).​где​Сведения о ценах​

​ фрагмента «стар_текст» нужно​ функции ПОИСК и​ кнопку​ нужное вхождение в​Найти формат​Формулы​ необходимости:​

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

​ (?) и звездочку​ например определенного числа​ работает, спасибо​Таким образом в​ большое​вырезаем все символы от​

​ пустую текстовую строку,​

1. ​Эта функция различает строчные​Ячейка​=ПОДСТАВИТЬ(A3; «1»; «2»; 1)​​ заменить фрагментом «нов_текст».​
2. ​ НАЙТИ.​Заменить​​ списке сделает ячейки​
3. ​.​,​Для поиска данных на​​ (*).​

planetaexcel.ru

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

​ или текстовой строки.​​Юрий М​
​ поле Найти поискового​Как в Excel сделать​
​ начала строки до​ т.е. удалить:​
​ и прописные буквы​- ячейка с​
​Замена первого экземпляра «1»​ Если этот аргумент​
​В этой статье описаны​
​или​ active. Можно сортировать​
​Совет:​значения​
​ листе или во​Звездочка используется для поиска​
​На вкладке​: Можно ведь на​
​ окошка должен получиться​ замену? на любой​
​ позиции # функцией​Если нужно подсчитать количество​

​ (в ячейке С3​​ текстом, где производится​ на «2» («Квартал​ определен, то заменяется​
​ синтаксис формулы и​Заменить все​

​ результаты​ Чтобы найти ячейки, точно​

CyberForum.ru

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

​и​ всей книге выберите​ любой строки знаков.​Главная​ месте менять (без​ такой текст —​

​ другой символ? Если​​ЛЕВСИМВ (LEFT)​ слов в ячейке,​ замена не сработала,​ замена​ 2, 2008 г.»)​ только заданное вхождение​ использование​.​Найти​ соответствующие определенному формату,​примечания​ в поле​ Например, если ввести​
​в группе​ формул): Ctrl+H​ «~?». И не​ просто ввести ?,​abcd84​ то можно применить​ т.к. «маша» написана​Старый_текст​Квартал 2, 2008 г.​ фрагмента «стар_текст». В​ПОДСТАВИТЬ​Microsoft Excel сохраняет параметры​все, щелкнув заголовок​
​ можно удалить все​доступны только на​Искать​г*д​Редактирование​В верхнем поле​ стоит забывать про​ то эксель удаляет​: Есть столбец с:​ простую идею: слов​ с маленькой буквы)​- текст, который​=ПОДСТАВИТЬ(A4; «1»; «2»; 3)​ противном случае все​в Microsoft Excel.​ форматирования, которые можно​

​ столбца.​​ условия в поле​
​ вкладке​вариант​

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

​, то будут найдены​​нажмите кнопку​ ~*​
​ дополнительные параметры замены​ вообще все​0 0 0​ на единицу больше,​Давайте разберем пару примеров​

​ надо найти и​​Замена третьего экземпляра «1»​

​ вхождения фрагмента «стар_текст»​​Подставляет значение аргумента «нов_текст»​

​ определить. Если вы​​Чтобы заменить текст или​Найти​

​Поиск​​на листе​ слова «год» и​Найти и выделить​
​В нижнем точка.​ (они раскрываются при​
​Зибин​

planetaexcel.ru

​ 0 0 0​

Зачистка текста

Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:

• лишние пробелы перед, после или между словами (для красоты!)
• ненужные символы («г.» перед названием города)
• невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или «кривой» выгрузки из 1С, переносы строк, табуляция)
• апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)

Давайте рассмотрим способы избавления от такого «мусора».

Замена

«Старый, но не устаревший» трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.

Изначально это окно было задумано для оптовой замены одного текста на другой по принципу «найди Маша – замени на Петя», но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим «г.» (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы «г.» перед названиями городов:

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

Удаление пробелов

Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:

Удаление непечатаемых символов

В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их «зачистить».

Вариантов решения два:

• Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
• Использовать функцию ПЕЧСИМВ (CLEAN). Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

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

Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE). У нее три обязательных аргумента:

• Текст в котором производим замену
• Старый текст – тот, который заменяем
• Новый текст – тот, на который заменяем

С ее помощью можно легко избавиться от ошибок (замена «а» на «о»), лишних пробелов (замена их на пустую строку «»), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):

Удаление апострофов в начале ячеек

Апостроф (‘) в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:

	 
Sub Apostrophe_Remove() 
   For Each cell In Selection 
      If Not cell.HasFormula Then 
         v = cell.Value 
         cell.Clear 
         cell.Formula = v 
      End If 
    Next 
End Sub

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.

Английские буквы вместо русских

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

Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:

	 
Sub Replace_Latin_to_Russian() 
  Rus = "асекорхуАСЕНКМОРТХ" 
  Eng = "acekopxyACEHKMOPTX" 
  For Each cell In Selection 
    For i = 1 To Len(cell) 
      c1 = Mid(cell, i, 1) 
      If c1 Like "[" & Eng & "]" Then 
         c2 = Mid(Rus, InStr(1, Eng, c1), 1) 
         cell.Value = Replace(cell, c1, c2) 
      End If 
    Next i 
  Next cell 
End Sub

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу

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

• Поиск символов латиницы в русском тексте
• Проверка текста на соответствие заданному шаблону (маске)
• Деление «слипшегося» текста из одного столбца на несколько

Замена символов в Microsoft Excel

Бывают ситуации, когда в документе нужно заменить один символ (или группу символов) на другой. Причин может быть множество, начиная от банальной ошибки, и, заканчивая переделкой шаблона или удалением пробелов. Давайте выясним, как быстро заменить символы в программе Microsoft Excel.

Способы замены символов в Excel

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

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

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

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

Кликаем по кнопке «Найти и выделить», которая располагается во вкладке «Главная» в блоке настроек «Редактирование». В появившемся после этого списке делаем переход по пункту «Заменить».

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

Как видим, в нижней части окна имеются кнопки замены – «Заменить всё» и «Заменить», и кнопки поиска — «Найти всё» и «Найти далее». Жмем на кнопку «Найти далее».

После этого производится поиск по документу искомого слова. По умолчанию, направление поиска производится построчно. Курсор останавливается на первом же результате, который совпал. Для замены содержимого ячейки жмем на кнопку «Заменить».

Можно найти все удовлетворяющие запросу результаты сразу.

1. После ввода поискового запроса и заменяющих символов жмем на кнопку «Найти все».

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

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

Можно выполнить автоматическую замену нажатием всего одной кнопки. Для этого после ввода заменяемых значений, и значений, на которые производится замена, жмем кнопку «Заменить все».

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

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

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

Кроме того, существует возможность расширенного поиска и замены по дополнительным параметрам.

Находясь во вкладке «Заменить», в окне «Найти и заменить» жмем на кнопку Параметры.

Открывается окно дополнительных параметров. Оно практически идентично окну расширенного поиска. Единственное отличие – присутствие блока настроек «Заменить на».

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

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

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

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

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В этом курсе:

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

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

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

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

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

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

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

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

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

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

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

Часто требуется внутри одной ячейки Excel сделать перенос текста на новую строку. То есть переместить текст по строкам внутри одной ячейки как указано на картинке. Если после ввода первой части текста просто нажать на клавишу ENTER, то курсор будет перенесен на следующую строку, но другую ячейку, а нам требуется перенос в этой же ячейке.

Это очень частая задача и решается она очень просто — для переноса текста на новую строку внутри одной ячейки Excel необходимо нажать ALT+ENTER (зажимаете клавишу ALT, затем не отпуская ее, нажимаете клавишу ENTER)

Как перенести текст на новую строку в Excel с помощью формулы

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

Для начала нам необходимо сцепить текст в ячейках A1 и B1 (A1&B1), A2 и B2 (A2&B2), A3 и B3 (A3&B3)

После этого объединим все эти пары, но так же нам необходимо между этими парами поставить символ (код) переноса строки. Есть специальная таблица знаков (таблица есть в конце данной статьи), которые можно вывести в Excel с помощью специальной функции СИМВОЛ(число), где число это число от 1 до 255, определяющее определенный знак.
Например, если прописать =СИМВОЛ(169), то мы получим знак копирайта ©

Нам же требуется знак переноса строки, он соответствует порядковому номеру 10 — это надо запомнить.
Код (символ) переноса строки — 10
Следовательно перенос строки в Excel в виде функции будет выглядеть вот так СИМВОЛ(10)

Примечание: В VBA Excel перенос строки вводится с помощью функции Chr и выглядит как Chr(10)

Итак, в ячейке A6 пропишем формулу

=A1&B1&СИМВОЛ(10)&A2&B2&СИМВОЛ(10)&A3&B3 

В итоге мы должны получить нужный нам результат.
Обратите внимание! Чтобы перенос строки корректно отображался необходимо включить «перенос по строкам» в свойствах ячейки.
Для этого выделите нужную нам ячейку (ячейки), нажмите на правую кнопку мыши и выберите «Формат ячеек…»

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

Как в Excel заменить знак переноса на другой символ и обратно с помощью формулы

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

Рассмотрим на примере, что на картинке выше. Итак, в ячейке B1 прописываем функцию ПОДСТАВИТЬ:

=ПОДСТАВИТЬ(A1;СИМВОЛ(10);» «)

A1 — это наш текст с переносом строки;
СИМВОЛ(10) — это перенос строки (мы рассматривали это чуть выше в данной статье);
» » — это пробел, так как мы меняем перенос строки на пробел

Если нужно проделать обратную операцию — поменять пробел на знак (символ) переноса, то функция будет выглядеть соответственно:

=ПОДСТАВИТЬ(A1;» «;СИМВОЛ(10))

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

Как поменять знак переноса на пробел и обратно в Excel с помощью ПОИСК — ЗАМЕНА

Бывают случаи, когда формулы использовать неудобно и требуется сделать замену быстро. Для этого воспользуемся Поиском и Заменой. Выделяем наш текст и нажимаем CTRL+H, появится следующее окно.

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

После этого в поле «Заменить на» введите пробел или любой другой символ на который вам необходимо поменять и нажмите «Заменить» или «Заменить все».

Кстати, в Word это реализовано более наглядно.

Если вам необходимо поменять символ переноса строки на пробел, то в поле «Найти» вам необходимо указать специальный код «Разрыва строки», который обозначается как ^l
В поле «Заменить на:» необходимо сделать просто пробел и нажать на «Заменить» или «Заменить все».

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

Как поменять перенос строки на пробел или наоборот в Excel с помощью VBA

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

1. Меняем пробелы на переносы в выделенных ячейках с помощью VBA

Sub ПробелыНаПереносы()
For Each cell In Selection
cell.Value = Replace(cell.Value, Chr(32), Chr(10))
Next
End Sub

2. Меняем переносы на пробелы в выделенных ячейках с помощью VBA

Sub ПереносыНаПробелы()
For Each cell In Selection
cell.Value = Replace(cell.Value, Chr(10), Chr(32))
Next
End Sub

Код очень простой Chr(10) — это перенос строки, Chr(32) — это пробел. Если требуется поменять на любой другой символ, то заменяете просто номер кода, соответствующий требуемому символу.

Коды символов для Excel

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