How To Imoprt Text File With No Delimiters?

[Sam]

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

 

[Tom Ogilvy]

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

 

[Jim Rech]

Feel free to send the text file to me.

--
Jim Rech
Excel MVP
|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

 

[Chris Ferguson]

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