PDA

View Full Version : [SOLVED] Text to columns for every character in text file



KongUK
11-29-2017, 07:07 AM
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
11-29-2017, 10:55 AM
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
11-29-2017, 11:27 AM
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
11-30-2017, 12:41 AM
Great thanks both!

Works a treat :-)