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

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

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

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

Подмена

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

Вначале это окно было задумано для оптовой подмены 1-го текста на иной по принципу «найди Маша – поменяй на Петя», но мы его, в этом случае, можем применять его и для удаления излишнего текста. К примеру, в первую строчку вводим «г.» (без кавычек!), а во вторую не вводим ничего и нажимаем клавишу Поменять все (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) и введите туда его текст:

Сейчас, если выделить на листе спектр и запустить наш макрос (Alt+F8 либо вкладка Разраб – клавиша Макросы), то апострофы перед содержимым выделенных ячеек пропадут.

Английские буковкы заместо российских

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

Интересно почитать: В excel склеить ячейки

Можно, естественно, вручную подменять знаки латинцы на подобающую им кириллицу, но еще резвее будет создать это при помощи макроса. Откройте редактор Visual Basic сочетанием кнопок Alt+F11, вставьте новейший модуль (меню Insert — Module) и введите туда его текст:

Сейчас, если выделить на листе спектр и запустить наш макрос (Alt+F8 либо вкладка Разраб – клавиша Макросы), то все английские буковкы, отысканные в выделенных ячейках, будут изменены на равноценные им российские. Лишь будьте аккуратны, чтоб не поменять случаем подходящую для вас латиницу 🙂

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

При работе с листами Microsoft Excel для вас может потребоваться удалить 1-ые несколько знаков, крайние несколько знаков либо и то, и другое из текста. Удаление первых нескольких знаков из столбца текстов полезно, когда для вас необходимо удалить заглавия (к примеру, доктор, лейтенант). Буквально так же удаление крайних нескольких знаков быть может полезно при удалении телефонных номеров опосля имен. В данной для нас статье пробелы числятся знаками.

Удалить текст до либо опосля определенного знака в Excel

Этот пост покажет для вас, как удалить 1-ые либо крайние несколько знаков либо определенные знаки позиции из текста в Microsoft Excel. Мы разглядим последующие темы:

Удалите 1-ые несколько знаков из столбца текстов
Удалите крайние несколько знаков из столбца текстов
Удалите как 1-ые несколько, так и крайние несколько знаков из столбца текстов.

Удалите 1-ые несколько знаков из столбца текстов

Синтаксис для удаления первых нескольких знаков из столбца текстов:

Где — размещение первой ячейки в столбце с полными текстами. — это количество знаков, которое вы собираетесь удалить из левой части текста.

К примеру. Если у нас есть столбец с полным текстом от ячейки A3 до A7 и нам нужен текст опосля удаления первых 2 знаков в столбце C, формула будет последующей:

Запишите эту формулу в ячейку C3. Нажмите Enter, и он покажет текст в ячейке A3 без первых 2-ух знаков в ячейке C3. Щелкните в любом месте за пределами ячейки C3, а потом вернитесь в ячейку C3, чтоб выделить параметр «Заливка». Сейчас перетащите формулу в ячейку C7. Это даст тексты без первых 2-ух знаков в столбце C для начальных текстов в столбцах A.

Удалите крайние несколько знаков из столбца текстов

Синтаксис для удаления крайних нескольких знаков из столбца текстов:

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

К примеру. Давайте разглядим вариант, когда у нас есть столбец с полными текстами от ячейки A3 до A7, и нам нужен текст опосля удаления крайних 9 знаков в столбце D; формула будет таковой:

Сейчас запишите эту формулу в ячейку D3. Нажмите Enter, и он покажет текст в ячейке A3 без крайних 9 знаков в ячейке D3. Щелкните в любом месте за пределами ячейки D3, а потом вернитесь в ячейку D3, чтоб выделить параметр «Заливка». Сейчас перетащите формулу в ячейку D7. Это даст тексты без крайних 9 знаков в столбце D для начальных текстов в столбцах A.

Удалите как 1-ые несколько, так и крайние несколько знаков из столбца текстов

Если вы собираетесь удалить как 1-ые несколько, так и крайние несколько знаков из столбца текстов, синтаксис формулы будет последующим:

К примеру. Если у нас есть столбец с полными текстами в столбце A от ячейки A3 до A7 и нам необходимы тексты без первых 2 знаков и крайних 9 знаков в столбце E от ячейки E3 до E7, формула будет иметь последующий вид:

Запишите эту формулу в ячейку E3. Нажмите Enter, и он покажет текст в ячейке A3 без первых 2 и крайних 9 знаков в ячейке E3. Щелкните в любом месте за пределами ячейки E3, а потом вернитесь в ячейку E3, чтоб выделить параметр Fill. Сейчас перетащите формулу в ячейку E7. Это даст тексты без первых 2 и крайних 9 знаков в столбце E для начальных текстов в столбцах A.

Я надеюсь, что этот пост поможет для вас удалить 1-ые либо крайние несколько знаков либо определенные знаки позиции из текста в Microsoft Excel.

Удаление ячеек в Excel

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

Как удаляются ячейки

Ячейки бывают 2-ух типов: содержащие данные и пустые. Зависимо от этого различается и сам процесс их удаления, т.к. в программке предусмотрена возможность автоматизации процесса выделения и следующего удаления пустых ячеек.

Интересно почитать: В excel в ячейке создать список

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

Самый обычной метод удалить ячейки

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

Выделяем элементы, которые подлежат удалению и кликаем правой клавишей мыши по хоть какому месту выделенной области. В показавшемся перечне избираем пункт “Удалить…”.
На дисплее отобразится соответственное окно с 4-мя опциями. Так как нам нужно избавиться конкретно от ячеек, а не от целых строк либо столбцов, избираем одно из 2-ух действий – удаление ячеек со сдвигом на лево или со сдвигом ввысь. Выбор определенного деяния зависит от задач, которые стоят перед юзером. Опосля того, как мы обусловились с необходимым вариантом, подтверждаем действие, нажав клавишу OK.
Как мы и желали, все избранные ячейки были удалены из таблицы. Мы избрали 2-ой вариант (сдвиг ввысь), потому группа ячеек, находящаяся под выделенной областью сместилась ввысь ровно на столько строк, сколько было в выделенном нами спектре.

Удаление ячеек при помощи клавиш на ленте

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

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

Отмечаем нужный спектр частей, перебегаем во вкладку “Основная” и жмем клавишу “Удалить”.
Итог этого метода ничем не различается от вышеперечисленного. Опосля выполнения операции, выделенная группа частей пропадет, а ячейки снизу от выделенного спектра сдвинутся ввысь.

В случае удаления спектра частей по вертикали, примыкающие ячейки сместятся в другом направлении. Давайте разглядим это на примере.

Отмечаем вертикальный спектр ячеек, и жмем по уже отлично знакомой кнопочке “Удалить” на ленте.
Опосля окончания процедуры, отмеченные ячейки удалились со сдвигом оставшихся справа в левую сторону.

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

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

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

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

Выделяем область ячеек для удаления. Но сейчас, в разделе “Ячейки” на ленте кликаем не по самой кнопочке «Удалить», а по треугольнику, расположенному справа от нее. В раскрывшемся перечне щелкаем по пт “Удалить ячейки…”.
Мы увидим знакомое окно с вариациями удаления и смещения. Избираем тот, который соответствует нашим целям и нажимаем клавишу OK, чтоб узреть итог. Допустим, пусть это будет сдвиг ввысь.
Операция удаления прошла удачно, и смещение вышло конкретно ввысь.

Композиция кнопок для удаления ячеек

Сочетания кнопок разрешают оперативно выполнить почти все операции в Эксель, и процедура удаления ячеек – не исключение (смотрите также на нашем веб-сайте статью “Жаркие клавиши в Excel“).

Как обычно, все начинается с выделения подходящей области данных. Опосля этого жмем на клавиатуре комбинацию кнопок “Ctrl” и “-“.
Запустится обычное нам окно с опциями удаления ячеек. Определяемся с хотимым направлением сдвига и подтверждаем операцию.
Отмеченные ячейки удалились из книжки со сдвигом в ранее обозначенном направлении.

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

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

Отмечаем 1-ый элемент обычным способом. Дальше, зажав кнопку “Ctrl” по очереди выделяем все оставшиеся нужные ячейки или спектры (вне зависимости от того, на каком расстоянии они находятся друг от друга).
Опосля того, как мы удачно совладали с первым шагом, приступаем к самому удалению, которое можно выполнить ранее рассмотренными методами.

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

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

Как постоянно, сначала нам будет нужно выделение. Но сейчас мы отмечаем всю таблицу либо ее часть, где планируется выполнить удаление. Опосля этого жмем многофункциональную кнопку “F5”.
В запустившемся окне жмем клавишу «Выделить…» в левом нижнем углу.
На дисплее отобразится окно с параметрами выделения групп ячеек. Устанавливаем отметку напротив функции «Пустые ячейки» и подтверждаем действие нажатием клавиши OK.
Выполнив прошлые деяния нам удалось отметить все пустые ячейки в выделенной таблице.
Сейчас остаётся только удалить эти элементы одним из способов, рассмотренных выше.

Заключение

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

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

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

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

Как удалить числа из текста?

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

Как удалить буковкы из текста?

Чтоб удалить буковкы из текста, нужно в диалоговом окне надстройки избрать пункт «Любые буковкы», при всем этом в тексте ячеек числа и знаки остаются, а все буковкы, независимо от того российские они либо английские, удаляются. Предвидено удаление из текста лишь латиницы (букв латинского алфавита) либо лишь кириллицы (букв российского алфавита).

Как удалить излишние знаки?

Для удаления излишних знаков, нужно избрать пункт «Лишь знаки», при всем этом все знаки не считая цифр и букв удаляются, но имеется возможность задать символы-исключения, к примеру, чтоб все слова не соединились в одну строчку, можно установить флаг в поле «также знаки:» и вписать пробел. В поле для символов-исключений можно вписать хоть какой иной знак, или группу знаков, вереницей без пробелов либо остальных разделителей.

Как удалить лишь данные знаки?

В случае, если нужно удалить из значений ячеек отдельные знаки из различных групп, предусмотрена возможность перечислить все знаки, подлежащие удалению через точку с запятой. Для удаления самого знака «;» (точка с запятой) можно применять опцию удаления по ASCII-кодам. К примеру, символу «;» соответствует ASCII-код «59». Эта функция будет также полезна по мере необходимости удаления непечатаемых знаков, к примеру знака перевода на новейшую строчку.

Выборочное удаление знаков из ячеек Excel

Надстройка дозволяет юзерам в избранном спектре ячеек производить выборочное удаление из значений:

2) цифр и всех знаков (не считая букв);

3) всех букв, как российского, так и британского алфавита;

4) всех букв и символов;

5) всех букв и цифр;

6) лишь символов (всех, не считая букв и цифр);

7) лишь букв кириллицы;

8) букв кириллицы и символов;

9) лишь букв латиницы;

10) букв латинского алфавита и всех символов;

11) знаков, данных вручную или по ASCII-кодам.

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