+- QP School (https://qomplainerzschool.lima-city.de)
+-- Forum: Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=3)
+--- Forum: Excel VBA Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=48)
+--- Thread: Split text in cells with VBA (/showthread.php?tid=5266)
Split text in cells with VBA - Qomplainerz - 07-27-2023
Example:
Sub SplitTextInCells()
Dim sourceRange As Range
Dim cell As Range
Dim parts As Variant
'Set the range where the text is located (assuming data starts from A1)
Set sourceRange = Range("A1:A5")
'Loop through each cell in the range
For Each cell In sourceRange
'Split the text in the cell based on the delimiter
parts = Split(cell.Value, ",")
'Place the split parts in adjacent columns
cell.Offset(0, 1).Resize(1, UBound(parts) + 1).Value = parts
Next cell
End Sub
Explanation:
The code defines a subroutine named SplitTextInCells.
It declares variables for the source range (sourceRange), a loop variable (cell), and an array variable to hold the split parts (parts).
Set sourceRange = Range("A1:A5") specifies the range A1:A5 where the text is located.
The For Each loop is used to loop through each cell in the sourceRange.
The Split function is used to split the text in the cell based on the delimiter ("," in this example).
The result is stored in the array variable parts.
The split parts are then placed in adjacent columns to the right of the original cell.
cell.Offset(0, 1) refers to the cell next to the current cell, and Resize(1, UBound(parts) + 1) adjusts the size of the destination range to fit the number of split parts.
The split parts are written to the destination range using .Value = parts.