Extraire adresse mail d'un texte

Le 09/03/2011 à 23:14

Bonjour à tous,

J'adore Excel mais de temps en temps un besoin qui paraît simple peut s'avérer super complexe à formuler. Et de temps en temps, on découvre une nouvelle formule et ce qui paraissait si complexe devient simple...

Alors simple ou complexe, je ne sais pas, en tout cas je suis confronté au problème suivant: dans un fichier Excel se trouve une colonne commentaire dans laquelle se trouve, ou pas, une adresse mail. Je souhaite récupérer uniquement l'email (dans une autre colonne).

Sachant qu'il n'y a pas de marqueur avant et après l'adresse mail (trype ";" ou ":"), le texte dans chaque cellule peut se présenter de la façon suivante

Option 1

"mon.adresse@email.com"

Option 2

"mon.adresse@email.com bla bla bla bla"

Option 3

"bla bla bla mon.adresse@email.com"

Option 4

"bla bla bla mon.adresse@email.com bla bla bal"

(plus difficile à mes yeux car plusieurs lignes de texte dans la même cellule)

Option 5

"bla bla bla mon.adresse@email.com bla bla bal

bla bla bla

bla bla bla"

Option 6 etc.

"bla bla bla

mon.adresse@email.com bla bla bal

bla bla bla"

Option 7

=> tout pareil qu'au dessus mais avec 2 adresses email

Option 8

"pas d'adresse email"

Je pense que la formule doit être du genre

SI je TROUVE un "@" dans le texte; alors je sélectionne le texte à partir de l'espace avant "@" (s'il y a un espace avant) jusqu'à l'espace après "@" (s'il y a un espace après)

Certainement que les formules SI, TROUVE, STXT, EPURAGE doivent être utilisées mais je pense qu'il m'en manque, notamment pour sélectionner du texte de droite à gauche en partant d'un caractère au milieu du texte, ou pour repérer le "à la ligne" au sein d'une cellule.

En résumé votre aide sera grandement appréciée, tant pour le travail que je vais pouvoir en faire que pour l'économie d'aspirines pour mon pauvre neurone qui fait des nœuds sur lui même en ce moment.

Merci d'avance

Le 10/03/2011 à 06:00

Bonjour,

Un essai qui doit pouvoir être simplifié :

Ton texte en A1, en B1 :

=SI(ESTNUM(TROUVE(" ";A1))+ESTNUM(TROUVE(CAR(10);A1))=0;A1;STXT(SUBSTITUE(A1;CAR(10);" ");TROUVE("µ";SUBSTITUE(SUBSTITUE(A1;CAR(10);" ");" ";"µ";NBCAR(GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";A1)))-NBCAR(SUBSTITUE(GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";A1));" ";""))))+1;TROUVE("µ";SUBSTITUE(SUBSTITUE(A1;CAR(10);" ");" ";"µ";NBCAR(GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";A1)))-NBCAR(SUBSTITUE(GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";A1));" ";""))))+TROUVE("@";A1)-TROUVE(" ";STXT(A1;TROUVE("@";A1);99)&" ")-4))

Je te laisse tester

Ne gère pas les textes avec 2 adresses mail

@+

Le 10/03/2011 à 16:12

Bonjour thibo et merci infiniment pour cette réponse si rapide.

J'ai testé la formule qui est efficace dans de nombreux cas mais malheureusement pas tous.

Voici les cas qui ne fonctionnent pas:

Option 1

"pas de mail"

=> j'ai résolu le problème en ajoutant au début "SI(ESTERREUR(TROUVE("@";A1;1));0;"

Option 2

"bla bla bla

bla bla mon.adresse@email.com

bla bla bla bla bla bla bla bla bla

bla bla bla"

=> le resultat donné est : "mon.adresse@email.com bla bla bla"

Ce que je ne comprend pas dans ce cas c'est pourquoi il sélectionne du texte avec des espaces

Option 3

"mon.adresse@email.com

bla bla bla

bla bla bla"

=> Le résultat donné est: "#VALEUR!"

Il ne sait pas s'arrêter après le ".com" le fait que l'adresse soit suivie par un double saut de ligne génère une erreur

Je vais regarder ça de plus près, car je n'ai pas encore eu le temps d'analyser cette belle formule avec des fonctions que je ne connaissais pas. Ca permet de progresser, c'est génial.

Merci beaucoup, et dès que je trouve la solution je t'en informe.

Le 10/03/2011 à 20:31

Bonsoir,

Une autre solution dans le fichier joint avec cette formule :

=SI(ESTNUM(TROUVE(" ";A1))+ESTNUM(TROUVE(CAR(10);A1))=0;A1;SI(ESTERR(TROUVE(" ";GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";SUBSTITUE(A1;CAR(10);" ")))));GAUCHE(A1;TROUVE(" ";SUBSTITUE(A1;CAR(10);" ")&" ")-1);GAUCHE(STXT(SUBSTITUE(A1;CAR(10);" ");TROUVE("µ";SUBSTITUE(GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";SUBSTITUE(A1;CAR(10);" ")));" ";"µ";NBCAR(GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";SUBSTITUE(A1;CAR(10);" "))))-NBCAR(SUBSTITUE(GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";SUBSTITUE(A1;CAR(10);" ")));" ";""))))+1;999);TROUVE(" ";STXT(SUBSTITUE(A1;CAR(10);" ");TROUVE("µ";SUBSTITUE(GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";SUBSTITUE(A1;CAR(10);" ")));" ";"µ";NBCAR(GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";SUBSTITUE(A1;CAR(10);" "))))-NBCAR(SUBSTITUE(GAUCHE(SUBSTITUE(A1;CAR(10);" ");TROUVE("@";SUBSTITUE(A1;CAR(10);" ")));" ";""))))+1;999)&" ")-1)))

On doit pouvoir simplifier.

Je regarde si j'ai le temps

@+

655ljs.xlsx (8.72 Ko)
Le 12/03/2011 à 19:05

Ca marche aux petits oignons!

C'est génial, merci vraiment beaucoup.

Le 13/03/2011 à 12:12

Bonjour à tous,

thibo, elle donne le vertige ta formule,

voici une exercice sur les "Split"

si plusieurs adresses E-mail, on prend la 1ère (ligne 9)

Sub AdresseEmail()
Dim Lg%, i%, x, y
    Lg = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Columns("b:f").Insert
    'On Error Resume Next
    For i = 1 To Lg
    '--- repère "@" ---
        x = Split(Cells(i, "a"), "@")
        Cells(i, "b") = x(0)
        Cells(i, "c") = x(1)
    '--- remplace retour ligne par espace ---
        Cells(i, "b") = Application.Substitute(Cells(i, "b"), Chr(10), " ")
        Cells(i, "c") = Application.Substitute(Cells(i, "c"), Chr(10), " ")
    '--- repère espaces ---
        x = Split(Cells(i, "b"), " ") 'avant "@"
        y = Split(Cells(i, "c"), " ") 'après "@"
        Cells(i, "d") = x(UBound(x))
        Cells(i, "e") = y(0)
    '--- résultat ---
        Cells(i, "f") = Cells(i, "d") & "@" & Cells(i, "e")
    Next i
        Columns("f").AutoFit
        Columns("b:e").Delete
End Sub

Bonne journée

Claude

265ljs-split-2003.zip (12.61 Ko)
Le 13/03/2011 à 13:55

Bonjour,

D'accord avec toi que la formule pouvait donner le vertige.

Je savais qu'elle pouvait être simplifiée. Suffisait d'y passer un peu de temps.

Voila une première simplification :

=STXT(GAUCHE(" "&SUBSTITUE(A1;CAR(10);" ")&" ";TROUVE("@";" "&SUBSTITUE(A1;CAR(10);" ")&" "));TROUVE("µ";SUBSTITUE(GAUCHE(" "&SUBSTITUE(A1;CAR(10);" ")&" ";TROUVE("@";" "&SUBSTITUE(A1;CAR(10);" ")&" "));" ";"µ";NBCAR(GAUCHE(" "&SUBSTITUE(A1;CAR(10);" ")&" ";TROUVE("@";" "&SUBSTITUE(A1;CAR(10);" ")&" ")))-NBCAR(SUBSTITUE(GAUCHE(" "&SUBSTITUE(A1;CAR(10);" ")&" ";TROUVE("@";" "&SUBSTITUE(A1;CAR(10);" ")&" "));" ";""))))+1;9^9)&STXT(" "&SUBSTITUE(A1;CAR(10);" ")&" ";TROUVE("@";" "&SUBSTITUE(A1;CAR(10);" ")&" ")+1;TROUVE(" ";STXT(" "&SUBSTITUE(A1;CAR(10);" ")&" ";TROUVE("@";" "&SUBSTITUE(A1;CAR(10);" ")&" ");9^9))-2)

Je pense que ça doit pouvoir encore se simplifier

@+

Le 30/11/2016 à 17:20

Bonjour,

Tout d'abord merci pour cette formule qui semble très bien fonctionner. En revanche, j'ai une incompatibilité avec mes données. En effet, les adresses emails que je recherche ne sont pas précédées d'un espace, mais de guillemets, Excel m'indique donc que la formule contient des erreurs. Sauriez-vous comment faire pour appliquer cette formule mais avec des guillemets au lieu des espaces ??

Pour être plus pragmatique, voici le contenu de ma cellule A1 :

1;1100009185,00;;;"SABALAN@WANADOO.FR";;21/9/1962 00:00:00;"7 RUE DAVIOUD";;75016,00;"PARIS";"France";0,00;"0 14 55 54 42"

Je souhaite donc applique en A2 votre formule :

=STXT(GAUCHE("""&SUBSTITUE(A1;CAR(10);""")&""";TROUVE("@";"""&SUBSTITUE(A1;CAR(10);""")&" "));TROUVE("µ";SUBSTITUE(GAUCHE("""&SUBSTITUE(A1;CAR(10);""")&" ";TROUVE("@";"""&SUBSTITUE(A1;CAR(10);""")&"""));" ";"µ";NBCAR(GAUCHE(" "&SUBSTITUE(A1;CAR(10);" ")&" ";TROUVE("@";" "&SUBSTITUE(A1;CAR(10);" ")&" ")))-NBCAR(SUBSTITUE(GAUCHE("""&SUBSTITUE(A1;CAR(10);""")&""";TROUVE("@";"""&SUBSTITUE(A1;CAR(10);""")&"""));" ";""))))+1;9^9)&STXT("""&SUBSTITUE(A1;CAR(10);""")&" ";TROUVE("@";"""&SUBSTITUE(A1;CAR(10);""")&""")+1;TROUVE(""";STXT(" "&SUBSTITUE(A1;CAR(10);""")&" ";TROUVE("@";"""&SUBSTITUE(A1;CAR(10);""")&""");9^9))-2)

Et ça ne fonctionne pas car il ne prend pas en compte mes " " " en considération. Merci par avance

Le 01/12/2016 à 11:36

En l'occurence voici à partir de quelle(s) données je souhaite extraire les emails :

51068;1100009185,00;"CARIOLE";"FITELOT1";"fmoto@wanadoo.fr";1,00;;"16 RUE MOUCHETTE";;10600,00;;"France";0,00;

1;1100009185,00;;;"SABALAN@WANADOO.FR";;21/9/1992 00:00:00;"7 RUE DAVID";;75008,00;"PARIS";"France";0,00;"09 74 65 54 42"

1621072;1100009185,00;;;"moa2157149966@caramail.com";;;;;;;;;

1386171;1100009185,00;"Aude";"Minja";"ancore1@yahoo.fr";2,00;;;;33320,00;"EYTES";"France";;

1386103;1100009185,00;;;"nanard.royere@orange.fr";;;;;33300,00;;"France";;

Merci par avance, et veuillez excuser mon ignorance, je ne possède pas de connaissances aussi poussée en excel.

Le 01/12/2016 à 11:54

Version regex, avec fonction personnalisée :

232regex-email.xlsm (15.59 Ko)
LJS a écrit :

Je pense que la formule doit être du genre

SI je TROUVE un "@" dans le texte; alors je sélectionne le texte à partir de l'espace avant "@" (s'il y a un espace avant) jusqu'à l'espace après "@" (s'il y a un espace après)

cela s'écrit comme suit

[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})

j'ai même pris le cas où on a @blabla.com.cn (Chine)

Le 01/12/2016 à 14:47

Bonjour Steelson,

Merci beaucoup pour cette réponse rapide. En revanche, lorsque j'ouvre ton fichier, je n'ai aucune valeur qui s'affiche, uniquement #VALEUR, car =ExtractEMAIL(A1) ne renvoie à rien.

Pour en revenir à la solution que tu as ensuite postée

cela s'écrit comme suit

CODE: TOUT SÉLECTIONNER

[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})

Cela doit donc venir remplacer l'espace entre les guillemets nous sommes d'accord ? Ce qui potentiellement devrait nous donner la formule ci-après :

=STXT(GAUCHE("[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})";TROUVE("@";"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&" "));TROUVE("µ";SUBSTITUE(GAUCHE(" "&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})";TROUVE("@";"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"));"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})";"µ";NBCAR(GAUCHE("[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})";TROUVE("@";"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")))-NBCAR(SUBSTITUE(GAUCHE("[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})";TROUVE("@";"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"));"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})";""))))+1;9^9)&STXT(" "&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})";TROUVE("@";"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")+1;TROUVE("[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})";STXT("[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"&SUBSTITUE(A1;CAR(10);" ")&" ";TROUVE("@";"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})"&SUBSTITUE(A1;CAR(10);"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})")&"[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})");9^9))-2)

Ou alors je suis complètement à côté de la plaque, je n'ai pas compris la logique, auquel cas une seconde explication serait la bienvenue.

Merci

Le 01/12/2016 à 17:34

Il faut activer les macros ...

Le 02/12/2016 à 09:16

Peux-tu nous dire si l'activation des macros était bien le problème ?

Le 09/12/2016 à 09:34

Bonjour Steelson,

Non ça n'a rien changé pour moi j'ai toujours #VALEUR avec le fichier transmis.

En revanche quelqu'un a eut la bonne idée d'utiliser Convertir les données >> Délimité >> point virgule.

Et comme par magie toutes les données de mon fichier se mettent dans les bonnes colonnes.

Le 27/01/2020 à 11:30

Comme c'est le premier résultat sur google quand on cherche extraire email d'un texte Excel, je me permets de déterrer le sujet au cas où ma solution serve à quelqu'un. Voilà ce que j'utilise:

=SUPPRESPACE(DROITE(SUBSTITUE(GAUCHE([@addresse],TROUVE(" ",[@addresse]&" ",TROUVE("@",[@addresse]))-1)," ",REPT(" ",NBCAR([@addresse]))),NBCAR([@addresse])))
Rechercher des sujets similaires à "extraire adresse mail texte"