How to convert this formula into a custom Function??

[Bryniac]

"=MID(J2;FIND("@";J2)+1;LEN(J2)-FIND("@";J2)-(LEN(J2)-FIND(".";J2;FIND("@";J2)))-1)"

I have made this formula to extract the domain from an email, i mean it takes anything after the "@" and before the final dot.

What i want to do is to add this to an Add-in like a custom function i.e. "=DOMAIN()"

I can make basic and easy functions like this, but i don't know how to do it :

Function CubeRoot(number)

CubeRoot = number ^ (1 / 3)


End Function

can someone please give me ideas, or tell me how to do it

Thanks in advance.

 

[VBA Geek]

Function Domain(str As String) As String
    Domain = Mid$(str, InStr(1, str, "@") + 1, InStrRev(str, ".") - InStr(1, str, "@") - 1)
End Function

 

[Rick Rothstein]

This function will also work...

Function Domain(str As String) As String
  Domain = Split(Split(str, ".")(0), "@")(1)
End Function

 

[skywriter]

Does this work with an email address of this type? j.bond@007.org

This function will also work...

Function Domain(str As String) As String
  Domain = Split(Split(str, ".")(0), "@")(1)
End Function

 

[Rick Rothstein]

Does this work with an email address of this type? j.bond@007.org

No, it won't (I had forgotten about other "dots" in email addresses), but this will...

Function Domain(str As String) As String
  Domain = Split(Left(str, InStrRev(str, ".") - 1), "@")(1)
End Function

By the way, thanks for catching that problem.

 

[skywriter]

By the way, thanks for catching that problem.

No, thank you. I learn a lot from you by reading your posts. The split function was a new one to me.

May I ask what the zero and one are for in your original function and how they work?

In looking up the function I see there is limit argument and a compare argument, but I don't know which one of those you are using the zero and one for or how they work.

 

[Rick Rothstein]

May I ask what the zero and one are for in your original function and how they work?

When you reference an array element, say that array is name MyArray, you put the element's index number in parentheses after the array's reference, so if you wanted to reference the element whose index number was 5 from MyArray, you would write MyArray(5). The Split function return a zero-based array, so, like any other array, you can reference any element in the array by placing that index number in parentheses after the array's reference. So...

Split(SomeDelimitedString,TheDelimiter)

returns an array and...

Slit(SomeDelimitedString,TheDelimiter)(0)

returns the first element from that array (remember, Split always produces a zero-based array), and...

Slit(SomeDelimitedString,TheDelimiter)(1)

returns the second element, etc. Note that you would only want to use this "trick" to retrieve a single item from the array... if you had to retrieve several items, it would be more efficient to assign the result from the Split function to a variable and then read the elements from that (otherwise you would inefficiently be Split'ting the same text over and over again

 

[skywriter]

Here's one more possibility.

Function Domain(str As String) As String

Domain = Split(Split(str, ".")(UBound(Split(str, ".")) - 1), "@")(1)
  
End Function

 

[Rick Rothstein]

Here's one more possibility.

Function Domain(str As String) As String

Domain = Split(Split(str, ".")(UBound(Split(str, ".")) - 1), "@")(1)
  
End Function

Yes, you could do that, but the code is slightly inefficient because you are splitting the contents of the str variable two times, once to the array and a second time to get the upper bound of that same array. It would be better to split the text to a variable and then use that in place of the str argument calls, that way you only split the text once. By the way, the above is what I had in mind when I wrote this at the bottom of Message #7...

"Note that you would only want to use this "trick" to retrieve a single item from the array... if you had to retrieve several items, it would be more efficient to assign the result from the Split function to a variable and then read the elements from that (otherwise you would inefficiently be Split'ting the same text over and over again)."

 

[skywriter]

Rick,

Thanks for the reply.
At first I didn't even realize there were multiple arrays created by the split function, I thought it split at the delimiter and you were left with just a half. After your responded to my first question I couldn't help but start playing and I came up with what you see.
I didn't know what you meant by splitting multiple times, now I do.
Thanks for the education.

Bruce