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 :-)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.