Text to columns for every character in text file

[KongUK]

Hi

I need VBA code to import text file and put it in the format of

1. No spaces
2. Every character in a new column
3. Ignore commas, full stops

As the text to columns 'fixed width' function in excel but saves having to click every column to the end of the text


Thank you

[Jacob Hilderbrand]

After importing the file, you can record a macro if you are unsure how to do that. You will have the data all in column A. Then you can use this code to split each character.

Dim i                   As Long
Dim j                   As Long
Dim n                   As Long
Dim LastRow             As Long


    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LastRow
        n = Len(Range("A" & i).Text)
        For j = 1 To n
            If Mid(Range("A" & i).Text, j, 1) <> " " Then
                Cells(i, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Mid(Range("A" & i).Text, j, 1)
            End If
        Next j
    Next i

[jolivanes]

Like this?

Sub Hola()
Dim i As Long, c As Range
Application.ScreenUpdating = False
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
c.Value = Replace(Replace(Replace(c, ",", ""), ".", ""), Chr(32), "")
    For i = 1 To Len(c)
        Cells(c.Row, i + 1) = Mid(c, i, 1)
    Next i
Next c
Application.ScreenUpdating = True
End Sub

[KongUK]

Great thanks both!

Works a treat :-)