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 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу

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

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

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

Иногда у пользователя Excel появляется проблема: в начале и конце ячейки показываются ненужные пробелы или появляются непонятные расстояния между словами. Их основная проблема в том, что они нередко незаметны для человека. Поэтому часто он ничего не подозревает, особенно если глаз достаточно замыленный большими объемами данных. Проблема еще в том, что не существует метода обнаружить визуально наличие лишних пробелов, находящихся в конце ячеек при режиме отображения «часть текста». Сегодня мы детально разберемся в том, что можно сделать для того, чтобы убрать все пробелы.

Как убрать все пробелы в конце ячеек при помощи «Найти и заменить»

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

Удаление двойных интервалов

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

1. Сделайте клик по ячейке, в которой нужно убрать ненужные пробелы. Также можно выделить целый диапазон значений.
2. Откройте главное меню. Там есть группа инструментов «Редактирование» и там нужно нажать на кнопку «Найти и выделить». После этого появится небольшая менюшка, в которой нужно выбрать «Заменить». Также возможно использование комбинации горячих клавиш CTRL+H.
3. В появившемся окне нужно ввести в поле «Найти» двойной пробел и заменить его на одинарный.

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

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

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

Удаление всех пробелов в тексте

Может понадобиться и удаление всех пробелов. Если это надо сделать, то нужно выполнить такие шаги:

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

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

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

Как удалить пробелы в ячейках при помощи формулы СЖПРОБЕЛЫ

Также есть специальная функция, предназначенная для того, чтобы убрать ненужные пробелы. Называется она СЖПРОБЕЛЫ. Она дает возможность убрать их за раз как начальные, так и ведущие. Также она способна подставить под нож промежуточные. После нее только один интервал остается, поэтому нужно быть в определенной степени осторожным. Синтаксис этой функции следующий: =СЖПРОБЕЛЫ(A2).

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

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

Как удалить пробелы в начале и в конце ячейки формулами

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

Так как вы поняли из описанного ранее, функция СЖПРОБЕЛЫ не подходит для удаления пробелов лишь в начале и конце строки. Чтобы оставить нетронутыми срединные пробелы, формула будет значительно сложнее и включает в себя сразу несколько операторов, как мы видим из скриншота выше.

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

В свою очередь, оператор ДЛСТР определяет то, насколько большой является определенная строка. После того, как мы ввели эту формулу, проверяем результат. Если пробелы исчезли, то мы все сделали правильно. Только в качестве финального этапа необходимо заменить первоначальный текст на те значения, которые мы получили выше.

Если же нам необходимо только конечные пробелы убрать, то нам надо использовать еще более сложную формулу: =ЛЕВСИМВ(A2;МАКС((ПСТР(A2&ПОВТОР(» «;99);СТРОКА(A2:A100);1)<>» «)*СТРОКА(A2:A100))).

Важно: эта формула должна вводиться в качестве формулы массива. То есть, необходимо использовать комбинацию клавиш Ctrl + Shift + Enter. У нас не очень хорошо удалось выровнять по правому краю значение, находящееся в первой колонке, потому что количество пробелов в конце каждой строки было различным. Во втором столбце мы видим, что у нас получилось решить эту проблему и текст был красиво расположен.

Как удалить разрыв строки и непечатаемые символы

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

Описанная ранее функция СЖПРОБЕЛЫ прекрасно справляется с тем, чтобы убрать из ячейки пробелы, но она не подходит для очистки ее от непечатаемых символов. Фактически она нужна только для того, чтобы убирать символ с кодом 32 в ASCII таблице 7-битного разряда. Именно так кодируется этот символ. Но есть и значения с другими кодами, для которых нужно использовать функцию ПЕЧСИМВ. В английской локализации программы она называется CLEAN. Как мы можем понять из названия, с помощью этого оператора можно очистить ячейку от всевозможного хлама и удалять первые 32 непечатаемые символы.

Допустим, нам нужно убрать и пробелы, и непечатаемые символы в ячейке A2. В этом случае наша задача – использовать сначала формулу ПЕЧСИМВ для того, чтобы убрать непечатные знаки, после чего передать эту строку функции СЖПРОБЕЛЫ, которая убирает оставшиеся пробелы. Результат использования этой комбинации экселевских операторов мы видим на скриншоте.

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

1. Открываем диалоговое окно «Найти и заменить» методом, описанным выше. В поле «Найти» указываем символ возврата каретки. Для этого вводим комбинацию клавиш Ctrl + J. В свою очередь, в поле «Заменить» используем символ пробела. Когда мы нажимаем кнопку «Заменить все» все разрывы автоматически заменяются на пробелы.
2. Также можно заменять возврат каретки на пробелы с помощью этой формулы: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2; СИМВОЛ(13);» «); СИМВОЛ(10); » «)) . Естественно, надо адрес ячейки в функции ПОДСТАВИТЬ заменить на тот, который нужен конкретно в вашей ситуации.

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

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

Очень часто пользователь, который использует функцию СЖПРОБЕЛЫ или ПЕЧСИМВ обнаруживает, что они не срабатывают. Все потому, что есть еще один непечатаемый символ, который обозначает неразрывные пробелы. Очень часто такие символы появляются, когда пользователь пытается вставить информацию из интернета сразу в таблицу Excel.

Для этого необходимо заменить символ неразрывного пробела (он имеет код 160) на пробел с помощью функции ПОДСТАВИТЬ. После этого пробел убирается таким же образом, как и раньше. Если оказывается, что рабочий лист еще и содержит непечатаемые символы, то также необходимо использовать такую формулу: =СЖПРОБЕЛЫ(ПЕЧСИМВ((ПОДСТАВИТЬ(A2;СИМВОЛ(160);» «)))).

Как найти и удалить непечатаемый символ

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

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

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

1. Если непонятный непечатаемый символ, который нам нужно удалить, находится в самом начале, необходимо использовать такую формулу: =КОДСИМВ(ЛЕВСИМВ(A2;1))
2. Если же он располагается в конце строки, формула аналогичная, но используется другой оператор: = КОДСИМВ(ПРАВСИМВ(A2;1))
3. Если же он находится в середине строки, то нам необходимо использовать функцию ПСТР, где в качестве значения n выступает номер знака, где находится проблемный символ.

Комбинации могут быть самыми причудливыми. Например, с помощью этой формулы пользователь может определить нужные коды символов, заменить их на пробелы и сразу же их уничтожить. И все это в одной формуле: =СЖПРОБЕЛЫ(ПЕЧСИМВ((ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;СИМВОЛ(127);» «);СИМВОЛ(160);» «)))).

Преобразование цифр с пробелами в число

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

Как посчитать пробелы при помощи формул

Иногда же пользователю необходимо определить общее количество пробелов в формуле. Для этого нужно выполнить следующую формулу: =ДЛСТР(A3) — ДЛСТР(ПОДСТАВИТЬ(A3;» «;»»)). Если же нам нужно определить, сколько всего ненужных символов в ячейке, необходимо воспользоваться такой формулой: =ДЛСТР(A3)-ДЛСТР(СЖПРОБЕЛЫ(A3)).

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