How To Imoprt Text File With No Delimiters?
 

I have a text file with over 2,000 names that look like the following:

Mr. Benoit Smith
120 Adam Street
Dorchester, MA 02124

Marcus Darbouze
1553 Blue Hill Avenue
Boston, MA 02126

American Public Health Initiatives
10 Fairway Street
Mattapan, MA 02126

Most consist of 3 lines some as many as 5. The text file has no
delimiters so that when I import/open it in Excel it becomes just one
very long column. I need to convert it or parse it into any other
database friendly importable format i.e., .dbf, .xls, .csv etc. What I
need is to convert it into a table with up to five header rows:

head1 head2 head3 head4 head5

Please help, I've been at it for way too many hours.

P.S. Please keep it simple if possible, Excel is all new to me.

TIA

How To Imoprt Text File With No Delimiters?
 

Import the file into Excel so it is in a single column.

then you can do something like this

Sub OrganizeData()
Dim lastrow as Long, rw as Long
Dim col as Long, i as Long
lastrow = cells(rows.count,1).End(xlup).Row
rw = 1
col = 5
i = 1
do
if cells(i,1).Value = "" then
rw = rw + 1
col = 5
else
cells(rw,col).Value = cells(i,1).Value
col = col + 1
end if
i = i + 1
Loop until i lastrow
End Sub

--
Regards,
Tom Ogilvy


"Sam" wrote in message
...
I have a text file with over 2,000 names that look like the following:

Mr. Benoit Smith
120 Adam Street
Dorchester, MA 02124

Marcus Darbouze
1553 Blue Hill Avenue
Boston, MA 02126

American Public Health Initiatives
10 Fairway Street
Mattapan, MA 02126

Most consist of 3 lines some as many as 5. The text file has no
delimiters so that when I import/open it in Excel it becomes just one
very long column. I need to convert it or parse it into any other
database friendly importable format i.e., .dbf, .xls, .csv etc. What I
need is to convert it into a table with up to five header rows:

head1 head2 head3 head4 head5

Please help, I've been at it for way too many hours.

P.S. Please keep it simple if possible, Excel is all new to me.

TIA

How To Imoprt Text File With No Delimiters?
 

Feel free to send the text file to me.

--
Jim Rech
Excel MVP
"Sam" wrote in message
...
|I have a text file with over 2,000 names that look like the following:
|
| Mr. Benoit Smith
| 120 Adam Street
| Dorchester, MA 02124
|
| Marcus Darbouze
| 1553 Blue Hill Avenue
| Boston, MA 02126
|
| American Public Health Initiatives
| 10 Fairway Street
| Mattapan, MA 02126
|
| Most consist of 3 lines some as many as 5. The text file has no
| delimiters so that when I import/open it in Excel it becomes just one
| very long column. I need to convert it or parse it into any other
| database friendly importable format i.e., .dbf, .xls, .csv etc. What I
| need is to convert it into a table with up to five header rows:
|
| head1 head2 head3 head4 head5
|
| Please help, I've been at it for way too many hours.
|
| P.S. Please keep it simple if possible, Excel is all new to me.
|
| TIA

How To Imoprt Text File With No Delimiters?
 

Hi Sam

I had nearly the same problem.

I solved it by having formulas to split the data across b c d e and f

Formula in b would look at row above the current row and if a was blank then
use column a so in say b2 = if (a2="",a2,"")
This will give you the starting point of each address.
Each successive column would look at column b and if it was not blank would
read the relevant line.
c2 = if(b2<"",a3,"")
d2 = if(b2<"",a4,"")
e2 = if(b2<"",a5,"")
f2 = if(b2<"",a6,"")


That should give you the addresses spread across the 5 columns. Autofilter
to exclude the blank lines and cut and paste special.

Hope this helps

Chris

"Sam" wrote in message
...
I have a text file with over 2,000 names that look like the following:

Mr. Benoit Smith
120 Adam Street
Dorchester, MA 02124

Marcus Darbouze
1553 Blue Hill Avenue
Boston, MA 02126

American Public Health Initiatives
10 Fairway Street
Mattapan, MA 02126

Most consist of 3 lines some as many as 5. The text file has no
delimiters so that when I import/open it in Excel it becomes just one
very long column. I need to convert it or parse it into any other
database friendly importable format i.e., .dbf, .xls, .csv etc. What I
need is to convert it into a table with up to five header rows:

head1 head2 head3 head4 head5

Please help, I've been at it for way too many hours.

P.S. Please keep it simple if possible, Excel is all new to me.

TIA