Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Оставить в ячейках только слова, содержащие "@".
 
#1
05.04.2018 23:48:12
Здравствуйте. Надо оставить в ячейках только слова, содержащие "@". Ну или проще говоря выдернуть все емейлы из ячеек. Пример прикрепляю
 
 
 
#2
06.04.2018 00:10:32
Вариант через Power Query

Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Столбец1", type text}}),
    #"Разделить столбец по разделителю" = Table.ExpandListColumn(Table.TransformColumns(#"Измененный тип", {{"Столбец1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Столбец1"),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Столбец1", type text}}),
    #"Условный столбец добавлен" = Table.AddColumn(#"Измененный тип1", "Пользовательская", each if Text.Contains([Столбец1], "@") then 1 else 0),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Условный столбец добавлен", each ([Пользовательская] = 1)),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Строки с примененным фильтром",{"Пользовательская"})
in
    #"Удаленные столбцы"
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#3
06.04.2018 00:43:07
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;СИМВОЛ(10);ПОВТОР(" ";99));ПОИСК("@";ПОДСТАВИТЬ(A1;СИМВОЛ(10);ПОВТОР(" ";99)))-50;99))
 
 
 
#4
06.04.2018 01:04:40
Не, ну конечно если E-Mail короткий, то вариант Выше, но если произвольный, то
Конечно монстроподобных E-Mail не часто встречаем, но есть. особенно роботы рассылок этим грешат (реальный адрес,  B332B547-D7EF-4D38-A179-C4904D21A350@report.keydisk.ru хотя он и уложится в те -50) что у Виктора. Вот и полез в дебри

Длинно и массивно, но было интересно
=IF(ISNUMBER(FIND("@";A1));TRIM(MID(SUBSTITUTE(CHAR(10)&A1&CHAR(10);CHAR(10);REPT(" ";LEN(A1)));LEN(A1)*LARGE(IF(FIND(CHAR(31);SUBSTITUTE(CHAR(10)&A1&CHAR(10);CHAR(10);CHAR(31);ROW($A$1:INDEX($A:$A;LEN(A1)-LEN(SUBSTITUTE(A1;CHAR(10);""))+2))))<FIND("@";A1);ROW($A$1:INDEX($A:$A;LEN(A1)-LEN(SUBSTITUTE(A1;CHAR(10);""))+2)));1);LEN(A1)));"")

P.S. Чтоб не плодить. Виктор. ну сразу про вариант с -50 не подумал,  пошел на поиск предыдущего разделителя, что и привело в логово монстра :-)

Изменено: БМВ - 06.04.2018 11:55:24
По вопросам из тем форума, личку не читаю.
 
 
 
#5
06.04.2018 01:29:13
А какой неподъемный адрес не потянет формула? Ну, в крайнем случае добавить разряд: 999, 500
 
 
 
#6
06.04.2018 12:16:41
Спасибо всем! Получилось!
 
 
 
#7
06.04.2018 12:36:00
Цитата
БМВ написал: что и привело в логово монстра :-)
Зачем...
Код
Function GetMail(cl, Optional dlm = vbCrLf)
iStr = Split(cl, dlm)
For I = 0 To UBound(iStr)
    If iStr(I) Like "*@*" Then
        GetMail = iStr(I)
        Exit Function
    End If
Next
End Function
Согласие есть продукт при полном непротивлении сторон
 
 
 
#8
06.04.2018 12:38:06
Не дает мне эта тема покоя своим названием. Вопрос то более общий и скорее вывод первого слова содержащего символ(ы)

в общем виде паразитирую на опыте  vikttur,
=TRIM(MID(SUBSTITUTE(within_text;Separator1;REPT(" ";LEN(within_text)));SEARCH(find_text;SUBSTITUTE(within_text;Separator1;REPT(" ";LEN(within_text))))-LEN(within_text);2*LEN(within_text)))
где:
within_text - исходная строка
find_text - фрагмент слова (для данного примера "@")
Separator1 - разделитель ( для данного примера char(10))

или если есть и пробелы и переводы строк, то
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1;Separator2;REPT(" ";LEN(within_text)));Separator1;REPT(" ";LEN(within_text)));SEARCH(find_text;SUBSTITUTE(SUBSTITUTE(within_text;Separator2;REPT(" ";LEN(within_text)));Separator1;REPT(" ";LEN(within_text))))-LEN(within_text);2*LEN(within_text)))
где:
Separator2- второй разделитель , и видимо всегда пробел " "
По вопросам из тем форума, личку не читаю.
 
 
 
#9
06.04.2018 13:48:03
У Игоря есть красивый макрос.
Владимир
 
 
 
#10
06.04.2018 14:09:41
Off
sokol92, Sanja, Так не интересно, а помучатся, голову поломать :-) . Мне очень понравилась часть из этогоУчитель информатики однажды сказал нам: «любые вычисления можно выполнить в любом языке программирования, даже через формулы электронной таблицы».
Поначалу, какой бы мудрой ни казалась эта фраза, упоминание в этом списке Excel выглядело глупо…
Затем, после изучения машины Тьюринга, фраза стала для нас полностью верной, хотя и не вполне реализуемой.
Получив многолетний опыт работы с Excel, мы уже поняли, что единственное ограничение формулы Excel — недостаток способов ввода-вывода.
Но множество задач, решаемых исключительно формулами, по-прежнему выглядит впечатляюще..
и далее


По вопросам из тем форума, личку не читаю.
 
 
 
#11
06.04.2018 14:34:24
Off. БМВ, да, я и сам понимаю, что таких шедевров мне не создать. Что-то Избушка заглохла, задач что ли подкинуть?
Изменено: sokol92 - 06.04.2018 14:44:46
Владимир
 
 
 
#12
06.04.2018 19:54:03
Цитата
sokol92 написал: Избушка заглохла, задач что ли подкинуть?
Вперед!
Я уже петли смазал, дверь не скрипит. Заходите.
 
 
 
#13
06.04.2018 20:43:04
Еще вариант
=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ЛЕВБ(A1;ПРОСМОТР(9^9;ПОИСК({".com";".ru"};A1)+{3;2}));СИМВОЛ(10);ПОВТОР(" ";99));99))
Если есть адрес сайта, то не верный результат. :-(
Изменено: АlехМ - 06.04.2018 21:01:08
Алексей М.
 
 
 
#14
06.04.2018 21:05:12
АlехМ, не только этими {".com";".ru"}; зонами мир наполнен :-)
По вопросам из тем форума, личку не читаю.
 
 
 
#15
06.04.2018 23:35:36
БМВ, знаю, но пример наполнен так.
Алексей М.
 
 
 
#16
07.04.2018 08:55:50
Цитата
АlехМ написал:
наполнен так.
но не совсем так. По доменам все ок. а вот последовательность в стр.4 иная и ловится URL сайта, а не E-Mail.
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх