Извлечение адреса электронной почты из ячейки, содержащей другой текст
Задача: у вас есть ячейки, содержащие адреса электронной почты, а также другой текст. Вам нужно извлечь адреса электронной почты.
Решение: проблему можно решить с помощью макроса, пользовательской функции или мегаформулы.
Макрос
В основе макроса лежит функция VBA Split. Допустим, что ячейка содержит текст Write to lora@mrexcel.com to book a seminar. Если передать этот текст в функцию Split и указать, что текст следует разбить на элементы, разделенные пробелом – x = Split(cell.Value, » » ) – VBA вернет массив, где каждое слово будет элементом массива. На рис. 1 показан массив x после использования функция Split.
Рис. 1. Функция Split возвращает массив, где каждое слово будет элементом массива
Скачать заметку в формате Word или pdf, примеры в формате Excel (с кодом VBA)
После этого макрос перебирает все элементы массива х, пока не найдет слово, соответствующее шаблону *@*.* – If x(i) Like » *@*.* » Then. Когда соответствие найдено, макрос записывает адрес электронной почты справа от исходной ячейки – cell.Offset(0, 1) = x(i). Для начала выделите все ячейки, содержащие текст (в том числе и адреса электронной почты), а затем запустите макрос:
Sub getEmailMacro()
Dim x As Variant
Dim i As Integer
Dim note As String
For Each cell In Selection
x = Split(cell.Value, » » )
For i = 0 To UBound(x)
If x(i) Like » *@*.* » Then
cell.Offset(0, 1) = x(i)
Exit For
End If
Next i
Next cell
End Sub
Адреса электронной почты записываются справа от исходного (выбранного) значения (рис. 2).
Рис. 2. Извлечение адресов электронной почты макросом
Пользовательская функция
Вы сможете адаптировать приведенный выше макрос в функцию, определяемую пользователем. Аргумент этой функции – текст, содержащий адрес электронной почты. Возвращаемое значение – сам адрес электронной почты (рис. 3).
Рис. 3. Пользовательская функция, извлекающая адрес электронной почты
Код пользовательской функции:
Public Function getEmail(note As String) As String
Dim x As Variant
Dim i As Integer
x = Split(note, » » )
For i = 0 To UBound(x)
If x(i) Like » *@*.* » Then
getEmail = x(i)
Exit Function
End If
Next i
End Function
Мегаформула
Несмотря на то, что следующая формула потребует некоторого времени на написание, она крайне умна и удивительно проста в эксплуатации: =СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ( » » &A1; » » ;ПОВТОР( » » ;20));НАЙТИ( » @ » ;ПОДСТАВИТЬ( » » &A1; » » ;ПОВТОР( » » ;20)))-20;40)). Сначала
Формула изначально использует функцию ПОДСТАВИТЬ, чтобы в исходном тексте заменить каждый пробел на двадцать пробелов подряд. Это позволяет отделить каждое слово в тексте большим числом пробелов.
Первоначальный текст: now is the time for bill@mrexcel.com to buy a book
Новый текст: now is the time for bill@mrexcel.com to buy a book
Функция НАЙТИ находит знак @ в новом тексте. Функции ПСТР извлекает фрагмент текста, начинающийся за 20 символов перед @ и включающий 40 символов. Я использовал эти параметры (20 и 40), так как считаю, что их достаточно, чтобы обработать любой возможный адрес электронной почты. Да, это может привести к ошибке, если в вашей базе есть адрес типа john.jacob.jingleheimer.schmidt@gmail.com. Однако, для адресов нормального размера в итоге вы получите что-то вроде:
Наконец, функция СЖПРОБЕЛЫ заменяет несколько пробелов подряд на один, а также удаляет начальные и конечные пробелы, так что в конечном итоге вы получите то, что хотели: bill@mrexcel.com (рис. 4).
Рис. 4. Мегаформула извлекает адрес электронной почты
Резюме: извлечение адреса электронной почты из ячейки, содержащей другой текст, можно выполнить с помощью макроса, пользовательской функции или мегаформулы.
Excel адреса электронной почты
Как проверить адреса электронной почты в столбце рабочего листа?
Как мы все знаем, действительный адрес электронной почты состоит из трех частей: имени пользователя, символа «at» (@) и домена. Иногда вы просто разрешаете другим вводить только текст формата адреса электронной почты в определенный столбец рабочего листа. Можно ли решить эту задачу в Excel?
Подтвердить только адреса электронной почты можно ввести в столбец с функцией проверки данных
В Excel мощная функция проверка достоверности данных может помочь вам быстро проверить только адреса электронной почты, введенные в столбец ячеек, пожалуйста, сделайте следующее:
1. Выделите ячейки, которые вы хотите ввести только в формате адресов электронной почты, а затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, см. снимок экрана:
2. В проверка достоверности данных диалоговое окно под Настройки вкладке, выберите На заказ из Разрешить раскрывающийся список, а затем введите эту формулу: = ISNUMBER (MATCH («*@*.?*»; A2,0)) в Формула текстовое поле, см. снимок экрана:
3. Затем нажмите OK кнопка, теперь, когда вы вводите тексты, которые не являются форматом адресов электронной почты, появится окно с предупреждением, чтобы напомнить вам, см. снимок экрана:
Подтвердить только адреса электронной почты можно ввести в столбец с отличной функцией
Советы: Чтобы применить это Подтвердить адрес электронной почты функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Выберите ячейки, в которые можно вводить только адреса электронной почты.
2, Затем нажмите Kutools > Предотвратить ввод > Подтвердить адрес электронной почты, см. снимок экрана:
3. А затем, при вводе текста, который не является форматом адреса электронной почты, появится всплывающее окно, напоминающее вам, см. Снимок экрана:
Простая демонстрация
Подтвердить только адреса электронной почты можно ввести в столбец или несколько диапазонов
Kutools for Excel‘s Подтвердить адрес электронной почты Утилита может помочь вам настроить проверку данных, что только адрес электронной почты можно ввести в диапазон ячеек одним щелчком мыши. Нажмите, чтобы загрузить Kutools for Excel!
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 60 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Excel адреса электронной почты
Как создать список рассылки Outlook из файла Excel?
Предположим, вы получили файл Excel, содержащий список адресов электронной почты новых клиентов, и теперь вам нужно сгруппировать все эти адреса электронной почты в одну группу контактов Outlook. Как этого добиться? Вам может помочь метод, описанный в этой статье.
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы создать список рассылки Outlook из файла Excel, сделайте следующее.
1. Выберите и скопируйте все адреса электронной почты на листе, на основе которого вы хотите создать список рассылки.
2. Запустите приложение Outlook и перейдите к Контакты Посмотреть. Затем нажмите Новая контактная группа под Главная таб. Смотрите скриншот:
3. в Контактная группа окно, пожалуйста, нажмите Добавить участников > Из контактов Outlook под Контактная группа меню.
5. Затем все адреса электронной почты добавляются в Контактная группа в окне укажите контактную группу в Имя поле, а затем щелкните Сохранить и закрыть кнопка. Смотрите скриншот:
Теперь контактная группа Outlook создается с указанными адресами электронной почты в файле Excel.
Добавление гиперссылок, закладок и почтовых ссылок в Excel
Задумывались ли вы, как добавить гиперссылки, закладки или почтовые ссылки в Excel? Ответы прямо здесь.
Следующие шаги применимы к Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 для Mac, Excel 2016 для Mac, Excel для Mac 2011 и Excel Online.
Что такое гиперссылки, закладки и почтовые ссылки?
Во-первых, давайте уточним, что мы подразумеваем под каждым термином.
Гиперссылка позволяет открыть веб-страницу, выбрав ячейку на листе. Он также используется в Excel, чтобы обеспечить быстрый и легкий доступ к другим книгам Excel.
закладка создает ссылку на определенную область в текущем листе или на другой лист в том же файле Excel, используя ссылки на ячейки.
Ссылка mailto – это ссылка на адрес электронной почты. Выбор ссылки mailto открывает новое окно сообщения в программе электронной почты по умолчанию и вставляет адрес электронной почты в строку «Кому» сообщения.
В Excel гиперссылки и закладки предназначены для облегчения навигации между областями связанных данных. Почтовые ссылки упрощают отправку сообщений электронной почты частному лицу или организации. Во всех случаях:
Откройте диалоговое окно Вставить гиперссылку
Комбинация клавиш для открытия диалогового окна Вставить гиперссылку : Ctrl + K на ПК или команда + K на Mac.
Как открыть диалоговое окно «Вставить гиперссылку» с помощью ленты
Добавить гиперссылку в Excel
Вот как настроить гиперссылку для перехода на веб-страницу или в файл Excel.
Добавить гиперссылку на веб-страницу
Текст привязки в ячейке рабочего листа имеет синий цвет и подчеркнут, чтобы указать, что он содержит гиперссылку. Каждый раз, когда он выбран, он открывает назначенный веб-сайт в браузере по умолчанию.
Добавить гиперссылку в файл Excel
Примечание. Этот параметр недоступен в Excel Online.
Текст привязки в ячейке рабочего листа меняется на синий и подчеркивается, чтобы указать, что он содержит гиперссылку. Всякий раз, когда он выбран, он открывает назначенную книгу Excel.
Создать закладку на тот же лист Excel
Закладка в Excel похожа на гиперссылку, за исключением того, что она используется для создания ссылки на определенную область на текущем листе или на другой лист в том же файле Excel.
В то время как гиперссылки используют имена файлов для создания ссылок на другие файлы Excel, закладки используют ссылки на ячейки и имена таблиц для создания ссылок.
Как создать закладку на тот же лист
В следующем примере создается закладка в другом месте на том же листе Excel.
Текст привязки в ячейке рабочего листа теперь имеет синий цвет и подчеркнут, чтобы указать, что он содержит закладку.
Выберите закладку, и курсор активной ячейки переместится на ссылку на ячейку, введенную для закладки.
Создать закладку на другой лист
Создание закладок на разных листах в одном и том же файле Excel или рабочей книге имеет дополнительный шаг. Вы также определите лист назначения для закладки. Переименование рабочих листов может упростить создание закладок в файлах с большим количеством рабочих таблиц.
Текст привязки в ячейке рабочего листа теперь имеет синий цвет и подчеркнут, чтобы указать, что он содержит закладку.
Выберите закладку, и курсор активной ячейки переместится на ссылку на ячейку на листе, введенном для закладки.
Вставьте ссылку Mailto в файл Excel
Добавление контактной информации на лист Excel позволяет легко отправить электронное письмо из документа.
Текст привязки в ячейке рабочего листа теперь имеет синий цвет и подчеркнут, чтобы указать, что он содержит гиперссылку.
Выберите ссылку mailto, и программа электронной почты по умолчанию откроет новое сообщение с введенным адресом и текстом темы.
Удалить гиперссылку, не удаляя текст привязки
Когда вам больше не нужна гиперссылка, вы можете удалить информацию о ссылке, не удаляя текст, который служил якорем.
Синий цвет и подчеркивание должны быть удалены из текста привязки, чтобы указать, что гиперссылка была удалена.
Как сделать почтовую рассылку клиентам из таблицы Excel
Бывает нужно разослать большое количество писем списку контактов из таблицы в Excel.
Это может быть список потенциальных клиентов с мероприятия, выгрузка контактов из программы (1С, Битрикс24, amoCRM), просто список родственников, которые с нетерпением ждут от вас поздравления с праздником или уникального предложения.
Расскажу, как сделать это просто, быстро и бесплатно. Для решения задачи понадобится Excel, Word и Outlook из пакета Microsoft Office.
Итак, например, есть Excel таблица с данными клиентов (ФИО, компания, E-mail) и мы хотим отправить им персонализированное сообщение о предстоящих скидках.
Колонка “Пол” потребуется для определения окончания “уважаемый” или “уважаемая”, ее я вычислил по формуле, если “Отчество” заканчивается на “ч” значит это мужчина, если нет — женщина:
В новом документе Word наберём шаблон текста сообщения, которое будем отправлять. Вместо обращения, имени и названия компании оставим пока пустые места.
Запустим Мастер Слияния: вкладка Рассылки (Mailings), Начать слияние (Start Mail Merge), далее Пошаговый мастер слияния (Step-by-Step Merge Wizard).
Откроется мастер, состоящий из 6 этапов.
На перовом этапе выберем пункт Электронное сообщение (E-mail), так как хотим отправить наши сообщения по электронной почте.
На втором этапе нужно указать, какой документ будет использоваться в качестве шаблона, выберем Текущий документ (Current document).
Третий этап — выбор получателей. Так как список наших клиентов хранится в файле Excel, выбираем пункт Использование списка, жмем Обзор (Browse) и указываем наш файл.
Откроется окно Получатели слияния, в нем можно выбрать или отменить клиентов для которых делаем рассылку.
Четвертый этап — создание сообщения. Тут можно вставить данные из нашего файла Excel в шаблон сообщения, который мы подготовили.
Вставим Фамилию, Имя, Отчество и название компании в наш шаблон, для этого: установим курсор в текст сообщения куда хотим вставить значение поля Excel, нажмем Другие элементы, в открывшемся окне выберем поле, которое хотим вставить, и так для всех полей.
Чтобы правильно указать обращение “уважаемый” для мужчин, и “уважаемая” для женщин создадим правило. Жмём кнопку Правила (Rules) на вкладке Рассылки (Mailings) и выберем пункт IF…THEN…ELSE.
В открывшемся окне укажем правило: если поле “Пол” равно “М” — вставить текст “уважаемый”, в противном случае — вставить текст “уважаемая”.
В итоге, после вставки полей должен получиться шаблон похожий на мой (вставленные поля выделил красным цветом).
На пятом этапе можно предварительно просмотреть результаты слияния. Для перехода между письмами служат кнопки со стрелками.
На завершающем 6 этапе нужно выбрать единственный пункт Электронная почта.
Затем в открывшемся окне указать тему отправляемых писем и выбрать в поле Кому столбец нашей таблицы Excel, в котором содержаться адреса получателей
После нажатия на кнопку ОК, Word автоматически подключится к вашему почтовому клиенту Outlook, поместит все сообщения в папку «Исходящие», после чего письма отправятся получателям.
Если вы хотите проверить письма перед отправкой, есть небольшая хитрость, нужно предварительно перевести Outlook в автономный режим Файл — Автономный режим. Письма останутся в исходящих, пока вы не выйдете из автономного режима и при необходимости вы сможете проверить или внести в них изменения.
На этом всё. Но вы можете поддержать проект. Даже небольшая сумма поможет нам писать больше полезных статей.
Если статья помогла или понравилась, пожалуйста поделитесь ей в соцсетях.
