Need to split the value in Excel VBA into two cells-CodePudding

I have a fairly simple question. I just can't for the life of me figure out how to do this.

I have a range of cells in Excel like so:

Row  ColA
1    B001
2    B002
3    B003
4    B004
5    B005

I need to know how to split these cells in VBA so that they now are like so:

   ColB  ColC
1   B   001
2   B   002
3   B   003
4   B   004
5   B   005

I know you use the SPLIT command in VBA, but I'm struggling to know how to fill in the rest. Can anyone help?

Here is a picture to help describe what I have/need in Excel

enter image description here

CodePudding user response：

Parse Numeric Text

Test 1

Option Explicit

Sub Test1()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion.Columns(1)
    
    ' If there are headers:
    'Set rg = rg.Resize(rg.Rows.Count - 1).Offset(1)
    
    Dim Data As Variant: Data = rg.Value
    ReDim Preserve Data(1 To UBound(Data, 1), 1 To 2)
    Dim r As Long
    For r = 1 To UBound(Data, 1)
        Data(r, 2) = Right(Data(r, 1), 3)
        Data(r, 1) = Left(Data(r, 1), 1)
    Next r
    
    With rg.Offset(, 1).Resize(, 2)
        .Columns(2).NumberFormat = "@"
        .Value = Data
    End With
    
End Sub

Test 2 (without messing with the number format)

Sub Test2()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion.Columns(1)
    
    ' If there are headers:
    'Set rg = rg.Resize(rg.Rows.Count - 1).Offset(1)
    
    rg.Offset(, 1).Value = ws.Evaluate("LEFT(" & rg.Address & ",1)")
    rg.Offset(, 2).Value = ws.Evaluate("""'"" & RIGHT(" & rg.Address & ",3)")
    
End Sub

Test 3 (Using TextToColumns without messing with the number format)

Sub Test3()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion.Columns(1)
    
    ' If there are headers:
    'Set rg = rg.Resize(rg.Rows.Count - 1).Offset(1)
    
    'Application.DisplayAlerts = False ' overwrite without confirmation
    rg.TextToColumns Destination:=rg.Offset(, 1), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(1, 2))
    'Application.DisplayAlerts = True

End Sub

The twos (2) mean As Text, the zero (0) means first char (after the 0th char) and one (1) means after the first char.