Convert Text files to Excel

[Sigh]

HI,
I got a large text files contain the address
information. How can I convert to the Excel file. Here's
my samples of the text file.

AAA XXXX STORES LTD.
1370 SONY PLACE
Sanigo, CANADA
R3C 3C3

When I convert to excel. It only import the file to only
one column. I want to convert it to each line from text to
each column in Excel.

Any help would be very appreciated

 

[Myrna Larson]

If all the data begins in row 1, and all addresses consist of 4 rows, you could put this formula
in B1: =OFFSET($A$1,(ROW()-1)*4+COLUMN()-2,0). Copy that across through E1. You should see the
first record in B1:E1. Copy the formulas down until they begin to return blanks.

Then convert the formula to their values by copying the cells, then Edit/Paste Special and
select the Values option.

If the number of rows for each entry is not constant, you may need to use a macro. You'll also
need to understand the logic that determines when a new record begins.

 

[Justin]

-----Original Message-----

HI,
I got a large text files contain the address
information. How can I convert to the Excel file. Here's
my samples of the text file.

AAA XXXX STORES LTD.
1370 SONY PLACE
Sanigo, CANADA
R3C 3C3

When I convert to excel. It only import the file to only
one column. I want to convert it to each line from text to
each column in Excel.

Any help would be very appreciated
.

One initial question - Does each address have the same
number of lines in the text file? If it has been exported
from a database it probably has. If it does then I will
send you a macro to reformat it, if not then I draw a
blank.

 

[Sigh]

Thanks or the Function. It works for the Constant line of
text file. But each entry of rw is not constant. Some of
the text file has 5 line and some have 3 or 6 line. Can
It's anyway to fix this?

Thanks

-----Original Message-----
If all the data begins in row 1, and all addresses

consist of 4 rows, you could put this formula

in B1: =OFFSET($A$1,(ROW()-1)*4+COLUMN()-2,0). Copy that

across through E1. You should see the

first record in B1:E1. Copy the formulas down until they begin to return blanks.

Then convert the formula to their values by copying the

cells, then Edit/Paste Special and

select the Values option.

If the number of rows for each entry is not constant, you

may need to use a macro. You'll also

need to understand the logic that determines when a new record begins.

 

[Ed]

Would it help to use a macro in Word first to change your addresses from

AAA XXXX STORES LTD.

1370 SONY PLACE

Sanigo, CANADA

R3C 3C3



to

AAA XXXX STORES LTD. 1370 SONY PLACE Sanigo, CANADA R3C 3C3



so everything is all on one line, and then try to import it?



Or, can you go back to the database people who give you this text file and
ask them to modify their report to save as an Excel-compatible file?



Ed

Thanks or the Function. It works for the Constant line of
text file. But each entry of rw is not constant. Some of
the text file has 5 line and some have 3 or 6 line. Can
It's anyway to fix this?

Thanks

-----Original Message-----
If all the data begins in row 1, and all addresses

consist of 4 rows, you could put this formula

in B1: =OFFSET($A$1,(ROW()-1)*4+COLUMN()-2,0). Copy that

across through E1. You should see the

first record in B1:E1. Copy the formulas down until they begin to return blanks.

Then convert the formula to their values by copying the

cells, then Edit/Paste Special and

select the Values option.

If the number of rows for each entry is not constant, you

may need to use a macro. You'll also

 

[Jim Bohnet]

Once it is converted and sitting in a single column, you
can convert each address to columns by highlighting the
address and then using paste special and checking
transpose to make it go across columns. If you have a lot
of addresses this would be pretty time consuming, but you
could write a simple macro to speed up the repetition.
It's not elegant, but it should get you the result you're
looking for.

 

[On Film]

May not be the most elegant approach but several elementary steps can
accomplish your result. Assuming that your data is ALWAYS 4 rows:

1. Put your data in Col B
2. Fill Col A with sequential numbers
3. In C1: =if(a1/4=.25,b2,"")
4. In D1 =if(a1/4=.25,b3,"")
5. In E1: =if(a1/4=.25,b4,"")

6. Copy C1,D1,E1 down length of data in Col B.
7. Select All
8. Paste Special Values
9. Data Filter on Col E
10. Select Blank
11. Delete Blank Rows
12. Remove Filter
13. Delete Col A
14. Your data is now is Col A, B, C, D