Очистка лишнего форматирования ячеек на листе
Форматирование ячеек на листе позволяет выделить нужные сведения, чтобы их было легко заметить, но при этом форматирование неиспользуемых ячеек (особенно целых строк и столбцов) может привести к быстрому росту размера файла рабочей книги. Если у вас открыт избыточно отформатированный лист, то это может замедлить работу не только Excel, но и снизить общую производительность компьютера. Также из-за лишнего форматирования в Excel могут возникать проблемы при печати.
Используйте команду Clean Excess Cell Formatting (Удалить лишнее форматирование ячеек), которая доступна в Excel на вкладке Inquire (Запрос) в Microsoft Office 365 и Office профессиональный плюс 2013. Если вкладка Inquire (Запрос) в Excel недоступна, вот как можно включить ее:
- Щелкните Файл > Параметры > Надстройки.
- Выберите в списке Управление пункт Надстройки COM и нажмите кнопку Перейти.
- В поле Надстройки COM установите флажок Inquire (Запрос) и нажмите кнопку ОК.
Теперь вкладка Inquire (Запрос) должна отображаться на ленте.
Важно: Перед очисткой лишнего форматирования ячейки мы рекомендуем создать резервную копию файла, так как иногда это может привести к увеличению размера файла, а отменить эти изменения невозможно.
Чтобы удалить лишнее форматирование на текущем листе:
- На вкладке Inquire (Запрос) выберите команду Clean Excess Cell Formatting (Удалить лишнее форматирование ячеек).
- Выберите очистку только открытого листа или очистку всех листов. После выполнения операции очистки нажмите кнопку Да для сохранения изменений на листах или кнопку Нет, чтобы отменить сохранение.
Влияние операции очистки на условное форматирование
При очистке лишнего форматирования из листа удаляются ячейки, расположенные после последней непустой ячейки. Например, если вы применили условное форматирование к целой строке, но ваши данные располагаются только до столбца V, условное форматирование может быть удалено из столбцов, расположенных за столбцом V.
Дополнительные информацию о том, как устранить другие проблемы, вызванные лишним форматированием, см. в статье службы поддержки Майкрософт Сброс последней ячейки в Excel.
Удаляйте текст из ячеек Excel по позиции в ячейке или по типу знаков
Когда вам нужно удалить некий текст из каждой ячейки, вы можете применить стандартный поиск и замену в Excel, или использовать функции или VBA. Но это может мыть сложно и непродуктивно, если вам нужно обработать большой документ и удалить знаки по их позиции в ячейке или по типу знаков.
Инструмент «Удалить знаки» поможет удалить знаки из ячеек за секунды:
Удалить любую текстовую строку из ячеек
Удалить в начале, в конце или в середине всех ячеек
Удалить до или после заданного текста
Удалить все знаки кроме числе и/или букв
Перед началом работы добавьте «Удалить знаки» в Excel
«Удалить знаки» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.
Как удалить заданные знаки из ячеек
Нажмите кнопку Очистка данных на панели XLTools Вы выпадающем списке выберите Удалить знаки Откроется диалоговое окно.
Выберите диапазон, в котором вы хотите добавить знаки.
Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Выберите эти знаки Введите текст, который нужно найти и удалить При необходимости отметьте флажком учитывать регистр .
Нажмите ОК Готово, текст удалён из всех ячеек, где был найден.
Как удалить знаки в начале ячеек
Нажмите кнопку Очистка данных на панели XLTools Вы выпадающем списке выберите Удалить знаки Откроется диалоговое окно.
Выберите диапазон, в котором вы хотите добавить знаки.
Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Выберите позицию в ячейке: первые N знаков Укажите число знаков от начала, которые нужно удалить.
Нажмите ОК Готово, знаки удалены в начале каждой ячейки.
Как удалить знаки в конце ячеек
Нажмите кнопку Очистка данных на панели XLTools Вы выпадающем списке выберите Удалить знаки Откроется диалоговое окно.
Выберите диапазон, в котором вы хотите добавить знаки.
Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Выберите позицию в ячейке: последние N знаков Укажите число знаков от конца, которые нужно удалить.
Нажмите ОК Готово, знаки удалены в конце каждой ячейки.
Как удалить знаки в середине ячеек
Нажмите кнопку Очистка данных на панели XLTools Вы выпадающем списке выберите Удалить знаки Откроется диалоговое окно.
Выберите диапазон, в котором вы хотите добавить знаки.
Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Укажите число знаков в начале, которые нужно пропустить
Укажите число знаков, которые нужно удалить
Нажмите ОК Готово, знаки удалены в середине каждой ячейки.
Как удалить знаки до или после заданного текста в ячейках
Нажмите кнопку Очистка данных на панели XLTools Вы выпадающем списке выберите Удалить знаки Откроется диалоговое окно.
Выберите диапазон, в котором вы хотите добавить знаки.
Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Выберите позицию в ячейке: все что до текста или все что после текста Введите текст, который нужно найти (с учётом регистра).
Нажмите ОК Готово, все знаки до или после заданного текста удалены из тех ячеек, где он был найден.
Как удалить знаки из ячеек по типу знаков
Нажмите кнопку Очистка данных на панели XLTools Вы выпадающем списке выберите Удалить знаки Откроется диалоговое окно.
Выберите диапазон, в котором вы хотите добавить знаки.
Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
числа и буквы
Нажмите ОК Готово, все ненужные типы знаков удалены их ячеек.
Задайте параметры удаления знаков в ячейки
По умолчанию, инструмент «Удалить знаки» не обрабатывает ячейки с формулами, чтобы не повлиять на расчёты. Вы можете задать дополнительные параметры обработки данных:
Отметьте флажком Пропустить нетекстовые ячейки Отметьте эту опцию, если вы хотите обработать только ячейки с текстом. Все ячейки с числовым, денежным форматом, форматом даты или другим форматом будут пропущены.
Отметьте флажком Пропустить строку заголовка Отметьте эту опцию, если вы не хотите обрабатывать первую строку с заголовком таблицы.
Отрезать (удалить) лишний текст слева или справа в ячейке «Excel».
Бывают ситуации, когда необходимо отрезать/ удалить строго определенное количество символов в тексте справа или слева. Например, когда вы имеете список адресов, и вначале каждого адреса стоит шестизначный индекс. Нужно убрать почтовые индексы из списка, но через замену их не уберешь, так как они имеют совершенно разные значения и не находятся опцией замены.
Функции, которая отрезает лишнее количество символов в ячейке, найти не удается, но существует система из двух функций, которые можно применить. Это система из функции ЛЕВСИМВ (или ПРАВСИМВ) и функции ДЛСТР (длина строки).
Рассмотрим их применение.
Вначале находим количество символов в тексте при помощи функции ДЛСТР. Для этого запускаем менеджер функций, выбираем ДЛСТР, указываем ячейку с текстом. Функция ДЛСТР посчитает количество символов.
Зная длину лишнего текста — 6 знаков в индексе, вычитаем их из общего числа символов посчитанных функцией ДЛСТР и получаем то количество символов, которое должно остаться в тексте, когда удалим индекс.
Полученное значение – количество оставляемых в тексте знаков, вписываем в функцию ЛЕВСИМВ или ПРАВСИМВ. Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.
Для уменьшения количества ячеек с расчетами эти функции можно записать в систему следующего вида:
ПРАВСИМВ(…*¹;(ДЛСТР(…*¹)-6)).
Где …*¹- адрес ячейки, из которой берем текст.
Автор Master Of Exc Опубликовано 01.02.2017 03.02.2017 Рубрики Системы функций
Как отрезать (удалить) лишний текст слева или справа в ячейке «Эксель».: 24 комментария
Отличная статья. Помогла отнять лишние знаки из диапазона значений с различной длинной текста.
Например: у меня были значения «100.00» «95.00» 15.00″ «5.00» и т.д.
Решил вопрос формулой: =ЛЕВСИМВ(A1;(ДЛСТР(A1)-3))
Как в ячейке Excel удалить символы справа
Чтобы удалить последние n символов из текстовой строки, вы можете использовать формулу, основанную на функциях ЛЕВСИМВ и ДЛСТР. Вы можете использовать подобную формулу, чтобы удалить последние 3 символа, последние 5 символов начиная справа.
В показанном выше примере в ячейке C5 приведена формула:
=ЗНАЧЕН(ЛЕВСИМВ( B5 ;ДЛСТР( B5 )- 5 ))
которая отсекает «евро» от каждого значения, возвращая только число.
Пояснение
Функция ЛЕВСИМВ идеально подходит для извлечения символов, начинающихся с левой стороны текстовой строки.
Мы используем ЛЕВСИМВ в этой формуле, чтобы извлечь все символы до количества символов, которое мы хотим извлечь.
Проблема для значений переменной длины состоит в том, что мы не знаем точно, сколько символов нужно извлечь.
Вот где используется функция ДЛСТР.
ДЛСТР вычисляет общую длину строки. Для ячейки B5 (5 евро) общая длина равна 6 символов. Чтобы получить количество извлекаемых символов, мы вычитаем 5, что составляет длину » евро», включая пробел. Результатом будет 1, которые передаются в ЛЕВСИМВ как количество символов для извлечения. ЛЕВСИМВ затем возвращает текст «5» как текстовое значение.
Наконец, поскольку нам нужно числовое значение (а не текст), мы пропускаем текст через функцию ЗНАЧЕН, которая преобразует числа в текстовом формате в фактические числа.
Шаги формулы выглядят так: