A VBA array approach
This late post in addition to the valid answers above demonstrates an array approach and a double splitting:
- section
[1]
splits into lines via the vbLf
delimiter (equalling Chr(10)
),
- section
[2]
restricts splitting to two parts (via ": ")
As it's not so widely known how to use the Split()
function by limiting output to 2 tokens only as shown in section [2]
, have a look at the
Syntax
Split(expression, [ delimiter, [ limit, [ compare ]]])
Option Explicit
Sub SplitIntoTokens()
With Sheet1 ' << change to your project's sheet Code(Name)
'[0] get string content
Dim lastRow As Long
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Dim data As Variant
data = .Range("A1:D" & lastRow).Value
Dim i As Long
For i = 2 To UBound(data)
'[1] split into lines
' ~> Name: John|Address: USA, New York|Age: 66
Dim lines: lines = Split(data(i, 1), vbLf)
'[2] split into 2 parts only and take the 2nd one
Dim ii As Long, tmp
For ii = 0 To UBound(lines)
lines(ii) = Split(lines(ii), ": ", 2)(1) ' split via ": "-delimiter, 2nd part via index (1)
data(i, ii + 2) = lines(ii)
Next
'Debug.Print Join(lines, "|") optional (display results in VB Editors Immediate Window
Next
'[3] write array results back to sheet
.Range("A1").Resize(UBound(data), 4) = data ' write data
.Range("A1:D1") = Split("Data,Name,Address,Age", ",") ' write header (if not existant)
End With
End Sub