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

Процедуры из статьи доступны в !SEMTools Lite и Pro.

Повторяющиеся строки внутри ячейки Excel — частая беда склеенных описаний и характеристик: «Самовывоз сегодня» дважды в одной ячейке, «Гарантия 2 года» в каждой третьей. В Excel 365 их удаляет формула =ОБЪЕДИНИТЬ(СИМВОЛ(10);ИСТИНА;УНИК(ТЕКСТРАЗД(A2;;СИМВОЛ(10)))). В любой версии и сразу для всего диапазона — две процедуры !SEMTools: повторы в каждой ячейке и повторы во всём выделении.

Здесь речь о строках текста внутри одной ячейки, разделённых переносом. Если нужно убрать одинаковые ячейки в столбце, смотрите, как удалить дубликаты в Excel, а повторы слов внутри фразы — удалить повторяющиеся слова.

Откуда в ячейке повторяющиеся строки

Повторы почти всегда — результат склейки. Excel тут ни при чём, виноват процесс:

• Сцепили через перенос несколько полей, а в двух из них было одно и то же («Самовывоз сегодня» и в доставке, и в оплате).
• Собрали значения нескольких строк в одну ячейку быстрой сводной или ОБЪЕДИНИТЬ, и одинаковые позиции продублировались.
• Поставщик прислал карточки, где характеристики повторяются, например «Цвета: чёрный, белый, серый» дважды.
• Одни и те же условия доставки и возврата есть в каждой карточке, а нужны только в первой.

Последний случай отличается от остальных: повтор там не в одной ячейке, а между ячейками. Для него в !SEMTools отдельный режим, о нём ниже.

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

Формула режет ячейку на строки, оставляет уникальные и склеивает обратно. ТЕКСТРАЗД с пустым вторым аргументом и СИМВОЛ(10) в третьем раскладывает строки в столбец — именно в таком виде их принимает УНИК.

=ОБЪЕДИНИТЬ(СИМВОЛ(10);ИСТИНА;УНИК(ТЕКСТРАЗД(A2;;СИМВОЛ(10))))

Порядок строк сохраняется, остаются первые вхождения. Два нюанса, о которых редко пишут. УНИК не различает регистр: «Самовывоз» и «самовывоз» для неё одно и то же. А строки, отличающиеся лишним пробелом в конце, считаются разными — добавьте СЖПРОБЕЛЫ перед УНИК, если это мешает:

=ОБЪЕДИНИТЬ(СИМВОЛ(10);ИСТИНА;УНИК(СЖПРОБЕЛЫ(ТЕКСТРАЗД(A2;;СИМВОЛ(10)))))

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

В Excel 2019 и раньше нет ни ТЕКСТРАЗД, ни УНИК. Остаётся Power Query: разделить столбец по разделителю «перенос строки» на строки, удалить дубликаты внутри группы и собрать строки обратно. Это работает, но на поверку это десяток шагов в редакторе запросов — для разовой чистки слишком долго.

Удалить повторы строк в ячейках в 2 клика с !SEMTools

В !SEMTools я разделил задачу на два режима, потому что «повтор» можно понимать по-разному. Оба — в меню УДАЛИТЬ → Диапазоны → Строки внутри ячеек.

Повторы в каждой ячейке

Строки сравниваются только в пределах своей ячейки: в каждой остаётся первое вхождение, повторы ниже удаляются.

1. Выделите ячейки или столбец.
2. Нажмите УДАЛИТЬ → Диапазоны → Строки внутри ячеек → Повторы в каждой ячейке.

Повторы во всём выделении

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

Что важно знать про обе процедуры:

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

Почему повторы строк не удаляются — частые ошибки

• Строки выглядят одинаково, но остались — отличаются пробелом в конце, неразрывным пробелом или регистром. Сначала удалите лишние пробелы.
• Формула вернула одну строку — у ТЕКСТРАЗД разделитель указан вторым аргументом, а не третьим, и строки разложились в строку, а не в столбец.
• Пропал перенос, текст склеился — у ячейки выключен «Перенос текста»; включите его на вкладке «Главная».
• Удалилось больше, чем ожидалось — выбран режим «во всём выделении» вместо «в каждой ячейке».

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

Как удалить дубли строк внутри одной ячейки Excel?

В Excel 365 — формулой =ОБЪЕДИНИТЬ(СИМВОЛ(10);ИСТИНА;УНИК(ТЕКСТРАЗД(A2;;СИМВОЛ(10)))). В любой версии — процедурой !SEMTools «Строки внутри ячеек → Повторы в каждой ячейке».

Учитывает ли УНИК регистр?

Нет. Для УНИК «Самовывоз» и «самовывоз» — одно значение. Процедура !SEMTools, наоборот, сравнивает строки точно, с учётом регистра.

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

Процедурой !SEMTools «Повторы во всём выделении»: каждая строка останется только в той ячейке, где встретилась впервые.

Сохраняется ли порядок строк?

Да, и в формуле с УНИК, и в процедурах !SEMTools остаются первые вхождения в исходном порядке.

Итоги: как удалить повторяющиеся строки в ячейке

Надеюсь, на этой странице вы нашли то, что искали, и смогли избавиться от дублей в многострочных ячейках. В Excel 365 для пересчитываемого результата подойдёт связка ТЕКСТРАЗД + УНИК + ОБЪЕДИНИТЬ. Если версия Excel старше или нужно убрать строки, повторяющиеся между ячейками, быстрее !SEMTools.

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

Смотрите также по теме:

• Удалить пустые строки в ячейке;
• Удалить повторяющиеся слова;
• Удалить диапазоны: строки и столбцы.