How to use Split Function - VBA

Introduction

Split: It is a function that can split a text string into an array, by making use of a delimiter character.

• As the name tells, the work of Split statement is to break, split or divide a string based on particular criteria.

• Split Function returns a String Array and not a String.

• Split (text_string, delimiter, limit, compare)  - where limit & Compare optional parameter

Objective

Let’s consider we have an Email ID: “someone@gmail.com” and now our objective is to break this email id into username and domain name separately.

Code: Usage of Split function

Sub Get_Domain_and_Username()

  Dim result() As String

  Dim email As String

    email = "someone@gmail.com"

    result() = Split(email, "@")

    domain = result(0)

    userName = result(1)

    MsgBox "Domain is " & domain & "UserName is " & userName

End Sub

Example 2:

Separate a list of Pipe separated names.  eg - "Yuvi|Viru|Msd|Lee"

Objective to get the third name in that list

Sub Splitdemo()
  Dim result() As String
  Dim lists As String

    lists = "Yuvi|Viru|Msd|Lee"
    result() = Split(lists, "|")

    thirdEntry = result(2)
    MsgBox "The third name in the list is: " & thirdEntry
    
    'To Loop all values in the list use the below code
    For i = LBound(result) To UBound(result)
        MsgBox "Name " & i & ": " & result(i)
    Next  
End Sub

                                   !********************* Leave your comments about the topic J ***************************!