Forum Discussion

Ninpike's avatar
Ninpike
Copper Contributor
Oct 22, 2021

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 another?

 

Formulas and Functions

3 Replies

Sort By
  • RMJM125UK's avatar
    RMJM125UK
    Copper Contributor
    Feb 03, 2025

    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? 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Feb 03, 2025

      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

       

Resources