Convert a text file to columes
 

I'm trying to convert a text file with multiple rows (10) of repeated data.
The "Text to Columns" function in Excel can only sort one row. Is there a way
in Excel where I can format it to convert these data into just one row?

Convert a text file to columes
 

Could you post a few lines of your text file, together with a bit more
description of what you want to achieve? Then someone might be able to
offer a solution.

Pete

A123BELLE wrote:
I'm trying to convert a text file with multiple rows (10) of repeated data.
The "Text to Columns" function in Excel can only sort one row. Is there a way
in Excel where I can format it to convert these data into just one row?

Convert a text file to columes
 

The sample text looks similar to this. There are two rows of repeated data
that I need to convert to one row so that I can sort the data in excel.

PO Number Release Line Currency Line Type Category Item# Description
_________ _______ ____ _______ ________ ________ _____ _________

Qty Billed Due Close Status Unit Price UOM Qty Ordered Qty
Received
________ _____ __________ ________ ____ __________ ___________

100619 1 USD Goods Hardware 123456 Screw
100 21-AUG-06 Open 10.17 Each 100
100
100222 1 USD Goods Chemical 345601 HOS
2 21-SEP-06 Open 500 ML 2
2
100222 2 USD Goods Hardware 567890 Nuts
1000 19-OCT-06 Open 0.09 Each 1000
1000


Thank you.

"A123BELLE" wrote:

I'm trying to convert a text file with multiple rows (10) of repeated data.
The "Text to Columns" function in Excel can only sort one row. Is there a way
in Excel where I can format it to convert these data into just one row?

Convert a text file to columes
 

I'm not sure I follow. Are you saying that your data is currently
wrapping from row 1 to row 2 and you'd like to move each wrapped row
into its own single line record?

If so, does each row wrap at the same column? Also, does each complete
record contain the same number of cells?

Max.


A123BELLE wrote:
The sample text looks similar to this. There are two rows of repeated data
that I need to convert to one row so that I can sort the data in excel.

PO Number Release Line Currency Line Type Category Item# Description
_________ _______ ____ _______ ________ ________ _____ _________

Qty Billed Due Close Status Unit Price UOM Qty Ordered Qty
Received
________ _____ __________ ________ ____ __________ ___________

100619 1 USD Goods Hardware 123456 Screw
100 21-AUG-06 Open 10.17 Each 100
100
100222 1 USD Goods Chemical 345601 HOS
2 21-SEP-06 Open 500 ML 2
2
100222 2 USD Goods Hardware 567890 Nuts
1000 19-OCT-06 Open 0.09 Each 1000
1000


Thank you.

"A123BELLE" wrote:

I'm trying to convert a text file with multiple rows (10) of repeated data.
The "Text to Columns" function in Excel can only sort one row. Is there a way
in Excel where I can format it to convert these data into just one row?

Convert a text file to columes
 

My current data are wrapping from row 1 to row 2. Some fields share the same
column, some fields don't. Each complete record contains the same number of
cells.

Does this helps?

" wrote:

I'm not sure I follow. Are you saying that your data is currently
wrapping from row 1 to row 2 and you'd like to move each wrapped row
into its own single line record?

If so, does each row wrap at the same column? Also, does each complete
record contain the same number of cells?

Max.


A123BELLE wrote:
The sample text looks similar to this. There are two rows of repeated data
that I need to convert to one row so that I can sort the data in excel.

PO Number Release Line Currency Line Type Category Item# Description
_________ _______ ____ _______ ________ ________ _____ _________

Qty Billed Due Close Status Unit Price UOM Qty Ordered Qty
Received
________ _____ __________ ________ ____ __________ ___________

100619 1 USD Goods Hardware 123456 Screw
100 21-AUG-06 Open 10.17 Each 100
100
100222 1 USD Goods Chemical 345601 HOS
2 21-SEP-06 Open 500 ML 2
2
100222 2 USD Goods Hardware 567890 Nuts
1000 19-OCT-06 Open 0.09 Each 1000
1000


Thank you.

"A123BELLE" wrote:

I'm trying to convert a text file with multiple rows (10) of repeated data.
The "Text to Columns" function in Excel can only sort one row. Is there a way
in Excel where I can format it to convert these data into just one row?

Convert a text file to columes
 

2 more questions:

1 - is every record wrapping ONLY on 2 rows?
2 - is every wrapped record on an even numbered row?

If the answer to these 2 question is Yes, the macro should be fairly
easy to write.

Max.

A123BELLE wrote:
My current data are wrapping from row 1 to row 2. Some fields share the same
column, some fields don't. Each complete record contains the same number of
cells.

Does this helps?

" wrote:

I'm not sure I follow. Are you saying that your data is currently
wrapping from row 1 to row 2 and you'd like to move each wrapped row
into its own single line record?

If so, does each row wrap at the same column? Also, does each complete
record contain the same number of cells?

Max.


A123BELLE wrote:
The sample text looks similar to this. There are two rows of repeated data
that I need to convert to one row so that I can sort the data in excel.

PO Number Release Line Currency Line Type Category Item# Description
_________ _______ ____ _______ ________ ________ _____ _________

Qty Billed Due Close Status Unit Price UOM Qty Ordered Qty
Received
________ _____ __________ ________ ____ __________ ___________

100619 1 USD Goods Hardware 123456 Screw
100 21-AUG-06 Open 10.17 Each 100
100
100222 1 USD Goods Chemical 345601 HOS
2 21-SEP-06 Open 500 ML 2
2
100222 2 USD Goods Hardware 567890 Nuts
1000 19-OCT-06 Open 0.09 Each 1000
1000


Thank you.

"A123BELLE" wrote:

I'm trying to convert a text file with multiple rows (10) of repeated data.
The "Text to Columns" function in Excel can only sort one row. Is there a way
in Excel where I can format it to convert these data into just one row?

Convert a text file to columes
 

I presume the horizontal lines are included in the data. If this file
has an extension .txt, then from within Excel if you do File | Open and
select "All files *.*" in the File Type box, you should be able to
identify the file - click Open and Excel will automatically take you
through the Data Import Wizard. Here you can specify whether you have
fixed-width fields or if they are delimited - it looks to me as if they
might be fixed-width if your sample is a copy of the first few lines,
and that might cause problems if the fields on the second row do not
have the same widths as those on the first row.

Another approach might be to rename the file to .xyz (for example) and
then to double-click this file - after a short delay Windows will tell
you that it doesn't recognise this file type, and you will be able to
specify which application you want to open it with. Choose Excel from
the list and uncheck the box which says always use this application
(unless you will have to do this frequently). Excel will open the file
with all the data in column A.

Then you could use the Data | Text-to-columns facility to split the
data into different columns, using space as the delimiter and checking
the box which says treat multiple delimiters as one - your header rows
will be slightly misaligned as you have spaces in the descriptions, but
it is fairly easy to correct these manually.

I think the first half of the first data record will appear on row 5 if
I have interpreted your sample correctly, and your data will occupy 7
cells on each row (A to G). If that is the case, then in H5 you could
enter the formula:

=A6

and copy this across to N5. To avoid having to copy this to alternate
rows, you can just copy the formulae in H5:N5 down the rows to the
bottom of your data, and then fix the values by selecting the whole
block of formulae, then <copy then Edit | Paste Special | Values
(check) then OK followed by <Esc.

In O5 you can enter this formula:

=MOD(ROW(),2)

and copy this down to the bottom of your data - this will give
alternate values of 1 and 0, indicating odd and even rows. Click the
"O" at the top of the column to highlight the complete column, then
Data | Filter | Autofilter (check), then using the pull-down list in
O1, select the value 0 - this should display only the even rows after
row 5, and these are the ones we want to delete. To do this, highlight
the first visible row (should be row 6) by clicking the row identifier,
then hold down the <Shift key and press <End once followed by
<Down-arrow then release <Shift. With this block highlighted, click
Edit | Delete Row, then use the pull-down on cell O1 again and select
"All".

Column O can now be deleted, and you should be left with your data
combined onto one row for each record. You'll have a bit of tidying up
to do for the headings, and then you can use File | Save As to save the
file as an Excel file with an appropriate name.

Hope this helps - it takes longer to describe than to do.

Pete

A123BELLE wrote:
The sample text looks similar to this. There are two rows of repeated data
that I need to convert to one row so that I can sort the data in excel.

PO Number Release Line Currency Line Type Category Item# Description
_________ _______ ____ _______ ________ ________ _____ _________

Qty Billed Due Close Status Unit Price UOM Qty Ordered Qty
Received
________ _____ __________ ________ ____ __________ ___________

100619 1 USD Goods Hardware 123456 Screw
100 21-AUG-06 Open 10.17 Each 100
100
100222 1 USD Goods Chemical 345601 HOS
2 21-SEP-06 Open 500 ML 2
2
100222 2 USD Goods Hardware 567890 Nuts
1000 19-OCT-06 Open 0.09 Each 1000
1000


Thank you.

"A123BELLE" wrote:

I'm trying to convert a text file with multiple rows (10) of repeated data.
The "Text to Columns" function in Excel can only sort one row. Is there a way
in Excel where I can format it to convert these data into just one row?