Как в excel убрать лишние символы в ячейке

Очистка лишнего форматирования ячеек на листе

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

Используйте команду Clean Excess Cell Formatting (Удалить лишнее форматирование ячеек), которая доступна в Excel на вкладке Inquire (Запрос) в Microsoft Office 365 и Office профессиональный плюс 2013. Если вкладка Inquire (Запрос) в Excel недоступна, вот как можно включить ее:

Управление надстройками COM

Щелкните Файл > Параметры > Надстройки.
Выберите в списке Управление пункт Надстройки COM и нажмите кнопку Перейти.
В поле Надстройки COM установите флажок Inquire (Запрос) и нажмите кнопку ОК.
Теперь вкладка Inquire (Запрос) должна отображаться на ленте.

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

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

Clean Excess Cell Formatting (Удалить лишнее форматирование ячеек

На вкладке Inquire (Запрос) выберите команду Clean Excess Cell Formatting (Удалить лишнее форматирование ячеек).
Выберите очистку только открытого листа или очистку всех листов. После выполнения операции очистки нажмите кнопку Да для сохранения изменений на листах или кнопку Нет, чтобы отменить сохранение.

Влияние операции очистки на условное форматирование

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

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

Excel works!

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

символ в ячейке Excel

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

1. Найти и заменить символ в ячейке Excel

Если вкратце, то существует специальный инструмент во всех продуктах MS Office. Вызывается клавишами Ctrl+F (найти) или Ctrl+H (заменить). В первое поле можно вписать символ, который нужно удалить, во второе ничего не вписывайте.

Подробнее об этом инструменте написано в этой статье

2. Удалить символ в ячейке Excel формулой

Удалить любой символ можно прекрасной функцией =ПОДСТАВИТЬ().

Эта функция имеет следующую конструкцию =ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)

текст — здесь указывается ячейка, где записан текст для изменения

старый_текст — что меняем

новый_текст — на что меняем

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

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

=ПОДСТАВИТЬ(A1;".";",")

3. =СЖПРОБЕЛЫ()

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

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

Но в 95% случаев сжпробелы помогает на «отлично».

Удаление пробелов я даже вынес в отдельную статью

4. Удалить первый символ

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

Очень подробно об этом написано здесь .

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

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

=ПСТР(A:A;ПОИСК(".";A:A)+2;ДЛСТР(A:A)-ПОИСК(".";A:A))

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

5. Функция =ПЕЧСИМВ()

Функция с одним аргументом очень похожа на СЖПРОБЕЛЫ, но она удаляет непечатные символы в строке, такие как перенос строки, знаки абзаца, какие-нибудь квадратики и т.п. (первые 32 непечатаемых знаков в 7-разрядном коде ASCII). Особенно полезна она, если нужно удалить перенос строки — мне всегда помогает.

удалить перенос строки

Внимание! ПЕЧСИМВ способна удалить большинство, но не все лишние символы.

Удаляйте текст из ячеек Excel по позиции в ячейке или по типу знаков

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

Инструмент «Удалить знаки» поможет удалить знаки из ячеек за секунды:

Удалить любую текстовую строку из ячеек
Удалить в начале, в конце или в середине всех ячеек
Удалить до или после заданного текста
Удалить все знаки кроме числе и/или букв

Перед началом работы добавьте «Удалить знаки» в Excel

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

Начните работу с инструментами XLTools

Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.

Как удалить заданные знаки из ячеек

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

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

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

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

Нажмите ОК Готово, текст удалён из всех ячеек, где был найден.

Результат: указанные знаки удалены из каждой ячейки

Как удалить знаки в начале ячеек

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

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

Нажмите ОК Готово, знаки удалены в начале каждой ячейки.

Результат: знаки удалены в начале каждой ячейки

Как удалить знаки в конце ячеек

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

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

Нажмите ОК Готово, знаки удалены в конце каждой ячейки.

Результат: знаки удалены в конце каждой ячейки

Как удалить знаки в середине ячеек

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

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

Нажмите ОК Готово, знаки удалены в середине каждой ячейки.

Результат: знаки удалены в середине каждой ячейки

Как удалить знаки до или после заданного текста в ячейках

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

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

Нажмите ОК Готово, все знаки до или после заданного текста удалены из тех ячеек, где он был найден.

Результат: все знаки до или после заданного текста удалены

Как удалить знаки из ячеек по типу знаков

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

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

Нажмите ОК Готово, все ненужные типы знаков удалены их ячеек.

Результат: все знаки, кроме чисел и букв, удалены из ячеек

Задайте параметры удаления знаков в ячейки

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

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

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