[Excel VBA] How to Split Cell Data by Comma into Columns Using TextToColumns and FieldInfo

When analyzing or processing data, you often encounter cases where multiple items are combined in a single cell, separated by a delimiter. This is especially common when pasting CSV data, resulting in a structure like “Name,Date,Address…”.

This article explains how to use the TextToColumns method in VBA to split cell contents by a delimiter (such as a comma) and expand them into separate columns.

Scenario Example

Assume you have data in the following format in your worksheet:

John,2025/07/30,Tokyo,A123,Remark
Mary,2025/07/31,Osaka,B456,Note

The goal is to split each item by the “comma” and format them into separate columns (e.g., Column B onwards).

VBA Code: Basic Usage of TextToColumns with Formatting Control

Sub SplitCellContentByComma()
    With ThisWorkbook.Worksheets(1).Range("B2").CurrentRegion
        .TextToColumns _
            DataType:=xlDelimited, _
            Comma:=True, _
            FieldInfo:=Array( _
                Array(1, xlTextFormat), _
                Array(2, xlYMDFormat), _
                Array(3, xlTextFormat), _
                Array(4, xlGeneralFormat), _
                Array(5, xlSkipColumn))
        .EntireColumn.AutoFit
    End With
End Sub

Explanation of Parameters

Argument	Description
DataType	Specifies that the data is delimited (xlDelimited).
Comma	Sets True to use the comma as the delimiter.
FieldInfo	Specifies the data format for each column or skips columns (details below).

Details of the FieldInfo Array

Column No.	Format Constant	Content Example
1	xlTextFormat	Name (e.g., John)
2	xlYMDFormat	Date (e.g., 2025/07/30)
3	xlTextFormat	Region (e.g., Tokyo)
4	xlGeneralFormat	Code (e.g., A123)
5	xlSkipColumn	Remark (Skipped/Not imported)

Expected Result

Column 1	Column 2	Column 3	Column 4
John	2025/07/30	Tokyo	A123
Mary	2025/07/31	Osaka	B456

(Note: The 5th item is skipped due to xlSkipColumn)

Advanced Points

• Other Delimiters: You can split by Tab (Tab:=True), Semicolon (Semicolon:=True), or Space (Space:=True).
• FieldInfo Power: Use this to precisely control which columns are treated as dates, text, or skipped entirely.
• Efficiency: This is very effective for formatting pasted data without opening a CSV file directly.

Important Notes

1. ** unintended Splitting**: If the source data contains a comma within a string (e.g., “Tokyo, Japan”), it will be split at that point, potentially shifting your data structure.
2. Overwriting: TextToColumns overwrites the destination cells. Make sure to back up your data if needed.

Summary

By using TextToColumns in VBA, you can automatically format and split data based on delimiters.

The FieldInfo parameter allows for advanced control, such as setting specific data formats or skipping unnecessary columns. Use this technique to streamline your daily data processing tasks.