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

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

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

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

Замена

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

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

clean-text1.png

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

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

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

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

clean-text2.png

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

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

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

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

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

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

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

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

clean-text3.png

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

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

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

 


28.05.2014 14:21:28
Николай, огромное спасибо за ваш труд! По моему мнению лучший ресурс по Excel в рунете. Хотел добавить по поводу удаления апострофов в начале ячеек - есть отличная родная функция на вкладке данные - "текст по столбцам", выделив необходимый столбец данных можно моментально избавиться от апострофов.
09.06.2014 12:43:44
Кстати, да - тоже отличный и быстрый способ. Спасибо, Николай!
13.08.2014 17:00:24
Удалить лишние пробелы без создания дополнительных столбцов (зачастую он лишний будет, обычно при работе с большими объёмами данных нужно чтоб вообще нигде не было  лишних пробелов) очень просто через заменить, в графе Найти ставим 2 пробела, в графе Заменить на ставим 1 пробел, всё что лишнее убирается.
14.08.2014 21:26:06
А если один лишний пробел перед или после текста?
15.08.2014 07:56:11
Мне очень часто приходится собирать различные данные, пробелы перед или после текста встречаются крайне редко, а вот посредине текста по два пробела, это стандартно (зачем это делают, непонятно). В случае пробелов до или после текста, приходится конечно что то думать, обычно подсвечиваю через условное форматирование (у Вас же и подсмотрел:) ) и вручную убираю, повторюсь, такое крайне редко встречается. А мудрить дополнительные столбцы (раньше так делал) при больших объёмах данных, потом работать с этими данными... и так приходится рабочие столбцы вводить.
15.11.2014 23:26:04
А как поменять русские буквы на латинские?
17.11.2014 00:22:45
Спасибо.
Я переделал последний макрос:
Sub Replace_Russian_to_Latin()
Eng = "thbmacekopxyACEHKMOPTXBY"
    Rus = "тнвмасекорхуАСЕНКМОРТХВУ"
      
    For Each cell In Selection
        For i = 1 To Len(cell)
            c1 = Mid(cell, i, 1)
            If c1 Like "[" & Rus & "]" Then
                c2 = Mid(Eng, InStr(1, Rus, c1), 1)
                cell.Value = Replace(cell, c1, c2)
            End If
        Next i
    Next cell
End Sub
18.03.2015 22:51:38
Всех приветствую! Присоединяюсь к благодарностям в адрес Николая П. Я Vba изучаю как пару месяцев и на эту тему накатал такой макрос... как Вам?!
Public Function Печатн(str1 As String)
Dim str, s, s1, s2 As String
Dim i, j, k, n As Long
           For i = 1 To Len(str1)
           s = Mid(str1, i, 1)
               If LCase(s) Like "[a-zа-я0-9,.!?/\|*'# )(+-_&%;:^$]" Or LCase(s) Like Chr(34) Then
               str = str & s
               End If
           Next i
Печатн = str

End Function

А еще (но не знаю почему)апострофы удаляет функция =текст(A1;0) ))))
24.03.2015 21:58:11
Доброго времени суток Николай. Будьте добры подсказать, как протянули формулу в столбце с верху вниз до конца без мышки. Какими клавишами пользовались?
Заранее благодарю за помощь.
Денис  
10.08.2016 16:40:02
Надо нажать кнопку "заполнить вниз", она выглядит на ленте голубая в белом квадрате, предварительно встав на 2ю ячейку и спустившись вниз при помощи комбинаций кнопок ctrl + shift + стрелка курсора вниз ;)
24.02.2016 22:28:56
Добрый день!
Подскажите как использовать функцию ПЕЧСИМВ на всем листе что бы не обрабатывать каждый столбик отдельно.
В таблице много столбиков с текстом в каждой ячейке с знаками которые нужно убрать этой функцией.  
16.03.2017 14:39:21
Фарид, такое только макросом - например http://www.planetaexcel.ru/plex/features/16/253/
08.08.2016 12:34:49
Подскажите пожалуйста! такая ситуация. есть таблица эксель, со списком. в каждой ячейке есть текст, пример:
абвгд@еёжз
как можно подчистить значения ячеек, таким образом, чтобы удалились все символы начиная от @ и до конца текста ячейки. т.е. нужно удалить @еёжз
Помогите пожалуйста с такой задачей:

Имеем текст:
Мастер стиральной машины gorenje!

Надо его привести к такому виду:
мастер-стиральной-ма

т. е. удалить знаки препинания (!?,.), заменить пробелы на тире и обрезать справа, чтобы получилось не больше 20-ти символов.
16.03.2017 14:38:36
=ЛЕВСИМВ(ЗАМЕНИТЬ(A1;" ";"-");20)
26.12.2016 00:38:20
Спасибо
16.03.2017 12:51:20
Здравствуйте!
Тема: Удаление апострофов в начале ячеек
Подскажите что делать в таком случае:
Я вставил новый модуль (меню 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

Но после выползает окно с ошибкой
ссылка на скрин:
http://joxi.ru/52aJkkbs40OqjA
http://joxi.ru/1A5RzzdunwN7pr
В итоге макрос не устанавливается.
Буду очень благодарен за подсказку.
16.03.2017 14:37:17
Сергей, код макроса нельзя валить в одну строку - разнесите на разные клавишей Enter (как я исправил в вашем посте) - и все будет ОК.
16.03.2017 16:17:17
Спасибо,Павел, супер!
Буду пробовать.
Дай бог вам сил и здоровья.
31.05.2018 00:47:28
в основном Всегда Николая с фамилией Павлов - можно называть Павел))));) хоть умудрились бы имя узнать УЧИТЕЛЯ) Он в ответ Вас кстати Гришой не назвал!
21.03.2017 15:40:37
Здравствуйте, Павел.
Вот незадача:
Я скопировал макрос, применил его, но в этот раз вот такая проблема http://joxi.ru/Rmzvqq1f0G5Eem
Данный макрос я давно использовал и он работа, но сейчас не получается.

Подскажите пожалуйста в чем может быть проблема.
22.04.2017 10:13:39
Сергей, скачайте лучше файл примера, приложенный к статье - там работающий макрос "в живую". Оттуда можно взять код.
22.03.2017 16:21:48
Добрый день!
А подскажите, пожалуйста, если у нас массив данных по типу
Диван кожаный PF-QW12345(F)
грубо говоря, список моделей, которые копируются из базы 1С, а для документа необходимо оставлять только саму модель:
PF-QW12345(F)

можно ли как-то макросом убрать ненужные слова?
22.04.2017 10:14:50
Тут надо импровизировать. Если названия на русском, то можно попробовать удалить все символы кириллицы и почистить потом лишние пробелы функцией СЖПРОБЕЛЫ.
10.05.2017 12:44:27
Скажите пожалуйста, а как сделать аналогичный макрос, который будет удалять в ячейке часть текста, цвет шрифта которой черный, а синий шрифт будет оставлять в ячейке?

То есть нужен макрос оставляющий в ячейке только тот текст, что выделен синим шрифтом.

Спасибо!
Здравствуйте. Тут уже был вопрос, ответ на который помог бы мне:
Давид Варосян 08.08.2016 12:34:49
Подскажите пожалуйста! такая ситуация. есть таблица эксель, со списком. в каждой ячейке есть текст, пример:
абвгд@еёжз
как можно подчистить значения ячеек, таким образом, чтобы удалились все символы начиная от @ и до конца текста ячейки. т.е. нужно удалить @еёжз
Если можете помочь, подскажите, пожалуйста. У меня нет даже предположений, что нужно делать.
15.07.2017 20:59:59
Можно формулами. Будет что-то типа:
=ЛЕВСИМВ(A1;НАЙТИ("@";A1)-1)
где A1 - ячейка с текстом.

Можно готовым макросом из моей надстройки PLEX (см. вторую картинку, предпоследний переключатель "Удалить все до символов")

Можно просто набрать в соседнем справа столбце пару-тройку ячеек с правильными ответами и нажать на вкладке "Данные - Мгновенное заполнение"
Спасибо большое! Решение нашлось!
01.08.2017 18:28:15
Подскажите, пожалуйста, как сделать, что бы ячейки после ввода проверялись на наличие символов №, +, «, », и некоторые другие? Если такие символы приствтуют, то вываливалось сообщение об ошибке. На ячейках столбца настроена проверка данных на максимальную длину текста. Можно ли использовать несколько критериев при данном способе?
07.11.2017 14:32:06
как быть в случае, если нужно удалить все кроме сочетания букв к примеру "отс"? это сочетание букв нужно оставить. заранее благодарю.
17.08.2019 05:00:10
Более полный перечень для замены

Rus = "асекорхуАВСЕНКМОРТУХь"
Eng = "acekopxyABCEHKMOPTYXb"
28.01.2020 12:12:20
Здравствуйте, Николай
Подскажите пожалуйста, как можно удалить сразу несколько символов из строки с помощью функции SUBSTITUTE.
Например мне нужно удалить пробел, "/" и "-"  из текста  "125 a/55-86",  т.е. оставить только буквы и цифры.
02.10.2020 10:12:12
Ещё как вариант, делаем одну ячейку "правильной", потом нажимаем Формат по образцу и выделяем весь столбец или строку с "неправильными" ячейками.
18.10.2021 17:51:39
Здравствуйте Николай! Трудно подобрать слова чтобы выразить Вам переполняющую меня благодарность за Ваши замечательные ролики! Самое главное что после их просмотра ощущаешь не только удовлетворение от полученных новых знаний, но и поразительный душевный подъём, который достигает пика на словах "Когда знаешь - всё просто!" :) Случай этот для меня крайне редкий, если не сказать уникальный.

Принимая во внимание Ваши слова о том что Вы являетесь тренером по продуктам MS Office (а значит не только по Excel), обращаюсь к Вам с покорнейшей просьбой: не могли бы Вы создать макрос, который подобно приведенному в данном разделе менял бы символы латиницы на похожие символы кириллицы в выделенном фрагменте текста MS Word? Был бы Вам благодарен всей душой, поскольку мне весьма часто приходится сталкиваться с этой проблемой, а программировать в MS Word я не умею. Большое спасибо заранее и низкий поклон за Ваш труд!
14.03.2023 09:59:27
Код макроса удаляющий апостроф у меня некорректно отрабатывал в случае если апостроф стоял перед формулой, как я понял проблема в локализации Ofice. В итоге переделал код для такого случая, может кому пригодиться.

Sub Apostrophe_Remove()
   For Each cell In Selection
      If Not cell.HasFormula Then
         v = cell.Value
         cell.Clear
         cell.FormulaLocal = v
      End If
    Next
End Sub
Так же в надстройке Plex очень не хватает функции вставки апострофа перед формулой, точнее она как бы есть в обработке текста, но при попытке использовать её на формулах пишет, что в выделенном диапазоне нет текста. Такая функция есть в надстройке Usap, но очень хочется чтобы появилась и в Plex именно для того что бы формулы превращать в текст. Я использовал для замены ссылки на таблицы в формулах. У меня получился код ниже, может кому пригодиться.

Sub ConvertFormulasToText()
    Dim inputRange As Range
    Set inputRange = Application.InputBox("Select a Range:", "Convert Formulas to Text", , , , , , 8)
    
    If inputRange Is Nothing Then Exit Sub
    inputRange.NumberFormat = "@"
    
For Each cell In inputRange.Cells
    cell.NumberFormat = "@"
    cell.Value = "'" & cell.FormulaLocal
Next cell
    
    inputRange.Select
End Sub 
29.03.2023 07:36:08
Столкнулся с тем, что из-за функции ПОДСТАВИТЬ(), превысил длину  разрешённую длину написания формулы.
Может есть безмакросный вариант, указав не символ в одной функции, а список символов?
Спасибо.
25.10.2023 08:15:36
Добрый день.
(01)08594200831431(21)5eV(gh(a"dagn
(01)08594200831639(21)5AWfK3c!Pt(ha
(01)08594200831639(21)5EJisjutN,Dc'
Есть такие коды, но в них присутствуют лишние знаки, а именно скобки вокруг цифр 01 и 21
Как их можно удалить.
Пробовала через заменить, но тогда удаляются все скобки, а это не правильно.
Помогите пожалуйста
Наверх