[Excel VBA] How to Split a String by Underscore and Retrieve Values (Using the Split Function)

2025年12月26日

Introduction

Have you ever wanted to extract specific elements from a string separated by underscores (_), such as filenames or IDs?

In this article, I will introduce how to use the VBA Split() function to divide a string by _ and extract only the parts you need.

What We Want to Achieve

Extract specific parts like “Mori”, “Hayashi”, or “Ki” from a string like "Mori_Hayashi_Ki".
Split a string by _ and handle it as an array.

VBA Code

Running the following code allows you to split a string by _ and retrieve the parts as array elements.

Sub SplitExample()
    Dim fileName As String
    Dim parts() As String
    Dim desiredPart As String

    fileName = "Mori_Hayashi_Ki"
    
    ' Split the string using "_" as the delimiter
    parts = Split(fileName, "_")
    
    ' Get the second element (Index starts from 0)
    desiredPart = parts(1)
    
    MsgBox "The retrieved value is: " & desiredPart
End Sub

Verifying the Results

The Split() function divides a string by a specified delimiter and returns it as an array.

Get parts(0) → Result is “Mori”
Get parts(1) → Result is “Hayashi”
Get parts(2) → Result is “Ki”

Supplement: Syntax of the Split Function

Syntax: Split(TargetString, Delimiter)

Return Value: A one-dimensional string array.
Note: The index starts at 0.

Example: For Split("A_B_C", "_"):

parts(0) = "A"
parts(1) = "B"
parts(2) = "C"

Common Use Cases

Extracting information from filenames:
- Example: Extracting the date from "Report_2024-05-01_Tokyo.xlsx".
Decomposing IDs:
- Breaking down folder structures or serial numbers into parts.
Simple Data Parsing:
- Handling CSV-like strings without complex parsing logic.

Summary

If you want to split and handle strings in VBA, the Split() function makes it simple and flexible.

You can easily split text by any delimiter, such as _.
The data is managed as a 0-based array, allowing direct access to the specific elements you need.
It is highly useful for filename processing and data formatting tasks.

Please try using this for your daily text data processing needs.