Text to Columns Macro

[heather]

Have a large spreadsheet, approximately 1000lines, exported from pdf to xml,
then merged from multiple files using Ron deBruin RDBMerge Add-in (thanks,
good macro, easy to use) - every third row is a some unformatted data in
approx. 5 lines. I want to convert this to columns; I can do this manually by
using text to column BUT - a) it is time consuming and I'd prefer to do it by
macro; and b) after about 50 or so iterations, the Text to Column Wizard
LOSES something, and says it is delimited instead of fixed column, and if I
click fixed column, it loses the columns and I am expected to add in the
breaks manually - if I cut and paste it to another worksheet, it works
fine...I really really don't want to do this a zillion times, as it will take
me all day... does anyone know of a) why the wizard stops working and b) if
there is a macro that will select every third row and convert it to text to
columns; any help muchly appreciated.

 

[Rick Rothstein \(MVP - VB\)]

Can you tell us what the row number of the first of these "every third row"
is? Also, can you give us the fixed column values that delineate your data?
I guess you should tell us the column this unformatted data is in. We can
design the macro to handle all the settings automatically, as long as you
tell us what they are.

Rick

 

[heather]

Hi, It starts at row 3, but I got as far as Row 249 by doing it manually
before the rows to columns wizard decided to stop working...

all the data is in column a; the wizard just pasted each separate number in
a new column, from col a to col ai
a................b... ..c.....d.....e.....f
01/04/08.....555..66...44...77....320 (like so, top row are column headings)

a sample of the data is below (it is water use by date for selected suburbs
- eventually I want this in db4 for GIS processing...) Ideally I would like
it in rows and columns in the form below, but if it just extends along one
row, I can manually cut and paste it to how I want it formatted)
"01/04/08 30 360 820 N/A N/A N/A
01/01/08 30 340 790 50 350 780
01/10/07 30 340 780 60 360 800
01/07/07 30 340 770 60 360 810
01/04/07 30 400 930 60 400 950"

thanks a mill for any help
cheers
Heather

 

[Rick Rothstein \(MVP - VB\)]

As a result of your last post, I don't think I'm entirely clear on what your
layout is anymore. What is in the "every third" row... the 5 lines of data
that you showed as a sample? I got the impression that you were using
fixed-width delimiting from your first post, but the N/A in the 5th column
(2-digit data) would preclude that, no? Are you using space delimiters or
not? Also, can you describe what is in the other two rows that are not in
the "every third" group? Better would be if you could post a sample of your
worksheet online somewhere so we can see exactly what you are working with.

Rick

 

[heather]

sorry, a sample of the worksheet is below (the within row line breaks are in
the original data, ie. between (Litres/Day) and Reading Period,
etc...something to do with the original xml format?):

Col A
Row "1" - Customer Classification: Domestic Customer Type: House Billing
Cycle: Quarterly

Row "2" - "Average daily usage (Litres/Day)
Reading period
Carina Heights Brisbane Total"

Row "3" - "01/04/08 30 360 820 N/A N/A N/A
01/01/08 30 340 790 50 350 780
01/10/07 30 340 780 60 360 800
01/07/07 30 340 770 60 360 810
01/04/07 30 400 930 60 400 950"

All of the block data (ie. immediately above is in one cell & the N/A just
means that no water data for that period have been collected - the wizard
previously made "it's own" fixed width estimates, which were correct, then
for some reason lost them (256 line limit????)

 

[heather]

Just a quick update, I've managed to bodge it by cutting and pasting 250
records at a time, then manually doing text to columns, and copying back to
the original worksheet (time consuming, but works)

 

[Rick Rothstein \(MVP - VB\)]

Assuming there is a single space between all your data (as shown in your
example), then this macro should take care of everything for you...

Sub ProcessData()
Dim X As Long
Dim Y As Long
Dim Z As Long
Dim LastRow As Long
Dim CellText As String
Dim Records() As String
Dim Fields() As String
With Worksheets("Sheet2")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
LastRow = 3 * Int(LastRow / 3) ' Last row that is a multiple of 3
For X = LastRow To 3 Step -3
CellText = Cells(X, "A").Value
Records = Split(CellText, vbLf)
For Y = 0 To UBound(Records)
Fields = Split(Trim$(Records(UBound(Records) - Y)), " ")
For Z = 0 To UBound(Fields)
Cells(X, "A").Offset(0, Z).Value = Fields(Z)
Next
If Y < UBound(Records) Then Cells(X, "A"). _
Resize(1, 1 + UBound(Fields)).Insert xlShiftDown
Next
Next
Rows("2:" & LastRow).RowHeight = Rows(1).RowHeight
End With
End Sub


Rick

 

[Dave Peterson]

I'd record a macro when I selected one of those lines and did data|text to
columns. Be careful and split each field the way you want--make sure you
specify the order you want for the date field, too (mdy or dmy or whatever).

Then you could use a macro like:

Option Explicit
Sub testme()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 3
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step 3
.Cells(iRow, "A").TextToColumns _
Destination:=.Cells(iRow, "A"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
Next iRow
End With
End Sub

This portion:
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True

Will be replaced with your recorded code that looks the same.

I did plop the parsed data into the same row starting with column A. Was that
correct?