How to "Text to column" by Macro ?

[nur2544]

If cell A1 contains numbers 123456 , now I need macro for “Text to column”
Required separation for every digit like bellow
B1= 1
C1= 2
D1= 3
E1= 4
F1= 5
G1= 6

like wise If cell A1 contains numbers ABCDEF, then it will like below

B1= A
C1= B
D1= C
E1= D
F1= E
G1= F

[jindon]

Why macro?

B1:
=MID($A$1,COLUMN(A1),1)
and copy right.

If you insist

Sub test()
    With Range("a1")
        .Cells(, 2).Resize(, Len(.Value)) = Split(StrConv(.Value, 64), Chr(0))
    End With
End Sub

[nur2544]

Hi Jindon ,Because I have another module , and I want to run both module together.
However both(formula & macro) are working fine…Thanks...

[excel-walker]

Hi,

you can achieve your objective with the below code: try this.. Please make the changes for the data range

Sub modTexttoColumn()
    Dim rngData As Range
    Dim varData As Variant
    Dim lngCount As Long
    Dim lngMaxLen As Long
    Dim varOutput As Variant
    Dim lngChartLen As Long
    
    Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
    If rngData.Columns.Count = 1 Then
        varData = rngData
        For lngCount = LBound(varData) To UBound(varData)
            If Len(varData(lngCount, 1)) > lngMaxLen Then
                lngMaxLen = Len(varData(lngCount, 1))
            End If
        Next
    End If
    
    ReDim varOutput(1 To rngData.Rows.Count, 1 To lngMaxLen)
    For lngCount = LBound(varData) To UBound(varData)
        For lngChartLen = 1 To Len(varData(lngCount, 1))
            varOutput(lngCount, lngChartLen) = Mid(varData(lngCount, 1), lngChartLen, 1)
        Next
    Next
    rngData.Cells(1, 1).Resize(UBound(varOutput, 1), UBound(varOutput, 2)) = varOutput
    
End Sub

[nur2544]

Hi excel-walker, it also works fine ...thanks..