Как убрать скобки в excel
Зачастую текст, который достается нам для работы в ячейках листа 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) и введите туда его текст:
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.
Английские буквы вместо русских
Это уже, как правило, чисто человеческий фактор. При вводе текстовых данных в ячейку вместо русских букв случайно вводятся похожие английские ("це" вместо русской "эс", "игрек" вместо русской "у" и т.д.) Причем снаружи все прилично, ибо начертание у этих символов иногда абсолютно одинаковое, но Excel воспринимает их, конечно же, как разные значения и выдает ошибки в формулах, дубликаты в фильтрах и т.д.
Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)
Для удаления всех скобок из текстовых строк в выбранном диапазоне мы предлагаем вам три метода.
Удаление скобок из строки с помощью функции поиска и замены
Освободи Себя Найти и заменить функция может заменить все скобки пробелами в выбранном диапазоне.
1. Выберите диапазон скобок, который необходимо удалить, а затем нажмите кнопку Ctrl + F клавиши одновременно, чтобы открыть Найти и заменить диалоговое окно.
2. в Найти и заменить диалогового окна, перейдите к Заменять вкладку, введите левую часть скобок в Найти то, что коробка, держи Заменить пустое поле, а затем щелкните значок Заменить все кнопка. Смотрите скриншот:
3. В следующем всплывающем диалоговом окне щелкните значок OK кнопку.
4. Не закрывайте Найти и заменить диалоговом окне и повторите шаги 2–3, указанные выше, чтобы удалить оставшуюся правую часть скобок «]». И, наконец, закройте диалоговое окно «Найти и заменить».
Легко удалить все скобки или указанные символы из ячеек в Excel:
Kutools for Excel's Удалить символы Утилита поможет вам легко удалить все скобки или указанные символы из выбранных ячеек в Excel, как показано в демонстрации ниже. Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная трасса)
Удалите скобки из строки с помощью пользовательской функции
Вы можете удалить скобки из ячейки с помощью пользовательской функции. Пожалуйста, сделайте следующее.
1. Нажмите другой + F11 клавиши одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.
2. в Приложение Microsoft Visual Basic окна, нажмите Вставить > Модуль. Затем скопируйте и вставьте приведенный ниже код VBA в окно модуля.
Код VBA: удалить скобки из строки
3. Нажмите другой + Q одновременно нажмите клавиши, чтобы закрыть окно Microsoft Visual Basic для приложения.
4. Выберите пустую ячейку (говорит ячейка B2) для поиска результата после удаления скобок, введите формулу = Без скобок (A2) в панель формул, а затем нажмите Enter ключ.
Внимание: A2 - ячейка содержит скобки, которые необходимо удалить.
5. Продолжайте выбирать ячейку B2, затем перетащите маркер заполнения в ячейку для применения этой формулы.
Удалите все скобки из строки в диапазоне с помощью Kutools for Excel
Y вы можете легко удалить все скобки из текстовых строк в выбранном диапазоне сразу с помощью Удалить символы полезности Kutools for Excel. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон скобок, который необходимо удалить, затем нажмите Kutools > Текст > Удалить символы. Смотрите скриншот:
2. в Удалить символы диалоговое окно, проверьте На заказ поле и введите скобки в текстовое поле, затем щелкните значок OK кнопку.
Теперь все скобки в текстовых строках в выбранном диапазоне немедленно удаляются.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Если у вас есть несколько данных, и некоторые из них заключены в квадратные или круглые скобки, теперь вы хотите удалить текст в скобках, как показано на скриншотах ниже, как вы можете быстро решить эту проблему?
Удалить текст в круглых скобках на нескольких листах с помощью Kutools for Excel
Удалите скобки с помощью Kutools for Excel
Удалите текст в круглых скобках с помощью функции поиска и замены
Чтобы удалить текст, заключенный в скобки, вы можете найти их, а затем заменить их пустыми.
1. Нажмите Ctrl + H ключи для включения Найти и заменить диалог, в Найти то, что текстовое поле, тип (*) в него и перейдите к Заменить текстовое поле, тип () внутрь. Смотрите скриншот:
2. Затем нажмите Заменить все. Затем появляется диалоговое окно с напоминанием о количестве замен. Нажмите OK закрыть его.
Удалить текст в круглых скобках на нескольких листах с помощью Kutools for Excel
Если вы открыли несколько книг и вам нужно удалить весь текст в скобках в открытых книгах, вы можете использовать Kutools for Excelпродвинутый Найти и заменить для поиска и замены текста в книгах.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Kutools > Навигация включить Навигация панель, затем щелкните Найти и заменить значок для перехода Найти и заменить раздел. Смотрите скриншоты:
3. Затем выберите Заменять и в Найти то, что текстовое поле, тип (*) в него и перейдите к Заменить текстовое поле, тип () в него и выберите Все книги под В раскрывающийся список, см. снимок экрана:
Наконечник: Если вы хотите заменить на определенных листах, вы можете проверить имена листов в Книги .
4. Нажмите Заменить все. Затем все тексты в скобках заменяются пустыми.
Работы С Нами Навигация панели, вы можете перечислить все имена открытых листов, имена диапазонов и добавить автоматический текст по своему усмотрению. Щелкните здесь, чтобы узнать больше о навигации.
Удалить содержимое в круглых скобках
Удалите скобки с помощью Kutools for Excel
Если вы хотите удалить скобки или скобки, вы можете использовать Kutools for ExcelАвтора Удалить символы.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон данных, для которого нужно удалить скобки, и нажмите Kutools > Текст > Удалить символы. Смотрите скриншот:
2. в Удалить символы диалог, только проверка На заказ вариант, а затем введите () в текстовое поле, вы можете просмотреть результат в предварительный просмотр панель. Смотрите скриншот:
3. Нажмите Ok or Применить. Теперь все скобки выделения сняты.
Про то, как можно быстро склеивать текст из нескольких ячеек в одну и, наоборот, разбирать длинную текстовую строку на составляющие я уже писал. Теперь же давайте рассмотрим близкую, но чуть более сложную задачу - как склеивать текст из нескольких ячеек при выполнении определенного заданного условия.
Допустим, что у нас имеется база данных по клиентам, где одному названию компании может соответствовать несколько разных email'ов ее сотрудников. Наша задача состоит в том, чтобы собрать все адреса по названиям компаний и сцепить их (через запятую или точку с запятой), чтобы сделать потом, например, почтовую рассылку по клиентам, т.е. получить на выходе что-то похожее на:
Другими словами, нам нужен инструмент, который будет склеивать (сцеплять) текст по условию - аналог функции СУММЕСЛИ (SUMIF) , но для текста.
Способ 0. Формулой
Не очень изящный, зато самый простой способ. Можно написать несложную формулу, которая будет проверять отличается ли компания в очередной строке от предыдущей. Если не отличается, то приклеиваем через запятую очередной адрес. Если отличается, то "сбрасываем" накопленное, начиная заново:
Минусы такого подхода очевидны: из всех ячеек полученного дополнительного столбца нам нужны только последние по каждой компании (желтые). Если список большой, то чтобы их быстро отобрать придется добавить еще один столбец, использующий функцию ДЛСТР (LEN) , проверяющий длину накопленных строк:
Теперь можно отфильтровать единички и скопировать нужные склейки адресов для дальнейшего использования.
Способ 1. Макрофункция склейки по одному условию
Если исходный список не отсортирован по компаниям, то приведенная выше простая формула не работает, но можно легко выкрутиться с помощью небольшой пользовательской функции на VBA. Откройте редактор Visual Basic нажатием на сочетание клавиш Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) . В открывшемся окне вставьте новый пустой модуль через меню Insert - Module и скопируйте туда текст нашей функции:
Если теперь вернуться в Microsoft Excel, то в списке функций (кнопка fx в строке формул или вкладка Формулы - Вставить функцию) можно будет найти нашу функцию MergeIf в категории Определенные пользователем (User Defined) . Аргументы у функции следующие:
Способ 2. Сцепить текст по неточному условию
Если заменить в 13-й строчке нашего макроса первый знак = на оператор приблизительного совпадения Like, то можно будет осуществлять склейку по неточному совпадению исходных данных с критерием отбора. Например, если название компании может быть записано в разных вариантах, то мы можем одной функцией проверить и собрать их все:
Поддерживаются стандартные спецсимволы подстановки:
По умолчанию оператор Like регистрочувствительный, т.е. понимает, например, "Орион" и "оРиОн" как разные компании. Чтобы не учитывать регистр можно добавить в самое начало модуля в редакторе Visual Basic строчку Option Compare Text, которая переключит Like в режим, когда он невосприимчив к регистру.
Таким образом можно составлять весьма сложные маски для проверки условий, например:
Способ 3. Макрофункция склейки текста по двум условиям
В работе может встретиться задача, когда сцеплять текст нужно больше, чем по одному условию. Например представим, что в нашей предыдущей таблице добавился еще один столбец с городом и склеивание нужно проводить не только для заданной компании, но еще и для заданного города. В этом случае нашу функцию придется немного модернизировать, добавив к ней проверку еще одного диапазона:
Применяться она будет совершенно аналогично - только аргументов теперь нужно указывать больше:
Способ 4. Группировка и склейка в Power Query
Решить проблему можно и без программирования на VBA, если использовать бесплатную надстройку Power Query. Для Excel 2010-2013 ее можно скачать здесь, а в Excel 2016 она уже встроена по умолчанию. Последовательность действий будет следующей:
Power Query не умеет работать с обычными таблицами, поэтому первым шагом превратим нашу таблицу в "умную". Для этого ее нужно выделить и нажать сочетание Ctrl + T или выбрать на вкладке Главная - Форматировать как таблицу (Home - Format as Table) . На появившейся затем вкладке Конструктор (Design) можно задать имя таблицы (я оставил стандартное Таблица1):
Теперь загрузим нашу таблицу в надстройку Power Query. Для этого на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013) жмем Из таблицы (Data - From Table) :
В открывшемся окне редактора запросов выделяем щелчком по заголовку столбец Компания и сверху жмем кнопку Группировать (Group By) . Вводим имя нового столбца и тип операции в группировке - Все строки (All Rows) :
Жмем ОК и получаем для каждой компании мини-таблицу сгруппированных значений. Содержимое таблиц хорошо видно, если щелкать левой кнопкой мыши в белый фон ячеек (не в текст!) в получившемся столбце:
Теперь добавим еще один столбец, где с помощью функции склеим через запятую содержимое столбцов Адрес в каждой из мини-таблиц. Для этого на вкладке Добавить столбец жмем Пользовательский столбец (Add column - Custom column) и в появившемся окне вводим имя нового столбца и формулу сцепки на встроенном в Power Query языке М:
Обратите внимание, что все М-функции регистрочувствительные (в отличие от Excel). После нажатия на ОК получаем новый столбец со склееными адресами:
Осталось удалить ненужный уже столбец ТаблАдресов (правой кнопкой мыши по заголовку - Удалить столбец) и выгрузить результаты на лист, нажав на вкладке Главная - Закрыть и загрузить (Home - Close and load) :
Важный нюанс : в отличие от предыдущих способов (функций), таблицы из Power Query не обновляются автоматически. Если в будущем произойдут какие-либо изменения в исходных данных, то нужно будет щелкнуть правой кнопкой в любое место таблицы результатов и выбрать команду Обновить (Refresh) .
Читайте также: