Text to Columns Macro

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. 
0
Heather (83)
6/11/2008 3:17:00 AM
excel 39879 articles. 2 followers. Follow

7 Replies
738 Views

Similar Articles

[PageSpeed] 37

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" <heather@discussions.microsoft.com> wrote in message 
news:197F9ADC-401F-49E3-8248-AB0818DB4BAC@microsoft.com...
> 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. 

0
6/11/2008 3:30:36 AM
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)" wrote:

> 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" <heather@discussions.microsoft.com> wrote in message 
> news:197F9ADC-401F-49E3-8248-AB0818DB4BAC@microsoft.com...
> > 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. 
> 
> 
0
Heather (83)
6/11/2008 3:46:00 AM
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" <heather@discussions.microsoft.com> wrote in message 
news:3D7B60C9-C193-4CA6-BEF5-A8128AAD1D29@microsoft.com...
> 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)" wrote:
>
>> 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" <heather@discussions.microsoft.com> wrote in message
>> news:197F9ADC-401F-49E3-8248-AB0818DB4BAC@microsoft.com...
>> > 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.
>>
>> 

0
6/11/2008 4:14:40 AM
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????)

"Rick Rothstein (MVP - VB)" wrote:

> 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" <heather@discussions.microsoft.com> wrote in message 
> news:3D7B60C9-C193-4CA6-BEF5-A8128AAD1D29@microsoft.com...
> > 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)" wrote:
> >
> >> 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" <heather@discussions.microsoft.com> wrote in message
> >> news:197F9ADC-401F-49E3-8248-AB0818DB4BAC@microsoft.com...
> >> > 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.
> >>
> >> 
> 
> 
0
Heather (83)
6/11/2008 4:33:00 AM
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)

"heather" wrote:

> 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????)
> 
> "Rick Rothstein (MVP - VB)" wrote:
> 
> > 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" <heather@discussions.microsoft.com> wrote in message 
> > news:3D7B60C9-C193-4CA6-BEF5-A8128AAD1D29@microsoft.com...
> > > 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)" wrote:
> > >
> > >> 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" <heather@discussions.microsoft.com> wrote in message
> > >> news:197F9ADC-401F-49E3-8248-AB0818DB4BAC@microsoft.com...
> > >> > 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.
> > >>
> > >> 
> > 
> > 
0
Heather (83)
6/11/2008 5:04:00 AM
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


"heather" <heather@discussions.microsoft.com> wrote in message 
news:CD098559-558C-43B0-B0C4-F9A3335784BC@microsoft.com...
> 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????)
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> 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" <heather@discussions.microsoft.com> wrote in message
>> news:3D7B60C9-C193-4CA6-BEF5-A8128AAD1D29@microsoft.com...
>> > 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)" wrote:
>> >
>> >> 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" <heather@discussions.microsoft.com> wrote in message
>> >> news:197F9ADC-401F-49E3-8248-AB0818DB4BAC@microsoft.com...
>> >> > 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.
>> >>
>> >>
>>
>> 

0
6/11/2008 5:59:30 AM
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?




heather wrote:
> 
> 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????)
> 
> "Rick Rothstein (MVP - VB)" wrote:
> 
> > 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" <heather@discussions.microsoft.com> wrote in message
> > news:3D7B60C9-C193-4CA6-BEF5-A8128AAD1D29@microsoft.com...
> > > 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)" wrote:
> > >
> > >> 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" <heather@discussions.microsoft.com> wrote in message
> > >> news:197F9ADC-401F-49E3-8248-AB0818DB4BAC@microsoft.com...
> > >> > 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.
> > >>
> > >>
> >
> >

-- 

Dave Peterson
0
petersod (12004)
6/11/2008 11:37:57 AM
Reply:

Similar Artilces:

TEXT = text
I have some data in an Excel table which uses case sensitive codes. Does anyone know of a way to perform case sensitive VLookups. Example. A1 contains "TEXT", A2 contains "One". B1 contains "text", B2 contains "Two" If I enter "text" in cell A4 and the following VLookup in B4: =VLOOKUP(A4;A1:B2;2;FALSE) I get the answer "One", but I would like to see "Two". I have found a work around by converting a number of characters to their ASCII codes, but this is very messy. I know that the FIND function is case sensitive whe...

Adding rows using macro
This is my first macro! (Excel 97 running under Windows 2000.) I have a form that I want to add rows (one row at a time) after the current row. The form allows businesses to send in changes to their products. They may have 1 or 100 products that need to be updated. We want the form to initially have one row for them to enter their information. If they have another product, then we want a row to be added automatically after the current row. It looks like this: A B C D E 1 Product Name Size Quantity Curr. Price New Price 2 Popso 1L 5 cases $20.00 $25.00 Since Exce...

Selecting text in a document
Hi, I have a macro which finds a certain piece of text in the document and then moves down a line and then selects all the text between that and the end of the document and the selected text is later sent out in an email. This has been working for some time but I have recently had some problems because the text is not being selected as before (extendmode on?) and I am sending out blank emails. The code fragment that I use to select the text is as follows Selection.HomeKey wdStory Selection.Find.Execute "Issued at" Selection.MoveDo...

What is the Limit of Text Boxes & Labels a User Form can have?
What is the Max File Size a User Form can be? I have Windows 7 with Office 2007. Does it have like a 64K limit? The file size as far as kb. My entire Program is 1,203 Kb. When I ran it I got a "compile Error out of memory", so I exported my User Form. Then I looked at the file size of just the User Form in a blank Workbook 34 KB = .frm 219 KB = frx My user Form is a Multi Page with 4 Pages on it. There are alot of text boxes and labels on the 4 sheets. Is there a limit to the number of text boxes & labels you can have in a user Form? I have 12 GB of ...

To Norman or Alan Macro About Macros protected Sheets
Hi, Paul again, Sorry but I have a last question. When I type at the end of the Macro code: Sheets("Critical Path"),protect("My Password") What else do I need to type to enable the Auto filter & the Data Sort Thanks Paul. Your post is separated from the original thread. Are you using xl2002+? If yes, I got this line when I recorded a macro protecting a sheet, but allowing autofilter and sort: ActiveSheet.Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True, _ AllowSorting:=True, AllowFiltering:=True So... worksheets("cri...

Resizing cells to fit text returned by VLOOKUP
I have some VLOOKUPs which sometimes return 50 or 60 characters (they're in merged cells). I want the text to wrap, and the cell to enlarge to fit the text - Just like it does whan you type it in, but somehow, because it is being returned from a formula it just displays the bottom line of the wrapped text, and doesn't resize the row. Anyone know how I cam make it fit? I'm happy to use a VBA solution if there's no built in way to make it happen automatically. Thanks M Would you mind to limit your request to ONE newsgroup? Joerg "Michelle" <mh_londonNOJUN...

CScrollView and text height
I call DrawText to draw text on the CScrollView.When text has multi-line,how do I get the screen height to draw them? Thanks. ___________________________________ http://www.msale.net/bbs a Chinese-language message board for developers,including palm,ppc,symbian,and etc. http://www.zirong.net/blog/ Miken's private life If you are trying to calculate the height of a multiline string user DrawText with the DT_CALCRECT flag. It will use the width of the Rect that you pass it, and sets the bottom of the rect to what the bottom would be if you draw it. AliR. "Miken" <mikenwon...

100% stacked column/clustered column
Hello I have a 100% stacked column chart (12 columns). The first two columns show data for: EUN 2002 EUN 2003 The next two shows data for: EUS 2002 EUS 2003 and so on. I want to have EUN 2002 and EUN2003 placed without any space (as in a clustered column chart). Similarly for EUS 2002 and EUS 2003 and on. Any help? Hans Knudsen Hi Hans - Through careful arrangement of the data in your worksheet, you can make a stacked column chart that looks like a clustered-stacked column chart. There is a tutorial showing this technique on Bernard Liengme's site: http://www.stfx.ca/peop...

Text Boxes on Charts not showing up
Has anyone experienced this? I've got a chart in a spreadsheet and I add text boxes to the graph section. They appear on the page yet when I click off the page, the text box disappears. If I go to print preview, the text boxes are there so why can't I see them on the screen? I've tried the bring forward command but that didn't work. This is also sporadic as some of my text boxes stay in place why others seem to disappear. Sue - If the chart is selected when you draw the text box, the text box becomes part of the chart. If not, the text box just floats along in the dr...

avoid losing text when text submission timed out?
Often I have to log in to some web site to enter a few paragraphs of text (e.g. web email submission, user forum, product reviews, etc). Sometimes it takes me a while to compose the text, and by the time I click submit, I get a response saying the session has timed out, and the text that took me a long time to compose is gone. Is there a way to retrieve this text? Clicking the back button doesn't work. Perhaps it is in a cache somewhere? The portion of the web page containing your text may or may not be in TIF, but here's what I do: Before I hit the 'Submit'...

Ranking Numbers with Text
I am trying to sort a list of serial numbers containing alpha an numeric text into ascending order so LOOKUP will work. ie: 5KJJAHAS63PL84681 input into col. E 5KJJAHAS43PL84685 5KJJAHASx3PL84683 5KJJAHAS83PL84686 5KJJAHAS63PL84689 5KJJAHAS43PL84684 5KJJAHAS23PL84682 5KJJAHAS03PL84688 5KJJAHASx3PL84610 5KJJAHAS83PL84690 5KJJAHAS83PL84691 Only the ninth and 13 thru 17 digits change. Worksheets are protected except for inputs. I need the sort to use the LOOKUP function. I tried =IF(E3>0,(RIGHT(E3,5)*1),999999) to RANK by the last 5 =RANK(D3,D$3:D$102,1)+COUNTIF(D$3:D3,D3)-1, but then LOOKUP ...

Null in text box
Can anyone see what might be wrong withthis procedure? Private Sub Job_Number_BeforeUpdate(Cancel As Integer) If IsNull(Me.Job_Number) = True Then MsgBox "No Job Number Entered" Cancel = True End If End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 "Haggr1 via AccessMonster.com" <u35393@uwe> wrote in news:74fa8171f7137@uwe: > Can anyone see what might be wrong withthis procedure? > > Private Sub Job_Number_BeforeUpdate(Cancel As Integer) > > If IsNull(Me.Job_...

"Can't find macro" error
I have a simple macro that executes the following: Sub SAVE_Quit() ActiveWorkbook.Save Application.Quit End Sub I assigned it to a toolbar button called "Save List". Whenever I try to run the macro from the toolbar, I get the "can't find macro error". But I can successfully run the macro from the Tools > Macro > Macros list. The sheet is protected but I still get the error when I unprotect it. The macro is stored in Module 1. I tried moving it to This Workbook or Sheet 1 (in case it was stored in the wrong place) but got the same error. What am I ...

convert text case in same column or row?
How do I convert text case in the same colum or row. At the moment I use UPPER, but have to create a new column/row. You may find this to be handy Sub ChangeCase() 'Don Guillett Application.ScreenUpdating = False Dim r As Range nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select Case Desired")) Select Case nCase Case "L" For Each r In Selection.Cells If r.HasFormula Then r.Formula = LCase(r.Formul...

I TYPE IN A TEXT BOX AND IT COPIES THE TEXT TO OTHER TEXT BOXES
Does anyone know how to fix this? Shari wrote: > Does anyone know how to fix this? ================================ Are you editing text on the Master page? -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://support.microsoft.com/ph/695 Notice This is not tech support I am a volunteer Solutions that work for me may not work for you Proceed at your own risk Yes, I thinnk so. :-) I'm not on the background. It's an 8 pg brochure I'm creating and it didn't do it before I added page 8. ...

Re: Using Text Styles
Front page will not have a Next Edition. AFAIK it is dead. > Ed Bennett wrote: > > Ron Hagley wrote: > >> I have a document with Headings on one line followed by a linespace > >> then on a new line a subheading followed by (on the same line ) a > >> description > >> > >> I wish to use TEXT STYLES as this pattern repeats many times, I have > >> no problem seting up the styles for the headings, but when I do the > >> same for the SUBHEADINGS the text style is applied to the WHOLE line > >> including the description...

How do I merge two columns without losing data?
If I want to split a column in two, I know I can use the Text to Column feature. How can I go from two columns to one without losing data in the merge? One way =CONCATENATE(A1,B1) Ian --- "Caseybay" <Caseybay@discussions.microsoft.com> wrote in message news:2D860F83-00D5-446D-AAA5-BE938DF3C1BF@microsoft.com... > If I want to split a column in two, I know I can use the Text to Column > feature. How can I go from two columns to one without losing data in the > merge? =a1&b1 or =a1&" "&b1 (use a helper column of formulas.) Caseybay wrote...

Text in Cell as Formula #2
Peo + Julie Many thanks for your answer with the last query. I Have a similar question, please advise: This method [INDIRECT()] does work with values form a different spreadsheet. I type the link (as text) from a different spreadsheet in a cell in the current spreadsheet and use this method to call a value in a cell. Would appreciate a detailed answer (if complicated) - I'm a novice user! Many thanks in advance. ...

Logical Test: Text Against Number 01-05-10
Can anyone provide insight into how Excel treats this type of test? For example, why does Excel return TRUE when we have ="a">2 but FALSE when ="a"<2 (or any other number for that matter). Thanks. That's the way it is treated. Any kind of text is larger than the largest number. Any worth? hit the YES below -- Max Singapore --- "rslaughter5" wrote: > Can anyone provide insight into how Excel treats this type of test? For > example, why does Excel return TRUE when we have ="a">2 but FALSE when ="a"<2 ...

Event macro for a task
Whenever I open that workbook ,datavalues of sheet 2, F2:G6 be populated in msg.box once only and after acknowledging with ok button macro should end. Hi Tungana, try this pasted into ThisWorkbook code module... Private Sub Workbook_Open() Dim rngInfo As Variant Dim stInfo As String Dim I As Byte Dim J As Byte rngInfo = Sheet2.Range("F2:G6") For I = 1 To 5 For J = 1 To 2 stInfo = stInfo & rngInfo(I, J) & IIf(J = 1, ", ", "") Next J If I <> 5 Then Let stInfo = stInfo & Chr(10) Next I MsgBox Prompt:=stInfo, Title:="Values in Sheet2 F2:G6&qu...

Rotate text?
Office(publisher) 2K. Is there anyway to rotate a specific word in a sentence? L. Not if it's part of the sentence rather than being a separate text box. -- JoAnn Paules MVP Microsoft [Publisher] "L." <ofallon12320@yahoo1.com> wrote in message news:%23TBzesS9FHA.2616@TK2MSFTNGP15.phx.gbl... > Office(publisher) 2K. > > Is there anyway to rotate a specific word in a sentence? > > L. > > Thanks for the info. L. "JoAnn Paules [MSFT MVP]" <jl_paules@hotmail.com> wrote in message news:U46dnd7PfZ5CTBHenZ2dnUVZ_sidnZ2d@suscom....

Plain text warning/ VS2005
Whenever I add a CRecordset derived class, I get an error telling me the password for the DB is sent in "plain text" and is not secure etc. I comment that line out, but how much of a security risk is this? What can I do with my Access database to avoid this? VC 6.0 never gave this error... tia b If you are on any kind of network that anyone can look at, you are wide open for attack. Example, a University network; potentially, ever student can be running a packet sniffer and see your password. Anything that passes acros the Internet outside your local intranet is also vuln...

shading behind text
I would like to place white text within a box of gray shading. When I use the 'background' function, it shades the entire document. How do I limit the background to a line of text? thanks Ken Depending on your preference I can think of at least 3 ways, but they all may not be available to you... or instructions may vary. It depends on what version of Word you're using & you haven't even indicated whether it's Windows or Mac. Since you posted to the Mac Word group I'll assume you're using a Mac version & further that it is the most current (2008):...

automatically updating a column
I have two columns, A and B, where I manually add a number to col. A every day. Col. B is sum of all numbers from beginning in col. A. There is an easy formula for col. B, e.g., b2=a2+b1. But every day, after entering the number in col. A, I have to copy one down in col. B. How can I have that copying done automatically as soon as I make an entry in col. A? On Feb 17, 4:53 pm, "cmar" <cm...@ne.rr.com> wrote: > But every day, after entering the number in col. A, > I have to copy one down in col. B. How can I have > that copying done automatically as soon as...

How to get the text of each selection?
Hi, Dear All We can use Ctrl key and mouse to multi-select range in a document, but if somebody could tell me how to get the text of each selection? Cheers! osen Sorry, it is not really possible. See http://support.microsoft.com/?kbid=288424. The closest you can come is to use Selection.Copy, then open a new blank document and paste. The individual pieces will be separated by paragraph marks, but you can't distinguish between those and any paragraph marks that might have been in the middle of some pieces. -- Regards, Jay Freedman Microsoft Word MVP FA...