Saturday, August 1, 2015

Excel VBA Extract domain name from email address using Excel VBA Macro

Excel VBA Extract domain name from email address using Excel VBA Macro

How to Extract, follow steps as below:

Step 1 : Split email id from @ sign
Step 2: Get second portion of split result
Step 3: Now in domain name there could be more than one dots, so we will split value in step 2 using "." sign
Step 4: Store values so split in to array & remove last array
Step 5 : Remove last array value
Step 6 : Join array value using "."
Step 7: Result is your domain name

Check Code:
Sub domain_name_old()

Dim a() As String

Dim domain As String

domain = "vba@sub.domain.com" 'ActiveCell.Value

domain = Split(domain, "@")(1)

a = Split(domain, ".")

ReDim Preserve a(UBound(a) - 1)

domain = Join(a, ".")

MsgBox "Domain name is : " & domain

End Sub



Method 2 ***

Sub domain_name2()
Dim a() As String
Dim domain As String
domain = ActiveCell.Value
domain = Split(domain, "@")(1)
domain = Replace(domain, "." & Right(domain, (Len(domain) - InStrRev(domain, "."))), "")
End Sub