Forum Discussion
split the contents in a cell without delimiters
How do I separate text and numbers in a cell without delimiters? For example, if I had a cell that had "C2b45", how could I get it to put C in one cell, 2 in another, b in another, 4 in another, 5 in...
but if i wish to split B2 so that 'C' remains in B2, '2' goes to D2, 'b' goes to G2, '4' goes to H2 & 5 to J2?
Since this changes B2, it cannot be done with a formula. It would require VBA code (in the desktop version of Excel for Windows or Mac). For example:
Sub SplitCells()
Dim rng As Range
Dim s As String
Dim i As Long
Application.ScreenUpdating = False
For Each rng In Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
s = rng.Value
For i = 1 To Len(s)
rng.Offset(0, 2 * i - 2).Value = Mid(s, i, 1)
Next i
Next rng
Application.ScreenUpdating = True
End Sub