Import data format, need help

[Guest]

I have about 5,000 Internet address + ports in a .txt file, e.g:
"127.0.0.1:80 127.0.0.20:80
172.9.34.6:3128 162.168.0.4:8080 12.5.4.3:80..."

As you can see, the delimiter for each entry is a "space(s) and sometimes
carriage return."

The format I want is for the data to be in columns and as many rows as
required to complete the operation.

When I try, Excel imports okay, uses the delimiters correctly, fills up
sheet #1 with data, down to about row 32, then moves to the next column,
until all columns are filled.

A message pops up that says that page is filled and to continue, I need to
use the Import Wizard, tell it where to start and it will begin a new sheet.
I keep doing this until all the data is put on separate sheets.

What I want in the formating is this:

Option 1
1) have the delimited data go only into the columns that I can see on my
screen.

That would be about, column "J" or so. I don't want to have to scroll
across all the columns to see the data.

and

2) rather than manually use the wizard to make a new Sheet after the 32
rows? or whatever, I'd like Excel to continue to put the data in columns
(that are on the screen), down to as many rows as each Sheet can hold.

(Instead, it looks like it is giving me the #of rows that will fit on a
page, regardless if the number of columns it has filled far exceed the size
of the page.)
----

Option 2: Using the delimiters, import all the data into only one column of
as many rows as can be placed on one sheet. then move to the next sheet,
using a single column.
_______

Are there ways to easily do either of these?

Thaks,
Bob

 

[Guest]

Essentially this is a job for VBA coding to read the file, parse the entries
and place them in the order and in the ranges you desire and automatically
insert new sheets when required. Pretty much any other solution involves a
lot of hand-eye coordination.

Before even beginning the effort you need to very clearly define exactly
what constitutes the area of each worksheet you want data entered into. A
generalized statement like "put the data in columns that are on the screen"
is undefinable - different screen resolutions display different columns and
different rows. I personally don't know of any way of telling if a given
column/row is currently being displayed on the video at any given time.