Как удалить повторяющиеся символы в ячейках Excel

Обновлено: 21.11.2024

В этом посте рассказывается, как удалить повторяющиеся символы из текстовой строки в одной ячейке Excel. Как удалить повторяющиеся слова из ячейки с помощью пользовательской функции в Excel 2013/2016.

Удалить повторяющиеся символы

Предполагается, что у вас есть список данных в диапазоне B1:B2, в котором содержатся некоторые значения тестовой строки. И вы хотите удалить все повторяющиеся символы из текстовой строки в одной ячейке. Например, текстовая строка имеет вид aabbcc, удаляя повторяющиеся символы, чтобы получить последнюю строку как abc. Как это сделать. Вы можете использовать определяемую пользователем функцию для достижения результата в Excel. Выполните следующие действия:

=removeDupes(B1)

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

Удалить повторяющиеся слова

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

=NoDupWords(B1",",")

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

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

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

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

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

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

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

В этом посте рассказывается, как фильтровать данные с помощью формулы в Excel. Как фильтровать ячейки с формулами с помощью определяемой пользователем функции в Excel 2013/2016. Формула фильтра Предположим, что у вас есть список данных.

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

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

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

Что касается удаления повторяющихся значений или строк, Microsoft Excel предлагает множество различных вариантов. Но когда дело доходит до удаления идентичного текста в данной ячейке, Excel… ничего не дает. Никаких инструментов, никаких функций, никаких формул, ничего. Помешает ли это нам достичь нашей цели? Ни в коем случае. Если в Excel нет нужной нам функции, давайте напишем свою :)

Как удалить повторяющиеся слова в ячейке Excel

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

Решение: пользовательская функция или макрос VBA.

Определяемая пользователем функция для удаления дубликатов в ячейке

Чтобы удалить повторяющийся текст в ячейке, вы можете использовать следующую определяемую пользователем функцию (UDF) с именем RemoveDupeWords:

Как вставить код функции в книгу

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

Нажмите клавиши ALT + F11, чтобы открыть редактор Visual Basic.
На левой панели щелкните правой кнопкой мыши Эта книга и выберите Вставить >Модуль.
Вставьте приведенный выше код в окно Код.

Дополнительную информацию см. в разделе Как вставить код VBA в Excel.

Синтаксис функции RemoveDupeWords

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

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

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

Как использовать функцию RemoveDupeWords

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

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

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

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

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

Если ваши исходные данные разделены пробелом, второй аргумент должен быть " " или опущен:

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

Макрос VBA для одновременного удаления повторяющегося текста из нескольких ячеек

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

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

В приведенном выше коде разделителем является запятая и пробел. Чтобы использовать другой разделитель, замените "," другим символом(ами) в этой строке кода:

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

Как использовать макрос

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

Подробнее см. в разделе Как запустить макрос в Excel.

Примечание. Поскольку действие макроса нельзя отменить, мы настоятельно рекомендуем сохранить книгу непосредственно перед использованием макроса. Таким образом, если что-то пойдет не так, вы можете просто закрыть и снова открыть книгу, и вы вернетесь к тому же месту, где и были. Или вы можете просто сделать копию рабочего листа (листов), на который может повлиять макрос.

Как удалить повторяющиеся символы в ячейке

Проблема: у вас есть несколько вхождений одного и того же символа в ячейке, в то время как каждая ячейка должна содержать только одно вхождение данного символа.

Решение: пользовательская функция или макрос VBA.

Определяемая пользователем функция для удаления повторяющихся символов

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

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

Синтаксис функции RemoveDupeChars

Синтаксис этой пользовательской функции настолько прост, насколько это возможно — требуется только один аргумент:

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

Эта функция чувствительна к регистру и обрабатывает строчные и прописные буквы как разные символы.

Как использовать функцию RemoveDupeChars

Все, что мы сказали об использовании RemoveDupeWords, верно и для RemoveDupeChars. Итак, не вдаваясь в теорию, давайте сразу перейдем к примеру.

Чтобы удалить повторяющиеся символы из столбца A, начиная с A2, введите эту формулу в B2 и скопируйте ее вниз:

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

Совет. Если ваши символы отделены друг от друга каким-либо разделителем, например пробелом, запятой или дефисом, используйте функцию RemoveDupeWords, как показано в предыдущем примере.

Макрос VBA для удаления одинаковых символов из ячейки

Как и RemoveDupeWords, функцию RemoveDupeChars также можно вызывать из макроса:

Поскольку в этой пользовательской функции не используются разделители, вам не придется вносить какие-либо изменения в код.

Примечание. Для работы макроса его код и код UDF RemoveDupeChars должны быть размещены в одном модуле в редакторе VBA.

Как использовать макрос

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

Выберите диапазон ячеек, из которых вы хотите удалить повторяющиеся символы.
Нажмите клавиши ALT + F8, чтобы открыть диалоговое окно Макрос.
В списке макросов выберите RemoveDupeChars2.
Нажмите Выполнить.

Удаление повторяющихся подстрок с помощью Ultimate Suite

В начале этого руководства упоминалось, что Microsoft Excel не имеет встроенной функции удаления дубликатов в ячейке. Но наш Ultimate Suite делает это!

Его можно найти в раскрывающемся меню Удаление дубликатов на вкладке Данные Ablebits в группе Дедупликация. Если параметр «Удалить повторяющиеся подстроки» не отображается в Excel, убедитесь, что у вас установлена последняя версия Ultimate Suite (бесплатную пробную версию можно скачать здесь).

Чтобы удалить повторяющиеся слова или текст из нескольких ячеек за 5 секунд (по секунде на шаг :), вам нужно сделать следующее:

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

Готово!Не нужно возиться с VBA или формулами, только быстрые и точные результаты.

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

Вот как удалить повторяющийся текст в ячейке. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Зарегистрированный пользователь Регистрация Дата 07-03-2012 Location md MS-Off Ver Excel 2010 Сообщений 37

Удалить повторяющиеся символы из текстовой строки в ячейке

У меня есть таблица Excel со следующими данными. Мне нужна помощь в уменьшении ";" до 1. если есть только 1 точка с запятой, оставьте ее как есть.
Это кажется простым, но я просто не могу его решить.
Любая помощь будет принята с благодарностью.

Уважаемый участник форума Дата регистрации 10 09 2012 Местоположение Лос-Анджелес, Калифорния MS-Off Ver Excel 2010 Сообщений 929

Выполните НАЙТИ и ЗАМЕНИТЕ (ctrl-H).

Найти что ;;
Заменить на ;

Заменять все, пока не исчезнут все дубликаты.

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

Зарегистрированный пользователь Регистрация Дата 07-03-2012 Location md MS-Off Ver Excel 2010 Сообщений 37

Я пытался использовать формулу, но вам пришлось использовать формулу несколько раз.
=SUBSTITUTE(C1,";;",";")
затем скопируйте это несколько раз во все на себя.

Это было не очень элегантно, поэтому я просто попробовал найти и заменить.

Гуру форума Дата регистрации 25 марта 2011 г. Местоположение Колорадо, США MS-Off Ver Excel 2003–2013 Сообщений 5 361

Надеюсь, это поможет,
~tigeravatar

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

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

Гуру форума Дата регистрации 25 марта 2011 г. Местоположение Колорадо, США MS-Off Ver Excel 2003–2013 Сообщений 5 361

Единственное, что я могу добавить к этому: почему вы (Sam99) в конечном итоге получаете строки в ячейках с большим количеством точек с запятой? Я предполагаю, что вы должны использовать формулу или VBA для объединения множества других данных. Если это так, то вы можете рассмотреть возможность изменения исходного кода/формулы, которая создает эти строки, чтобы она добавляла точку с запятой только в том случае, если находит данные для конкатенации. Таким образом, вам не нужно беспокоиться о формуле, которая удаляет лишнее.

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

Мы покажем, как удалять дубликаты в Excel с помощью набора данных Sports. Этот набор данных содержит информацию об олимпийских медалистах за 2012 год.

Использование параметра «Удалить дубликаты» на вкладке «Данные»

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

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

Затем найдите параметр "Удалить дубликаты" и выберите его.

Вкладка «ДАННЫЕ» → раздел «Инструменты данных» → «Удалить дубликаты»

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

Если ваши данные состоят из заголовков столбцов, выберите параметр «Мои данные содержат заголовки», а затем нажмите «ОК».

Последипломная программа по бизнес-анализу

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

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

Как вы заметили, повторяющиеся записи удаляются.

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

Использование расширенного фильтра

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

Сначала нажмите ячейку или диапазон в наборе данных, из которого вы хотите удалить дубликаты. Если вы нажмете на одну ячейку, Excel автоматически определит диапазон, когда вы нажмете «Расширенный фильтр».

Вкладка «ДАННЫЕ» → раздел «Сортировка и фильтр» → «Дополнительно» и нажмите на нее.

Вы увидите диалоговое окно. Он состоит из списка параметров расширенной фильтрации.

Выберите параметр "Копировать в другое место", чтобы скопировать уникальные значения в другое место.
Проверьте диапазон ваших записей в поле "Диапазон списка" и убедитесь, что это именно тот диапазон, который вы указали.
В поле "Копировать в:" введите диапазон, в который должны быть скопированы результирующие уникальные значения.
Установите флажок "Только уникальные записи". Этот шаг является самым важным.
Нажмите "ОК".

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

Бесплатный курс: основы бизнес-анализа

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

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

Этот метод включает объединение столбцов с помощью формулы Excel и определение количества. Затем мы отфильтруем повторяющиеся значения (те, у которых количество больше 1).

Объединим столбцы A, B и C с помощью оператора конкатенации «&». Таким образом, формула Excel будет выглядеть так:

Эта формула вводится в ячейку D2, а затем копируется во все строки.

Теперь нам понадобится еще один столбец с именем «Количество», чтобы найти дубликаты в столбце D. Поэтому мы используем функцию СЧЁТЕСЛИ в ячейке E2. Формула будет такой:

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

Если значение Count равно "1", то оно появилось только один раз и является уникальным. Если значение равно «2» или больше, оно считается повторяющимся значением.

Теперь добавьте фильтр в столбец "Число", выбрав параметр "Фильтр".

Вы найдете его на вкладке «ДАННЫЕ» → раздел «Сортировка и фильтр» → «Фильтр»

Нажмите на фильтр в верхней части столбца E. Выберите «1», чтобы оставить только уникальные значения и удалить дубликаты.

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

Давайте рассмотрим последний способ удаления дубликатов: использование Power Query.

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

Power Query в Excel позволяет импортировать данные из различных источников, очищать и преобразовывать данные. Этот инструмент упрощает удаление дубликатов в Excel.

Выберите ячейку или диапазон, перейдите на вкладку «Данные» → раздел «Получить и преобразовать данные» → «Из таблицы/диапазона» и нажмите на нее.

При нажатии вы увидите диалоговое окно для создания таблицы запросов мощности. Убедитесь, что диапазон значений указан правильно. Нажмите "ОК".

Появится следующее окно редактора Power Query.

Магистерская программа бизнес-аналитика

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

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

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

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

При выборе параметра «Закрыть и загрузить» данные будут загружены в вашу электронную таблицу.

Подготовьтесь к роли бизнес-аналитика в программе последипломного образования по бизнес-анализу. Зарегистрируйтесь сейчас!

Заключение

В этой статье мы узнали о различных подходах к удалению повторяющихся записей. Если вы заинтересованы в изучении основ Excel или хотите развить более продвинутые навыки работы с Microsoft Excel, Simplilearn предлагает вам сертификационный курс по бизнес-аналитике с Excel. Удаление дубликатов в Excel — очень утомительная задача, и мы надеемся, что эта статья помогла вам научиться эффективно удалять дубликаты.

Пожалуйста, не стесняйтесь задавать любые вопросы в разделе комментариев к статье «Как удалить дубликаты в Excel?: пошаговое руководство». Наши специалисты свяжутся с вами в ближайшее время.

Об авторе

Простое обучение

Simplilearn — один из ведущих мировых поставщиков онлайн-обучения по цифровому маркетингу, облачным вычислениям, управлению проектами, науке о данных, ИТ, разработке программного обеспечения и многим другим новым технологиям.

Читайте также: