| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
email validation?
is there a built-in function in Excel that tells whether a text string is a
valid email? (I don't mean whether the address exists or not, but rather if it has exactly one "@" in it, then some letters, then a ".", some more letters, etc) I can build one, but before I do... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
email validation?
There is no built-in way to do this. Try
Dim S As String S = " If S Like "?*@?*.?*" Then Debug.Print "OK" Else Debug.Print "Not ok" End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "GoBobbyGo" wrote in message ... is there a built-in function in Excel that tells whether a text string is a valid email? (I don't mean whether the address exists or not, but rather if it has exactly one "@" in it, then some letters, then a ".", some more letters, etc) I can build one, but before I do... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
email validation?
This one is better than my previous reply:
If Len(S) - Len(Replace(S, "@", "")) = 1 Then If S Like "?*@?*.?*" Then Debug.Print "OK" Else Debug.Print "Not ok" End If Else Debug.Print "not ok" End If "Chip Pearson" wrote in message ... There is no built-in way to do this. Try Dim S As String S = " If S Like "?*@?*.?*" Then Debug.Print "OK" Else Debug.Print "Not ok" End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "GoBobbyGo" wrote in message ... is there a built-in function in Excel that tells whether a text string is a valid email? (I don't mean whether the address exists or not, but rather if it has exactly one "@" in it, then some letters, then a ".", some more letters, etc) I can build one, but before I do... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
email validation?
I ended up just going with the function:
=IF(OR(ISERROR(FIND("@",M14)),ISERROR(FIND(".",RIG HT(M14,LEN(M14)-FIND("@",M14))))),"invalid",IF(AND(FIND("@",M14)1 ,ISERROR(FIND("@",RIGHT(M14,LEN(M14)-FIND("@",M14)))),FIND(".",RIGHT(M14,LEN(M14)-FIND("@",M14)))1,FIND(".",RIGHT(M14,LEN(M14)-FIND("@",M14)))<LEN(M14)-FIND("@",M14)),"valid","invalid")) "Chip Pearson" wrote: This one is better than my previous reply: If Len(S) - Len(Replace(S, "@", "")) = 1 Then If S Like "?*@?*.?*" Then Debug.Print "OK" Else Debug.Print "Not ok" End If Else Debug.Print "not ok" End If "Chip Pearson" wrote in message ... There is no built-in way to do this. Try Dim S As String S = " If S Like "?*@?*.?*" Then Debug.Print "OK" Else Debug.Print "Not ok" End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "GoBobbyGo" wrote in message ... is there a built-in function in Excel that tells whether a text string is a valid email? (I don't mean whether the address exists or not, but rather if it has exactly one "@" in it, then some letters, then a ".", some more letters, etc) I can build one, but before I do... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
email validation?
I use this UDF
'----------------------------------------------------------------- Public Function ValidEmail(Adress As String) As Boolean '----------------------------------------------------------------- Dim oRegEx As Object Set oRegEx = CreateObject("VBScript.RegExp") With oRegEx .Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$" ' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$" ValidEmail = .Test(Adress) End With Set oRegEx = Nothing End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GoBobbyGo" wrote in message ... I ended up just going with the function: =IF(OR(ISERROR(FIND("@",M14)),ISERROR(FIND(".",RIG HT(M14,LEN(M14)-FIND("@",M 14))))),"invalid",IF(AND(FIND("@",M14)1,ISERROR(F IND("@",RIGHT(M14,LEN(M14) -FIND("@",M14)))),FIND(".",RIGHT(M14,LEN(M14)-FIND("@",M14)))1,FIND(".",RIG HT(M14,LEN(M14)-FIND("@",M14)))<LEN(M14)-FIND("@",M14)),"valid","invalid")) "Chip Pearson" wrote: This one is better than my previous reply: If Len(S) - Len(Replace(S, "@", "")) = 1 Then If S Like "?*@?*.?*" Then Debug.Print "OK" Else Debug.Print "Not ok" End If Else Debug.Print "not ok" End If "Chip Pearson" wrote in message ... There is no built-in way to do this. Try Dim S As String S = " If S Like "?*@?*.?*" Then Debug.Print "OK" Else Debug.Print "Not ok" End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "GoBobbyGo" wrote in message ... is there a built-in function in Excel that tells whether a text string is a valid email? (I don't mean whether the address exists or not, but rather if it has exactly one "@" in it, then some letters, then a ".", some more letters, etc) I can build one, but before I do... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sending email from excel not working | Excel Discussion (Misc queries) | |||
| Remove Duplication from Validation List? | Excel Discussion (Misc queries) | |||
| Copy workbook- Validation function | Excel Worksheet Functions | |||
| Move a Column of 500 Email Addresses into BCC Field of an Email | Excel Worksheet Functions | |||
| body of email disappears when I send an email from Excel | Excel Discussion (Misc queries) | |||
Linear Mode
