Columns

[GARY]

I have a text-file with 10,000 rows. (Each row contains an
item-number, a name, an address and an amount).

I used a text-editor (TEXTPAD) to move the item-numbers, names,
addresses and amounts to varying positions. (Each field is separated
by many spaces).

For example:

In the first row, the item-number may start in position 1 and end in 8.
In the second row, the item-number may start in 32 and end in 62
In the third row, the item-number may start in 40 and end in 50.
In the fourth row, the item-number may start in 18 and end in 75.

(A similar scenario applies to the names, addresses and amounts).


Now, when I open the text-file in Excel, I click on 'fixed-width'.
Then, I'm asked to create "break lines" to separate each field into
columns.

For the 10,000 rows, how can I determine the maximum width (i.e., the
first and the last positions) of each field?

 

[David Biddulph]

You haven't got "fixed width" fields, so that option won't work. You might
try "delimited" with space as separator, but that would split fields with
spaces in them (such as address lines). You may be better off going back to
the text file and using an editor to convert multiple spaces to a delimiting
character (perhaps % or some character that isn't included in your file, and
then use that as the delimiter, rather than space). You may also want to
replace %space by % before importing, to avoid importing extraneous leading
spaces into your Excel fields.