Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"



Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub


"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky