Как удалить тире между числами в Excel

Обновлено: 21.11.2024

Иногда вы можете получить данные (текст или числа), содержащие тире (или дефисы).

Если вы хотите удалить эти дефисы в Excel, вы можете использовать несколько способов.

И нет… делать это вручную не входит в число описанных здесь вариантов.

Это может быть особенно полезно, когда вы получаете данные номеров социального страхования (в формате AAA-GG-SSSS) и хотите удалить эти дефисы и просто получить номер.

Позвольте мне показать вам два очень простых способа сделать это.

Удалить тире с помощью функции «Найти и заменить»

Самый простой способ удалить тире в Excel – воспользоваться функцией "Найти и заменить".

Как следует из названия, вы можете найти все ячейки, в которых есть тире (или дефис), а затем заменить их пробелом или нулевой строкой.

Давайте посмотрим, как это сделать.

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

Ниже приведены шаги по использованию поиска и замены для удаления дефисов в Excel:

Вышеуказанные шаги удалят все тире из ячеек, и у вас останутся только цифры.

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

  • Изменяет исходные данные. Если исходные данные могут понадобиться позже, обязательно создайте резервную копию, прежде чем делать это.
  • Если ваш номер начинается с 0 (например, 087-54-7465), этот метод даст вам результат, в котором удален начальный нуль. Это то, что делает Excel (попробуйте ввести 001 в ячейку, и вы увидите, что она изменена только на 1). Если вам нужно изменить формат ячейки, чтобы отображались не менее 9 цифр.

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

Еще один простой способ избавиться от тире в Excel — использовать формулы.

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

Приведенная ниже формула Excel сделает это:

Приведенная выше формула ПОДСТАВИТЬ принимает три аргумента:

  • A2 — ячейка, из которой вы хотите удалить текстовую строку (в данном примере — тире).
  • «-» — строка, которую вы хотите удалить (вам нужно поместить ее в двойные кавычки)
  • «» — что вы хотите заменить. В этом примере я хочу заменить тире пробелом (пустой строкой)

Приведенная выше формула ищет тире (или дефис, или что-то другое, что вы укажете) в ячейке и заменяет его пустой строкой (что, по сути, означает, что она удаляет все тире в ячейке).

В наборах данных мы встречаем телефонные номера, содержащие тире (-). По понятным причинам мы должны убрать тире из записей телефонных номеров. В этой статье я попытаюсь объяснить некоторые из самых быстрых методов, таких как «Найти и выбрать», «Форматировать ячейку», «ПОДСТАВИТЬ формулу» и «Макрокод VBA», чтобы с легкостью удалить тире.

Предположим, у меня есть список телефонных номеров клиентов,

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

Загрузить набор данных

4 простых способа удалить тире из телефонных номеров в Excel

Способ 1: использование метода «Найти и выбрать»

Шаг 1. Перейдите на вкладку «Главная»>> Нажмите «Найти и выбрать» (в разделе «Редактирование»)>> Выберите «Заменить».

Шаг 2. В диалоговом окне «Заменить» в поле «Найти» введите тире/дефис (-) и в поле «Заменить на» нажмите «Нуль» ( ). Нажмите «Найти все».

Вы можете нажать CTRL+H, чтобы открыть окно "Найти и заменить".

Шаг 3. Нажмите «Заменить все».

Шаг 4. Появится окно подтверждения. Нажмите "ОК".

Все тире/дефисы автоматически заменяются, как показано на изображении ниже

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

Способ 2: использование формата ячейки

Шаг 1. Выберите диапазон ячеек, из которых вы хотите удалить тире. Перейдите на вкладку «Главная» >> нажмите «Ячейка» (раздел) >> нажмите «Формат» >> «Выбрать формат ячейки». Появится окно.

Шаг 2. В левой части окна «Формат ячейки» внутри «Категорий» выберите «Пользовательский» >> «Изменить любой формат с одиннадцатью нулями» (поскольку наш номер телефона состоит из 11 цифр)

Шаг 3. Нажмите "ОК".

Результат будет похож на изображение ниже

Номера телефонов, начинающиеся с 0, также сохраняют 0 в начале номера в этом процессе.

Метод 3: Использование метода формулы

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

= ПОДСТАВИТЬ(D4,"-","")

Шаг 1. Введите формулу =SUBSTITUTE(D4, "-","") в соседней ячейке.

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

Метод 4: использование кода макроса VBA

Макрокод VBA удаляет тире из выбранного диапазона ячеек с помощью кода, запускаемого Microsoft Visual Basic.

Шаг 1. Нажмите сочетание клавиш ALT+F11, чтобы открыть Microsoft Visual Basic.

Шаг 2. На панели инструментов Microsoft Visual Basic нажмите Вставить>> Модуль.

Шаг 3. Вставьте следующий код в модуль Microsoft Visual Basic.

Шаг 4. Нажмите F5, чтобы запустить код. Появится окно выбора.

Шаг 5. Выберите диапазон ячеек, из которых нужно удалить дефисы.

Шаг 6: Нажмите OK. Выполнение шагов приводит к результату, аналогичному изображению ниже

Если в начале телефонных номеров стоят нули, этот метод сохраняет их как есть.

Заключение

Наборы данных Excel поддерживают различные форматы ячеек, в том числе телефонные номера. Набор данных, содержащий телефонные номера, часто должен быть в ячейке общего формата, а дефисы удалены для работы. Мы продемонстрировали четыре самых простых метода, таких как «Найти и выбрать», «Форматировать ячейку», «ЗАМЕНИТЬ формулу» и «Макрокод VBA», для выполнения удаления дефисов в любом диапазоне ячеек. Надеемся, что эти методы правильно ответят на ваши запросы и помогут вам понять процесс.

При работе с данными в Excel (числа или текст) могут быть случаи, когда данные содержат тире или дефисы, которые необходимо удалить.

Например, предположим, что у вас есть большая группа номеров социального страхования в следующем формате: 123-456-789.

Вам нужно удалить все символы «-» и оставить только цифры.

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

Удаление тире и дефиса по одному просто невозможно при наличии больших объемов данных.

Это утомительная и трудоемкая задача.

Не волнуйтесь, есть еще несколько простых и эффективных способов удаления тире и/или дефисов из ваших данных, чтобы остались только числа или текст.

Давайте рассмотрим два простых способа удаления тире или дефиса в Excel.

Удаление тире или дефиса в Excel с помощью функции «Найти и заменить»

В Excel уже есть небольшая удобная функция "Найти и заменить".

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

Давайте рассмотрим пример с использованием номеров SNN. Это также можно сделать с номерами телефонов или любым другим типом данных:

Используя приведенный выше набор данных SSN, мы собираемся использовать функцию «Найти и заменить», чтобы удалить все дефисы в столбце A:

  1. Выберите набор данных, из которого нужно удалить дефисы (в этом примере мы выбираем A(1–6)
  2. Удерживая нажатой клавишу Control, нажмите клавишу H. Это должно было открыть диалоговое окно функции «Найти и заменить».
  3. В поле «Найти» введите символ тире (-).
  4. Поле «Заменить на» остается пустым.
  5. Далее выберите «Заменить все» в левом нижнем углу.

    6. Этот метод должен был быстро удалить все дефисы из ваших данных Excel.

    Прежде чем использовать этот метод, обратите внимание на несколько моментов:

    • Поскольку функция "Найти и заменить" заменяет ваши исходные данные, убедитесь, что у вас есть резервная копия на случай повторного восстановления исходных данных.
    • Если какое-либо из ваших чисел начинается с нуля, Excel, скорее всего, удалит эти ведущие нули. Чтобы исправить это, вам просто нужно исправить формат ячейки, чтобы отображались не менее 9 цифр.

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

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

    Вот формула, которую вы могли бы использовать:

    =ПОДСТАВИТЬ(A2, "-","")

    Давайте лучше поймем, что пытается сделать эта формула:

    • A2 для ячейки, в которой вы хотите удалить тире. Это также может быть A2–A 6, если вы одновременно редактируете несколько ячеек.
    • «-» подчеркивает удаляемый элемент, в данном случае тире.
    • "" сообщает Excel, что мы хотим заменить тире ничем (пустым).

    Давайте используем тот же пример набора данных SSN и посмотрим, как это работает в Excel:

    Как видите, формула успешно удалила тире из SSN, указанного в данных.

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

    Эти два метода позволяют быстро и легко удалить тире и дефисы из большого набора данных.

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

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

    Форматы номеров социального страхования

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

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

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

    Использование Excel для удаления символов

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

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

    Замените "J6" в приведенной выше формуле адресом ячейки, содержащей первый номер социального страхования. Например, если эта ячейка имеет формат A3, введите:

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

    Повторение шаблона во всех электронных таблицах

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

    Наведите курсор мыши на правый нижний угол ячейки, в которую вы ввели формулу. Курсор изменится на знак «+».

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

    Проблемы безопасности SSN

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

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

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