Как убрать лишние знаки в ячейке excel

Отрезать (удалить) лишний текст слева или справа в ячейке «Excel»

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

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

Рассмотрим их применение.

Вначале находим количество символов в тексте при помощи функции ДЛСТР. Для этого запускаем менеджер функций, выбираем ДЛСТР, указываем ячейку с текстом. Функция ДЛСТР посчитает количество символов.

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

Полученное значение – количество оставляемых в тексте знаков, вписываем в функцию ЛЕВСИМВ или ПРАВСИМВ. Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.

Для уменьшения количества ячеек с расчетами эти функции можно записать в систему следующего вида:

ПРАВСИМВ(…*¹;(ДЛСТР(…*¹)-6)).

Где …*¹- адрес ячейки, из которой берем текст.

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

Зачастую текст, который достается нам для работы в ячейках листа 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

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

Чтобы выполнить задачу по удалению первых 2-3 (или первого одного) слов необходимо воспользоваться системой (сочетанием) из нескольких функций, о которых расскажем ниже:

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

Вторая функция «НАЙТИ» — находит позицию текста и присваивает позиции числовое значение. Например, если замененный нами на символ пробел будет стоять на 12 месте от начала строки, то функция найти примет значение 12.

Третья функция это «ЛЕВСИМВ» или «ПРАВСИМВ» (в зависимости от того будем ли мы удалять слова справа или слева). «ЛЕВСИМВ» или «ПРАВСИМВ» — присваивают ячейке строго определенное количество символов из текста.

Четвертая функция «ДЛСТР» — находит длину строки в ячейке и определяет количество символов в выбранной ячейке.

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

Система выглядит следующим образом:

=ПРАВСИМВ(B3;((ДЛСТР(B3))-(НАЙТИ(«*»;ПОДСТАВИТЬ(B3;» «;»*«;3)))))

В этой системе функция «ПОДСТАВИТЬ» заменяет третий по счету пробел на знак «*», функция «НАЙТИ» находит положение этого знака относительно начала строки, функция «ДЛСТР» считает общее количество знаков, а «ПРАВСИМВ» оставляет нужное количество знаков, справа, удаляя первые знаки ровно по длине «ненужных» слов.

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

Функции выглядят следующим образом:

=ЛЕВСИМВ(B3; НАЙТИ(«*»;ПОДСТАВИТЬ(B3;» «;»*»;3)).

Похожее:

Очищайте ячейки от лишних пробелов, переносов строк и непечатаемых символов

Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

Сколько времени у вас занимает очистка данных перед тем, как можно будет приступить к анализу? Лишние пробелы и непечатаемые символы почти неизбежны при импорте данных в Excel из веб-страниц или сторонних программ. Поиск и удаление вручную или применение функций Excel не продуктивно, если вы работаете с большими таблицами.

Инструмент «Очистить ячейки» поможет очистить массив данных в секунды:

Удалить лишние пробелы в начале, в конце и между словами
Удалить неразрывные пробелы “&nbps”
Удалить переносы строк и другие непечатаемые символы

Перед началом работы добавьте «Очистить ячейки» в Excel

«Очистить ячейки» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel

– пробный период дает 14 дней полного доступа ко всем инструментам.

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

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

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

Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Отметьте флажком пробелы в начале и в конце.
Нажмите ОК Готово, нежелательные пробелы в начале и в конце удалены.

Как удалить все лишние пробелы между словами

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

При удалении лишних пробелов инструмент «Очистить ячейки» одновременно удалит все пробелы в начале и в конце ячеек.

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

Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Отметьте флажком все лишние пробелы.
Нажмите ОК Готово, лишние пробелы сокращены до одиночных пробелов.

Как удалить неразрывные пробелы “&nbps” из ячеек

Неразрывные пробелы “&nbps” являются html символами, которые могут появится в книге Excel при копировании или импорте данных из веб-страниц или сторонних программ. Обычно пробелы “&nbps” выглядят в точности как обычные, но функция =СЖПРОБЕЛЫ в Excel их не распознает и обрабатывает только стандартные пробелы (7-битный код ASCII, значение 32).

Инструмент «Очистить ячейки» удалит неразрывные пробелы “&nbps” (Unicode, значение 160) из всего диапазона.

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

Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Отметьте флажком неразрывные пробелы “&nbps”.
Нажмите ОК Готово, все неразрывные пробелы “&nbps” удалены.

Как убрать переносы строк из ячеек

Перенос строки – это один из непечатаемых знаков, который может появится в таблице Excel при использовании Alt+Enter, копировании или импорте данных из веб-страниц или сторонних программ. Почти аналогично Excel функции =СЖПРОБЕЛЫ, вы можете удалить переносы строк, но сразу и во всем диапазоне.

Инструмент «Очистить ячейки» удалит переносы строк (CR, ASCII код 13) и разрывы строк (LF, ASCII код 10) из всего диапазона.

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

Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Отметьте флажком переносы строк.
Нажмите ОК Готово, все переносы строк удалены.

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

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

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

Инструмент «Очистить ячейки» удалит следующие непечатаемые символы:

Непечатаемые символы в 7-битном коде ASCII (занчения от 1 до 31)
Непечатаемые символы в Unicode (значения 127, 129, 141, 143, 144, 157, 160)

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

Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Отметьте флажком непечатаемые символы.
Нажмите ОК Готово, все непечатаемые символы удалены.

Excel - как убрать пробелы?

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

Как удалить ненужные пробелы?

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

Переходим в новую ячейку - в нашем случае это будет В2 - и вводим формулу: = УДАЛИТЬ.ТРЕБУЕТСЯ.ДОСТУП(А2), затем нажимаем Вводим . A2 в скобках, очевидно, является ячейкой с ненужными пробелами.

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

Как убрать пробелы из чисел?

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

Выделите диапазон ячеек, из которых мы хотим удалить пробелы и используйте сочетание клавиш Ctrl+H .

В открывшемся окне Найти и заменить введите один пробел в поле Найти и оставьте поле Заменить на: пустым. Затем нажимаем кнопку Заменить все .
Таким образом, все пробелы в выбранной области были удалены (технически преобразованы в полное отсутствие символов).

Вам нравится эта статья? Поделись с другими!

.90 000 Десять лучших способов очистки данных

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

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

Основные этапы очистки данных следующие:

Импорт данных из внешнего источника данных.
Сделайте копию исходных данных в отдельной книге.
Убедитесь, что данные представлены в табличном формате со строками и столбцами, где: каждый столбец содержит похожие данные, все столбцы и строки видны, и в отображаемом диапазоне нет пустых строк.Для достижения наилучших результатов используйте таблицу Excel.
Сначала выполните задачи, не требующие изменения столбца, например проверку орфографии или задачу с использованием диалогового окна «Найти и заменить» .
Затем выполните задачи, требующие модификации столбца. Вот общие инструкции по изменению столбцов:
1. Вставьте новую колонку (B) рядом с исходной колонкой (A), которую необходимо очистить.
2. В верхней части нового столбца (B) добавьте формулу, которая будет преобразовывать данные.
3. Заполните новый столбец (B) формулой. В таблице Excel будет автоматически создан вычисляемый столбец с заполненными значениями.
4. Выберите новый столбец (B), скопируйте его, а затем вставьте как значения в новый столбец (B).
5. Удалить исходный столбец (A). Это преобразует новый столбец B в столбец A.

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

REMOVE.REQUIRED Функция интервала

В этой статье описаны синтаксис формулы и использование функции интервала REMOVE.REQUIRED в Microsoft Excel.

Описание

Удаляет все пробелы из текста, кроме одиночных пробелов между словами. Функцию УДАЛИТЬ НЕОБХОДИМЫЕ ИНТЕРФЕЙСЫ следует использовать с текстом, полученным из другого приложения, в котором могут быть неправильные пробелы.

Важно: Функция DELETE.НЕОБХОДИМО.SPACING используется для удаления 7-битного символа пробела ASCII (значение 32) из текста. В наборе символов Unicode есть дополнительный пробел, так называемый неразрывный пробел, с десятичным значением 160. Этот символ часто используется на веб-страницах в качестве единицы HTML, & nbsp; . Сама по себе функция УДАЛИТЬ НЕОБХОДИМЫЕ ПРОБЕЛЫ не удаляет символ неразрывного пробела. Пример того, как обрезать оба символа пробела из текста, см. в разделе Десять лучших способов очистки данных.

Синтаксис

УДАЛИТЬ НЕОБХОДИМЫЕ ПРОБЕЛЫ (текст)

Синтаксис функции УДАЛИТЬ ИНТЕРФЕЙС имеет следующие аргументы:

Пример

Скопируйте образец данных из приведенной ниже таблицы и вставьте его в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД.При необходимости вы можете настроить ширину столбцов для отображения полных данных.

Формула	Описание	Результат
= УДАЛИТЬ.REQUIRED.ROADS ("Прибыль в первом квартале")	Удаляет первый и конечные пробелы из текста в формуле (прибыль за первый квартал).	Прибыль в первом квартале

🥇 Удалите пробелы и повреждения строки из

ячеек

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

Проверьте это.

Используйте функцию TRIM Microsoft Excel для удаления лишних пробелов

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

 = ОТДЕЛКА (текст)

Используйте функцию ПОДСТАВИТЬ Microsoft Excel для удаления специальных символов

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

Синтаксис для ПОДСТАВИТЬ:

 = ПОДСТАВИТЬ (текст, старый_текст, новый_текст, [номер_экземпляра])

Ты понял?

Но, Джек, как мне поставить пробел в формуле?

Я рад, что вы спросили. Просто введите "".

Нравится:

 = ЗАМЕНИТЬ (B2, "", "")

В этой функции вы ничем не заменяете пробел.Хороший.

Напишите что-нибудь действительно странное, в качестве разрыва строки вы должны использовать CHAR(). Это позволяет выбрать определенный символ, который нельзя ввести в формулу. Номер разрыва строки — 10. Итак, сделайте так:

 = ЗАМЕНИТЬ (B2, СИМВОЛ (10), "")

Необязательный аргумент [instance_num], который может удалить, скажем, только первое или второе вхождение старого текста. Например:

 = ЗАМЕНА (B2, СИМВОЛ (10), "", 1)

Вы также можете оставить функции ПОДСТАВИТЬ.Например, если вы хотите разобрать специальные символы из нескольких телефонных номеров:

 = ЗАМЕНИТЬ (ЗАМЕНИТЬ (ЗАМЕНИТЬ (ЗАМЕНИТЬ (ЗАМЕНИТЬ (B5, СИМВОЛ (40), ""), СИМВОЛ (41), ""), СИМВОЛ (45), ""), СИМВОЛ (32), ""), СИМВОЛ (46), "")

CHAR() и CODE() Код ANSI/ASCII

Хитрость в ПОДСТАВКЕ() состоит в том, чтобы запомнить каждый отдельный номер для вызова СИМВОЛ(). Это заняло у меня весь день, но в конце концов я запомнил код каждого символа ANSI.

Шучу. Я даже не помню, сколько мне лет, не говоря уже о том, какой код ANSI для пробела или знака @. К счастью, вам не нужно. Вы можете распечатать эту диаграмму из MSDN или использовать функцию CODE () Excel для поиска кодов символов на ходу.

 = КОД (текст)

Думайте о CODE(), а не о CHAR().

Применение

Вот ваш совет по Excel на сегодня. Удачного тюнинга и изменений!

Как удалить лишние символы в Excel?

Удаление символов всего за несколько шагов

В Microsoft Office Excel раздражающие пробелы или другие лишние символы снова и снова проникают в документ после импорта файлов из других источников. Они выглядят некрасиво. Но гораздо хуже то, что они мешают, когда вы хотите продолжить работу с импортированными данными. Во многих случаях счета-фактуры больше не могут быть выставлены должным образом, и появляются сообщения об ошибках. Поэтому импортированный документ необходимо обработать с помощью Excel ОЧИСТКА, СГЛАЖИВАНИЕ и, при необходимости, ИЗМЕНЕНИЕ.

Избыточные и непечатаемые символы в Excel

Excel — отличный инструмент для обработки импортированных данных. Это могут быть, например, данные финансового учета или производственные требования заказчика. Однако при импорте данных нежелательные пробелы или непечатаемые символы могут снова и снова появляться. Это нормально, и во многих случаях этого нельзя избежать. Поэтому стоит разобраться с функциями CLEAN, SMOOTH и CHANGE, чтобы можно было использовать их при необходимости.Импортированные тексты очищаются, а затем удаляются ненужные и непечатаемые символы. Эти функции быстро и легко использовать. Даже неспециалисты могут легко правильно редактировать документ Excel.

Быстро и легко удаляйте ненужные символы из Excel с помощью CLEAN and SMOOTH

Основная процедура ОЧИСТКИ и разглаживания работает следующим образом:

Щелкните ячейку, столбец или область таблицы, куда вы хотите вставить очищенное или сглаженное содержимое.
Запустите функцию в Excel, введя «= CLEAN» или «= SMOOTH».
Теперь выделите текстовый раздел, ячейку или столбец, которые вы хотите очистить или сгладить. Например, = ЧИСТЫЙ (a6) или = ГЛАДКИЙ (b9).
Следующим шагом является подтверждение ввода нажатием клавиши Enter.

Очищенный или сглаженный текст теперь отображается в выбранном поле. Вы также можете использовать комбинацию двух табличных функций SMOOTH и CLEAN для очистки «ошибок» при импорте данных за один раз.Это делается путем ввода:

Пример: удалить ненужные символы из таблицы Excel

В показанной рабочей книге следующая формула в ячейке B4 содержит «Müller Huber»: = SMOOTH (CLEAR (A4))

Во-первых, четыре непечатаемых символа, представленные фреймом, были удалены из табличной функции CLEAN. Затем GLĘTTEN освободил полученный текст от всех лишних мест в колонке.

С помощью функции импорта вы переносите импортированное содержимое, которое хотите исправить, в формулу.

Что делает функция CLEAN?

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

Примечание для профессиональных пользователей CLEAN

CLEAN удаляет первые 32 символа из 7-битного кода ASCII. Эти символы нельзя распечатать и, следовательно, нельзя отобразить в Excel. Некоторые символы Юникода также не распознаются и не отображаются Excel.

Что делает функция SMOOTH?

Функция SMOOTH удаляет лишние и ненужные пробелы из выделенного фрагмента текста. Более одного пробела между двумя словами считается избыточным. Пробелы в начале или в конце текста также считаются лишними и удаляются GLĘTTEN.

Примечание для профессиональных пользователей GLĘTTEN

SMOOTH удаляет только пробел ASCII (число 32) из текстов. Есть пробелы из других кодов, таких как Unicode, которые SMOOTH не распознает и не удаляет.

Функция CHANGE помогает при особых проблемах

Однако иногда в импортируемых данных встречаются символы, которые не обрабатываются функциями. Это потому, что они вообще не являются специальными символами. В этом вам поможет функция CHANGE. Благодаря этой функции вы обмениваете одного персонажа на другого. Например, подумайте о данных, полученных в другом формате, в котором вместо десятичной точки используется десятичная точка. Это распространено, например, в США. Вы можете преобразовать точку в запятую, используя соответствующую функцию Excel, и сразу продолжить расчет со значениями.

Для этого используйте функцию ИЗМЕНЕНИЕ. Вы передаете текст табличной функции, символ, который нужно заменить, и символ, который будет использоваться в качестве замены. Чтобы заменить точку запятой в содержимом ячейки A1, используйте следующую формулу:

= ИЗМЕНЕНИЕ (A1, "."; ",") * 1

Можно ли избежать ненужных символов в Excel?

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

Часто задаваемые вопросы

Откуда в Excel берутся лишние символы?

Создаются лишние символы при импорте данных из других программ, таких как бухгалтерия, бухгалтерия или логистика. Они часто используют другие коды (Unicode, 7-битный ASCII и т. д.), которые содержат символы, которые Excel не может правильно отображать.

Какую формулу можно использовать для удаления ненужных символов в Excel?

Формула Excel CLEAN удаляет все непечатаемые символы из файлов.Затем вы можете использовать функцию SMOOTH для удаления ненужных пробелов из текста. Также можно комбинировать обе функции (= SMOOTH (CLEAN (импорт данных)) для одновременного редактирования разделов текста.

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

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

Я не могу удалить ненужные символы в Excel, почему?

Прежде всего, рекомендуется повторить процесс и проверить правильность применения всех шагов и формул. Если вы все равно не можете удалить лишние символы, возможно, они пришли из кодов (например, пробел из Юникода). Их нельзя удалить с помощью соответствующих функций. Excel обрабатывает только символы ASCII.

Вы поможете в развитии сайта, поделившись сайтом с друзьями

КАК: Узнайте, как удалить лишние пробелы из Excel с помощью TRIM

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

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

Гораздо быстрее использовать горячую клавишу CTRL-H для поиска в проблемном столбце и быстрого удаления лишних пробелов.

Как работает TRIM

Синтаксис функции TRIM следующий:

= TRIM (текст)

Текст в этом случае относится к данным с внешним интервалом. Аргумент может быть:

Фактический текст заключен в кавычки
Ячейка, которая ссылается на расположение текстовых данных на листе

Многие считают, что TRIM удаляет пробелы только в начале и в конце текста.По сути, это позволит избавиться от всего лишнего пространства.

Например, если текст в ячейке A1:

Это очень плохое предложение.

Excel = TRIM (A1) даст следующий результат:

Это очень плохое предложение.

Использование TRIM

Чтобы удалить лишние пробелы с помощью функции TRIM, выполните следующую процедуру:

Щелкните ячейку A6, чтобы сделать ее активной, здесь будет функционировать

Тип = ОТДЕЛКА (A4)

нажмите Введите

Текст из формата A4 с дополнительными пробелами появится в формате A6, но все лишние пробелы будут удалены.

На данном этапе проблема заключается в том, что на листе существуют форматы A4 и A6, когда вы хотите, чтобы обычный текст (в формате A6) заменял грязный текст (в формате A4).

Заменить содержимое A6 на A4 очень просто:

Щелкните правой кнопкой мыши A6 .

Набрать Копировать

Щелкните правой кнопкой мыши A4 .

Select Специальная паста

Выберите Значения и затем нажмите хорошо .

Удалить текст из A6 .

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

Если вы используете Excel Online, пропустите шаги 4 и 5 выше. Вместо этого щелкните стрелку вниз под Вставить на ленте Главная, а затем выберите Вставить значения .

Удалить лишние пробелы из всего столбца

Описанный выше метод отлично работает, если вы очищаете пробелы только в одной ячейке. Что, если вы хотите удалить пробелы во всем столбце?

Процедура обработки столбца такая же, как и обработка одной ячейки.В этом примере столбец со слишком большим количеством пробелов начинается с A2.

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

Щелкните верхнюю пустую ячейку в новом столбце (B2).

Введите = ОБРЕЗАТЬ (B2) и нажмите Enter.

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

Двойной щелчок правой кнопкой мыши для автоматического заполнения всего столбца

Теперь у вас будет столбец A с дополнительными пробелами и столбец B с таким же подчищенным текстом.

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

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

Использование ярлыка для поиска и замены

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

Выберите весь столбец, который вы хотите очистить

Нажмите CTRL-H , чтобы открыть окно поиска и замены в Excel

в Найдите каждое поле , введите два пробела

на Замените поле , введите один пробел в

Нажмите Замените все , пока не исчезнут все лишние пробелы

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

Метод удаления пустых пространств в Excel зависит от исходных данных и пространства.

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

Excel - как убрать пробелы | Блог Excel

Хотя Excel представляет собой электронную таблицу, в нем есть много текстовых функций. Это позволяет использовать его не только для анализа числовых данных. Одной из функций, позволяющих мгновенно улучшить текст, содержащийся в ячейках, является функция УДАЛИТЬ НЕОБХОДИМО , которая сократит количество пробелов в тексте до необходимого минимума.

Эта функция используется, когда кто-то получает данные из других программ и импортирует их в Excel.Часто оказывается, что в данных слишком большой интервал между словами. После использования УДАЛИТЬ НЕОБХОДИМЫЕ ПРОБЕЛЫ конечным результатом будет только один пробел между словами, независимо от того, сколько пробелов было в начале. Если в начале или в конце текста появляется пробел, он также будет удален этой функцией.

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

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

На фото этого не видно, но в поле Find просто пробел , а в поле изменится на ń в ничто. После использования кнопки заменить все получаем последовательность цифр, без лишних пробелов.

Blender уроки

Категории