isValidEmail but two in field problem

[Ruegen]

I have an old database where I can check for is valid email and that works fine however some fields have two emails placed in them causing chaos when sending an email using that field.

I need a function that checks to see if the field has two emails in it

so maybe something that

1. Checks for two @ characters


2. Checks to see if after the first email there is (after the space characters) another set of characters i.e. the second email

I have no idea which function will go through and count the number of similar characters in a string and I don't know how to get to the end of a set of characters until it gets to the space and then slices of the rest of the string.

any tips?

[Ruegen]

I was thinking for 2. I could use

Function sliceString(StringToTrim As String) As String


Dim strText As String
strText = Trim(StringToTrim)
strText = Left(strText, InStr(strText, " "))
Debug.Print strText


sliceString = strText


End Function

however I don't know if this is the best way

[pbaldy]

My first thought is using the Split() function and testing each. That would depend on a consistent separator.

[Ruegen]

My first thought is using the Split() function and testing each. That would depend on a consistent separator.

so loop through each character in a string and use split to count up how many match ?

still now sure how to loop through a string of characters one by one (yes I know it is basic 101 programming)

[pbaldy]

No, the Split() function returns an array based on the delimiter. If I used it on "paul@abc.com jim@xyz.com" using the space as the delimiter, it would return 2 values: the 2 email addresses. You have to loop the array it returns, but I think there's an example in help.

[orange]

I agree with Paul, Split is probably easiest/quickest approach. It will also find emails where are more than 2, if that condition exists.

[drexasaurus]

Oh dear, it sounds like you are storing multiple values in a single column. Ideally I would want to use something like the split function mentioned above to "clean" the data, but that would be a huge project, obviously.

Is the data entry consistent? Like, is there always (generally) a comma or space between emails? If so, creating a function to validate it will at least give you the option of forcing the user to correct issues if multiple email addresses aren't formatted correctly.

As far as going through and checking for multiple characters, it is uncharacteristically hard to loop through a string in vba (I think you have to use the mid function, if I remember correctly). I would also consider using InStr to find the number of @ characters.
Store the location in the string where the first @ is stored in a variable using InStr. If it returns 0, it's invalid (no at symbol). If it's bigger than 0, see if there are any others after that position you stored by checking if there are any occurrences of "@" after the first place one was found, if none others were found, it could then be considered valid. You'd also want to check that there was at least one "." character, but multiple periods are allowed in emails, obviously.

I checked for what was legal in email addresses on a whim for this answer, and apparently the rules are much more flexible than I expected. Technically multiple @ symbols are allowed, but they have to be enclosed in quotations (really?). Fortunately for us, I don't think that will likely ever come up...

I'd like to create a class that takes a string and turns it into a collection of single characters. It would be very inefficient, but at this point, I really don't care. It shouldn't be so awkward to loop through a string.

[pbaldy]

I don't think it's that hard to loop a string, but I suppose it depends on what you're trying to do. I've got a function to get rid of special characters and it can't be more than 8-10 lines (For/Next loop and the Len() and Mid() function are the key components).

Also, testing for how many of a certain character there are in a string can be done without looping and storing positions; think creative use of the Len() and Replace() functions.

[Ruegen]

I've updated the 2 function

'function slices string when it gets to space character
Function sliceString(StringToTrim As String) As String


Dim strText As String
strText = Trim(StringToTrim)
If InStr(1, strText, " ", vbTextCompare) > 0 Then
strText = Left(strText, InStr(1, strText, " ", vbTextCompare))
End If
sliceString = strText


End Function

[Ruegen]

No, the Split() function returns an array based on the delimiter. If I used it on "paul@abc.com jim@xyz.com" using the space as the delimiter, it would return 2 values: the 2 email addresses. You have to loop the array it returns, but I think there's an example in help.

I have functions that count array values so I could use that in the function.

[Ruegen]

Oh dear, it sounds like you are storing multiple values in a single column. Ideally I would want to use something like the split function mentioned above to "clean" the data, but that would be a huge project, obviously.

Is the data entry consistent? Like, is there always (generally) a comma or space between emails? If so, creating a function to validate it will at least give you the option of forcing the user to correct issues if multiple email addresses aren't formatted correctly.

As far as going through and checking for multiple characters, it is uncharacteristically hard to loop through a string in vba (I think you have to use the mid function, if I remember correctly). I would also consider using InStr to find the number of @ characters.
Store the location in the string where the first @ is stored in a variable using InStr. If it returns 0, it's invalid (no at symbol). If it's bigger than 0, see if there are any others after that position you stored by checking if there are any occurrences of "@" after the first place one was found, if none others were found, it could then be considered valid. You'd also want to check that there was at least one "." character, but multiple periods are allowed in emails, obviously.

I checked for what was legal in email addresses on a whim for this answer, and apparently the rules are much more flexible than I expected. Technically multiple @ symbols are allowed, but they have to be enclosed in quotations (really?). Fortunately for us, I don't think that will likely ever come up...

I'd like to create a class that takes a string and turns it into a collection of single characters. It would be very inefficient, but at this point, I really don't care. It shouldn't be so awkward to loop through a string.

did not know some of that info - thanks

You could create a class with a method that pulls in a string and then puts it into a collection/array varaiable

I thought about making a function that splits up a string to 255 characters (each loop) but I guess I could build it to suit any character number including 1 so that you could use it in your custom class.

[pbaldy]

Well, that function would get you the first address if there were multiple, right? You realize I'm suggesting the built-in Split() function (used within something else)?

[Ruegen]

Well, that function would get you the first address if there were multiple, right? You realize I'm suggesting the built-in Split() function (used within something else)?

Yeah it would return multiple array values however if there were more than one space, sometimes the staff typed all kinds of crazy things in there too which complicates things.

I just needed the first email, then check if it is valid (by the way this all works and has been tested).

[pbaldy]

I guess if you don't need any other emails from the string that function will do it.

[Ruegen]

I guess if you don't need any other emails from the string that function will do it.

I've written this function to loop through any number of characters you want to slice and puts it into an array. I'd need another set of eyes to glaze over it...

'cuts up the string into an array depending on the number of characters you want
Function sliceCharByNum(FullString As String, NumberOfCharacters As Long) As String()


If Len(FullString) = 0 Then
Dim arrEmpty() As String
ReDim arrEmpty(0 To 0) As String
arrEmpty(0) = FullString
sliceCharByNum = arrEmpty()


Else


Dim SliceByNum As Long, CurrentVal As Long, i As Long
Dim strText As String, strImportedText As String, strTextFinished As String
Dim arr() As String
ReDim arr(0 To 0) As String


NumFrom = Len(FullString)
SliceByNum = IIf(NumberOfCharacters = 0, 1, NumberOfCharacters)
strImportedText = FullString


CurrentVal = Len(strImportedText)
i = 0
Do Until strImportedText = ""


If Len(strImportedText) < NumberOfCharacters Then


Debug.Print "current i = " & i
ReDim arr(0 To i) As String
arr(i) = strImportedText
Debug.Print "HERE" & arr(i)
strImportedText = ""


Else


strText = ""
strText = Left(strImportedText, NumberOfCharacters)


CurrentVal = CurrentVal - NumberOfCharacters
Debug.Print CurrentVal


strImportedText = Right(strImportedText, CurrentVal)




ReDim arr(0 To i) As String
arr(i) = strText
i = i + 1
End If


Loop
End If




End Function