Как проверить, действителен ли адрес электронной почты в Excel?

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

Проверка действительности Адрес электронной почты с помощью формулы

Код VBA — автоматическая Можно вводить только адреса электронной почты

---

Проверка действительности Адрес электронной почты с помощью формулы

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

Приведённая ниже формула проверяет адрес электронной почты, убеждаясь, что он содержит хотя бы одну точку («.») и что после символа «@» следует как минимум одна точка — оба условия характерны для корректных email-адресов.

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

2. После ввода формулы нажмите Enter, чтобы подтвердить. Затем протяните маркер заполнения вниз, чтобы применить эту формулу ко всем ячейкам целевого столбца. Формула вернёт ИСТИНА для записей, прошедших проверку (вероятно, действительных), и ЛОЖЬ для записей, не соответствующих этим требованиям.

Примечания и советы:

• Эта формула проверяет лишь базовый формат: она подтверждает наличие точек и их расположение относительно символа «@», но не гарантирует существования домена или имени пользователя и не охватывает некоторые редкие, хотя и допустимые, случаи.
• Если ваши данные содержат пробелы, специальные символы или завершающие знаки препинания, это может нарушить корректность проверки на валидность.
• Для более строгой проверки формата электронной почты рекомендуем добавить дополнительные проверки или воспользоваться VBA/макросами, как описано ниже.

---

Код VBA — автоматическая Можно вводить только адреса электронной почты

Для более продвинутой и автоматизированной проверки адресов электронной почты — особенно если вы хотите программно помечать или выделять недопустимые записи — макрос VBA станет отличным решением. Этот подход идеально подходит для таблиц с большим объёмом данных или когда требуется пакетная обработка для обеспечения соответствия протоколам связи.

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

1. Нажмите Инструменты разработчика > Visual Basic, затем в окне Microsoft Visual Basic for Applications выберите Вставка > Модуль и вставьте следующий код VBA в модуль:

Sub ValidateEmailAddresses()
    Dim rng As Range
    Dim cell As Range
    Dim email As String
    Dim atPos As Long
    Dim dotPos As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.InputBox("Select email range", xTitleId, Selection.Address, Type:=8)
    
    For Each cell In rng
        email = Trim(cell.Value)
        atPos = InStr(1, email, "@")
        
        If atPos > 1 Then
            dotPos = InStr(atPos + 1, email, ".")
            
            If dotPos > atPos + 1 Then
                cell.Interior.ColorIndex = xlNone ' Format as valid
            Else
                cell.Interior.Color = vbYellow ' Flag as invalid
                cell.AddComment "Invalid email format"
            End If
        Else
            cell.Interior.Color = vbYellow ' Flag as invalid
            cell.AddComment "Invalid email format"
        End If
    Next cell
End Sub

2. Чтобы запустить макрос, нажмите кнопку . Появится диалоговое окно с предложением выбрать диапазон, содержащий ваши адреса электронной почты. Недопустимые записи будут выделены жёлтым цветом и снабжены комментарием «Недопустимый формат электронной почты», а действительные останутся без изменений.

• Если ваш лист защищён, сначала сохраните свою работу, а затем, при необходимости, снимите защиту перед запуском макроса.
• Перед повторным запуском удалите или очистите комментарии, чтобы избежать дублирования пометок.
• Макрос проверяет только формат электронного адреса, но не подтверждает его существование. При необходимости проверки на уровне всей организации экспортируйте результаты для последующей верификации.

Благодаря этим решениям Excel вы сократите ручной труд при управлении электронными адресами, минимизируете ошибки в переписке и упростите подготовку контактных списков для email-рассылок и отчётности.

• Если возникают ошибки из-за формата ячеек (например, чисел, сохранённых как текст), убедитесь, что столбец с адресами электронной почты отформатирован как «Общий» или «Текст» до применения формул или выполнения проверки.
• Для больших наборов данных рекомендуется сочетать проверку с помощью формул и пометку недопустимых записей через VBA, чтобы обеспечить тщательный анализ.
• Периодически проверяйте свою базу данных на наличие изменений в требованиях к доменам или стандартах формата «Новое письмо».

Другие связанные статьи: