[VBA] How to Split Strings and Extract Values using the Split Function

Background

Imagine a situation where a single cell in Excel contains multiple pieces of information in one line, such as “Soccer, August 1, 8:00 PM, School Ground”.

I wanted to find a way to break this text down into individual parts (Event, Date, Time, Location) using VBA so I could use them separately. I learned that the Split function makes this easy to achieve.

In this article, I will summarize how to implement this.

Target String and Goal

Assume Cell A1 contains the following string:

Soccer, August 1, 8:00 PM, School Ground

My goal is to split this string and extract each element as follows:

• Event: Soccer
• Date: August 1
• Time: 8:00 PM
• Location: School Ground

Implementation Code (Split Function Example)

Below is the VBA code to achieve this. Note that I am using a comma (,) as the delimiter.

Sub SplitTextExample()

    Dim splitValues() As String
    Dim eventName As String
    Dim dateValue As String
    Dim timeValue As String
    Dim location As String

    ' Split the value of Cell A1 by comma ","
    splitValues = Split(Range("A1").Value, ", ")

    ' Store the split values into variables
    ' Note: Arrays created by Split start at index 0
    eventName = splitValues(0)  ' Event
    dateValue = splitValues(1)  ' Date
    timeValue = splitValues(2)  ' Time
    location = splitValues(3)   ' Location

    ' Display message for verification
    MsgBox "Event: " & eventName & vbCrLf & _
           "Date: " & dateValue & vbCrLf & _
           "Time: " & timeValue & vbCrLf & _
           "Location: " & location

End Sub

Supplement: Basic Syntax of the Split Function

Split(String, Delimiter)

Example: Split("Soccer, August 1, 8:00 PM, School Ground", ", ")

This generates an array: {"Soccer", "August 1", "8:00 PM", "School Ground"}.

Practical Application: Inserting into Emails

You can use the variables extracted above (like eventName or dateValue) to build email templates.

For example: MailBody = "The schedule for " & eventName & " is " & dateValue & "."

Summary

By using the Split() function, you can easily break down multiple pieces of information entered in a single cell and retrieve them individually.

This is extremely useful for business automation, such as inserting data into email bodies or transferring data to other cells.