Эксель заменить пробелы на тире

Обновлено: 04.07.2024

Зачастую текст, который достается нам для работы в ячейках листа 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 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)

Информация о сайте

Инструменты и настройки

Excel Windows
и
Excel Macintosh

Вопросы и решения

Работа и общение

Работа форума и сайта

Функции листа Excel

= Мир MS Excel/Замена пробела на знак "_" - Мир MS Excel

Войти через uID

Доброго времени суток и с наступающим Новым годом!
Есть нужда заменить пробелы на знак _
например было
One-Twenty Carbon 5000-D
а надо
One-Twenty_Carbon_5000-D
как реализовать? Доброго времени суток и с наступающим Новым годом!
Есть нужда заменить пробелы на знак _
например было
One-Twenty Carbon 5000-D
а надо
One-Twenty_Carbon_5000-D
как реализовать? Гость

На месте:
Выделяем необходимые данные,
Ctrl+H,
Найти: пробел (знак пробела " ", лучше его скопировать из любой ячейки)
Заменить на: _
Заменить.

На месте:
Выделяем необходимые данные,
Ctrl+H,
Найти: пробел (знак пробела " ", лучше его скопировать из любой ячейки)
Заменить на: _
Заменить. Serge_007

На месте:
Выделяем необходимые данные,
Ctrl+H,
Найти: пробел (знак пробела " ", лучше его скопировать из любой ячейки)
Заменить на: _
Заменить. Автор - Serge_007
Дата добавления - 30.12.2010 в 15:52

Замена одного текста на другой внутри заданной текстовой строки - весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE) . Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Её синтаксис таков:

=ПОДСТАВИТЬ( Ячейка ; Старый_текст ; Новый_текст ; Номер_вхождения )

Ячейка - ячейка с текстом, где производится замена
Старый_текст - текст, который надо найти и заменить
Новый_текст - текст, на который заменяем
Номер_вхождения - необязательный аргумент, задающий номер вхождения старого текста на замену

Обратите внимание, что:

Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 - обе "Маши" заменены на "Олю").
Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая "Маша" заменена на "Олю").
Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. "маша" написана с маленькой буквы)

Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом - спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами - заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

Подсчет количества слов в ячейке

Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:

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

Извлечение первых двух слов

Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:

Добавить дефис между словами по тексту в столбец и формулу

Функция «Текст в столбец» может разделять слова на столбцы на основе пробела, а затем вы можете использовать формулу, чтобы снова объединить их с дефисом.

1. Выберите список данных, в который вы хотите добавить дефис, и нажмите Данные > Текст в столбцы. Смотрите скриншот:

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

3. Нажмите Следующая> пойти последний шаг мастера, выберите ячейку для размещения разделенных данных Destination текстовое окно. Смотрите скриншот:

4. Нажмите Завершить. Теперь список данных разделен на столбцы, выберите пустую ячейку рядом с разделенными данными и введите эту формулу. = C1 & "-" & D1 (C1 и D1 - это разделенные данные), а затем перетащите дескриптор автозаполнения вниз на ячейку, необходимую для применения этой формулы. Смотрите скриншот:

Добавить дефис между словами с помощью поиска и замены

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

1. Выберите данные, к которым вы хотите добавить символы дефиса, и нажмите Ctrl + H клавиши одновременно, чтобы открыть Найти и заменить Диалог.

2. в Найти и заменить диалога под Заменять вкладка, введите пространство клавишей пробела к Найти то, что текстовое поле и введите дефис символ к Заменить текстовое окно. Смотрите скриншот:

3. Нажмите Заменить Alл. Появится диалоговое окно с напоминанием о том, сколько ячеек было успешно заменено, нажмите OK чтобы закрыть это. Пока что все пробелы в выделенном фрагменте заменены дефисом. Смотрите скриншот:

4. Закрой Найти и заменить Диалог.

Добавить дефис между словами Kutools for Excel

Если в некоторых случаях слова разделять нечем, как быстро добавить дефис между словами?

Работы С Нами Kutools for Excel, вы можете использовать его Добавить текст чтобы добавить символ дефиса перед каждым заглавным символом, а затем применить Удалить по позиции убрать первый дефис.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите данные, к которым нужно добавить дефис, и нажмите Kutools > Текст > Добавить текст. Смотрите скриншот:

2. в Добавить текст диалог, введите – в Текст поле, а затем установите флажок Только добавить к и выберите Перед заглавными буквами из раскрывающегося списка. Смотрите скриншот:

3. Нажмите Ok, и перед каждым символом верхнего регистра был добавлен символ дефиса, и нажмите Kutools > Текст > Удалить по позиции. Смотрите скриншот:

4. в Удалить по позиции диалог, введите 1 в Номера текстовое поле и отметьте Слева под Позиция раздел. Смотрите скриншот:

5. Нажмите Ok, теперь дефис ставится только между словами.

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

Замените пустое пространство ничем / подчеркиванием / тире / запятой с помощью функции поиска и замены

Этот метод применит функцию «Найти и заменить», чтобы легко заменить пустые пробелы ничем или подчеркиванием / тире / запятой в выбранных ячейках.

1. Выберите диапазон, в котором вы замените пробелы, и нажмите Ctrl + H одновременно с клавишами, чтобы открыть диалоговое окно «Найти и заменить».

Внимание: Вы также можете открыть диалоговое окно «Найти и заменить», нажав Главная > Найти и выбрать > Заменять.

2. В открывшемся диалоговом окне «Найти и заменить» сделайте следующее:
(1) В Найти то, что коробка, введите пробел;
(2) В Заменить поле, введите подчеркивание / тире / запятая по мере необходимости. (Чтобы заменить пустое пространство ничем, оставьте это поле пустым);
(3) Щелкните значок Заменить все кнопку.

3. Затем появляется диалоговое окно, в котором отображается общее количество выполненных замен. Просто нажмите на OK кнопку, чтобы закрыть его. Затем закройте диалоговое окно «Найти и заменить».

И тогда вы увидите, что все пробелы заменены указанной запятой / тире / подчеркиванием или ничем, в зависимости от вашей конфигурации. Смотрите скриншот:

Легко удалять ведущие / конечные / лишние пробелы в ячейках

Kutools для Excel Удалить пробелы Утилита позволяет пользователям Excel легко быстро удалять все пробелы в начале, в конце, лишние пробелы или все пробелы из выбранных ячеек. Полнофункциональная бесплатная 30-дневная пробная версия!

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Замените пустое пространство ничем / подчеркиванием / тире / запятой в нескольких листах / книгах

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

Помимо замены в каждой книге вручную, Kutools for Excel's Расширенный поиск и замена Утилита может помочь вам легко выполнить пакетную замену на нескольких листах и книгах.

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

1. Откройте все книги, в которых вы будете делать замены, и нажмите Kutools > Область переходов > Расширенный поиск и замена кнопка для активации панели «Расширенный поиск и замена». Смотрите скриншот:

2. На панели «Расширенный поиск и замена» перейдите к Заменять вкладка и:

(1) В Найти то, что коробка, введите пробел;
(2) В Заменить поле, введите подчеркивание / тире / запятая по мере необходимости. (Чтобы заменить пустое пространство ничем, оставьте это поле пустым);
(3) Укажите объем замены из В раскрывающийся список;

Внимание: Если вы выберете Избранные листы из В раскрывающийся список, все рабочие листы открывающих книг перечислены в Книги коробка. Затем вам нужно проверить листы в поле «Книги».

3. Нажмите Заменить все кнопку.

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

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