Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Вытянуть полный email c текста, С текста в ячейке A1 достать только электронный адрес в ячейку A2
 
#1
14.07.2017 11:10:34
Привет всем!

Помогите достать с текста email.
Кручу формулы ПОИСК + ДЛСТР + ПСТР никак не получается.

Детальнее:
В одной из ячеек есть текст, например:
A1: «Добрый день! Мой адрес: spasibo-za.planetu@excel.ru. Буду ждать звонка.»

С этого текста хочу достать в другую ячейку только email:
B1: spasibo-za.planetu@excel.ru

----------
Добавлено:
Примеры возможного текста: Примеры..xlsx (10.03 КБ)
PS: текст может меняться разнообразно. Нет привязки к ":", ". ", "su", "ru".
Изменено: epos2050 - 14.07.2017 12:38:45 (добавил пример текстов)
Google Sheets + Excel 2010
 
 
 
#2
14.07.2017 11:17:42
Код
=ПСТР(A1;1+ПОИСК(":";A1);ПОИСК(". ";A1)-ПОИСК(":";A1)-1)
 
 
 
#3
14.07.2017 11:20:17
Спасибо за ответ. В тексте не всегда присутствует ":" или "." в конце. Привязку можно делать только к @

PS: текст может меняться разнообразно.
Как может быть сразу email: «spasibo-za.planetu@excel.ru. Буду ждать звонка.»
Так может быть и просто голый адрес: «spasibo-za.planetu@excel.ru»
Также как и текст типа: «Привет! мой емаил spasibo-za.planetu@excel.ru»
Изменено: epos2050 - 14.07.2017 11:59:35
Google Sheets + Excel 2010
 
 
 
#4
14.07.2017 11:22:00
А точка после ru ВСЕГДА присутствует?
 
 
 
#5
14.07.2017 11:24:08
нет. Присутсвует только @ :(

Представьте себе чат поддержку. Где клиент может написать сразу свой логин. И пишет клиент обычно это как угодно:
«Ау, мой адрес spasibo-za.planetu@excel.ru»
«spasibo-za.planetu@excel.ru жду ответ»
Google Sheets + Excel 2010
 
 
 
#6
14.07.2017 11:27:40
epos2050, набросайте в Excel-файле возможные варианты написания. Оно, конечно, и нам не очень сложно, но помощь ведь ВАМ нужна )
 
 
 
#7
14.07.2017 11:28:48
Длинная формула
=ПСТР(ЛЕВБ(A1;ПРОСМОТР(99;ПОИСК({".ru";".com";".su"};A1))+ДЛСТР(ПРОСМОТР(99;ПОИСК({".ru";".com";".su"};A1);{"ru";"com";"su"})));ЕСЛИ(ЕОШ(ПОИСК(": ";A1));1;ПОИСК(": ";A1)+3);99)
Алексей М.
 
 
 
#8
14.07.2017 11:28:50
Цитата
текст может меняться разнообразно.
UDF
Код
Function Email(iCell As String)
    With CreateObject("vbscript.regexp")
        .Pattern = "[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}"
        .Global = True
        .IgnoreCase = True
        If .test(iCell) Then
          Email = .Execute(iCell)(0).Value
        Else
          Email = "Нет в строке электронного адреса"
        End If
    End With
End Function
 
 
 
#9
14.07.2017 11:36:05
Алекс, нужно уточнить у автора: может там домены и за пределами РФ ))
 
 
 
#10
14.07.2017 11:40:14
Если на то пошло, то лучше валидировать EMAIL-адрес по стандартам. Следующая регулярка вроде как решает (почти) все проблемы валидации:
Код
^[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})$

Соответственно вместо начала/завершения строки можно использовать ограничители-слова \b.
Официальный стандарт:
Код
(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])

Последний приведенный вариант скорее всего не сработает на VBA, не проверял.
Взято отсюда http://emailregex.com/

Не используйте то, что я написал. Сделано для разнообразия.
С уважением,
Федор/Все_просто
 
 
 
#11
14.07.2017 11:53:59
=ПСТР(" "&B3&" ";ПРОСМОТР(999;1/(ПСТР(" "&B3&" ";СТРОКА($A$1:ИНДЕКС($A$1:$A$1000;ПОИСК("@";B3)));1)=" ");СТРОКА($A$1:ИНДЕКС($A$1:$A$1000;ПОИСК("@";B3))))+1;ПОИСК(" ";" "&B3&" ";ПОИСК("@";B3))-ПРОСМОТР(999;1/(ПСТР(" "&B3&" ";СТРОКА($A$1:ИНДЕКС($A$1:$A$1000;ПОИСК("@";B3)));1)=" ");СТРОКА($A$1:ИНДЕКС($A$1:$A$1000;ПОИСК("@";B3)))))
Изменено: Владимир - 14.07.2017 12:37:13 (Доработка после выкладки примера.)
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#12
14.07.2017 11:57:56
Еще массивный монстр
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПСТР(ЛЕВБ(A1;ПОИСК("@";A1));1+ПОИСКПОЗ(2;1/(ПСТР(ЛЕВБ(" "&A1;ПОИСК("@";A1));СТРОКА($1:$99);1)=" "));99)&ЛЕВБ(ПСТР(A1;ПОИСК("@";A1)+1;99);ПОИСКПОЗ(1;1/(ПСТР(ПСТР(A1&" ";ПОИСК("@";A1)+1;99);СТРОКА($1:$99);1)=" ");))&" ";". ";))
Алексей М.
 
 
 
#13
14.07.2017 12:01:45
Делать народу нечего :)

epos2050, покажите варианты в файле. Или предлагаете собирать Ваши варианты по сообщениям?
 
 
 
#14
14.07.2017 12:05:46
АlехМ, спасибо. Текст не всегда может содержать ":" ru, su, "."(в конце).
Цитата
Юрий М написал:  epos2050 , набросайте в Excel-файле возможные варианты написания. Оно, конечно, и нам не очень сложно, но помощь ведь ВАМ нужна )
Юрий М,Примеры возможного текста, с которыми хотелось бы видеть результат:
« spasibo-za.planetu@excel.ru. Буду ждать звонка.»
« Привет! мой емаил spasibo-za.planetu@excel.ru »
« Добрый день! Мой адрес spasibo-za.planetu@excel.ru Буду ждать звонка.»

Я для себя точно понял как достать правую часть после @
Мы можем поиском найти позицию @ а далее искать " " (пробел). Если " " нет, тогда весь текст до конца.
А вот левую часть, до @ думаю достать либо отниманием нного текста от позиции @ или как то еще. вот гадаю.

VBA не хотелось бы использовать. Согласен на длиннющую формулу. (:
Изменено: epos2050 - 14.07.2017 12:14:53
Google Sheets + Excel 2010
 
 
 
#15
14.07.2017 12:09:35
epos2050, Вы хоть процитировали меня, но не прочитали: я просил
Цитата
Юрий М написал:
набросайте в Excel-файле
А Вы опять нам рассказываете...
 
 
 
#16
14.07.2017 12:09:56
epos2050, Примеры должны быть в файле Excel
Алексей М.
 
 
 
#17
14.07.2017 12:11:25
Цитата
Юрий М написал:
набросайте в Excel-файле возможные варианты написания
Цитата
vikttur написал:
покажите варианты в файле
Цитата
АlехМ написал:
Примеры должны быть в файле Excel
Как ещё Вам объяснить про файлы-примеры?
 
 
 
#18
14.07.2017 12:15:00
посмотрите готовое решение
http://excelvba.ru/code/EmailList

там уже есть файл-пример)
Надстройки для Excel  Парсинг сайтов  Мониторинг цен конкурентов
 
 
 
#19
14.07.2017 12:16:29
Игорь,
Цитата
epos2050 написал:
VBA не хотелось бы использовать. Согласен на длиннющую формулу.
Алексей М.
 
 
 
#20
14.07.2017 12:27:14
Извините. Примеры во вложении.

PS: возможны грамматические ошибки.
Изменено: epos2050 - 14.07.2017 12:30:37
Google Sheets + Excel 2010
 
 
 
#21
14.07.2017 12:29:23
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(" "&B3;" ";ПОВТОР(" ";99));ПОИСК("@";ПОДСТАВИТЬ(" "&B3;" ";ПОВТОР(" ";99)))-50;99))&"//";".//";);"//";)
 
 
 
#22
14.07.2017 12:43:25
Владимир, формула вроде работает :)))))) Спасибо.
Возможно ли убрать точку в 1 примере « spasibo-za.planetu@excel.ru. » ?
Google Sheets + Excel 2010
 
 
 
#23
14.07.2017 12:45:19
epos2050, не нужно цитировать целиком всё сообщение! Для обращения по имени есть кнопка "Имя".
 
 
 
#24
14.07.2017 12:45:19
vikttur, идеально! Кланяюсь!))

Если возможно, подчистите тему для будущих поколений)
Изменено: epos2050 - 14.07.2017 12:48:43
Google Sheets + Excel 2010
 
 
 
#25
14.07.2017 13:25:45
Еще вариант
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(B3;" ";ПОВТОР(" ";99));МАКС(1;ПОИСК("@";ПОДСТАВИТЬ(B3;" ";ПОВТОР(" ";99)))-50);99);". ";))
Изменено: АlехМ - 14.07.2017 13:26:46
Алексей М.
 
 
 
#26
14.07.2017 14:38:38
Код
Function ExtrEMail(ByVal s As String) As String
Dim objRegExp, a, a0, i As Integer
Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.Global = True
objRegExp.Pattern = "(\:|\?|\,|\«|""|\»|\/|\!)" 'символы, которые не могут принадлежать адресу
a = Split(objRegExp.Replace(s, " "), "@")
For i = UBound(a) To 1 Step -1 'если emailов несколько, то возвращаются через запятую
  a(i) = Split(a(i), " ")(0)
  a0 = Split(a(i - 1), " ")
  a(i) = a0(UBound(a0)) & "@" & a(i)
  If Len(a(i)) Then
    If Left(a(i), 1) = "." Then a(i) = Right(a(i), Len(a(i)) - 1)
    If Right(a(i), 1) = "." Then a(i) = Left(a(i), Len(a(i)) - 1)
  End If
  If ExtrEMail = "" Then ExtrEMail = a(i) Else ExtrEMail = a(i) & "," & ExtrEMail
Next
End Function
Sub Пример()
Dim s, m
s = "«Добрый день! Мой адрес: spasibo-za.planetu@excel.ru. Буду ждать звонка.» трали-вали.abc.def.spb@даже.рф: трали-вали попадет в адрес"
m = ExtrEMail(s)
End Sub
Изменено: AAF - 14.07.2017 14:38:57
 
 
 
Страницы: 1
Читают тему
Наверх