Text to Columns Question

[Dennis]

I have 6 fields of data all in column A but each row the data is spaced out
differently, not even close. The fields themselves do not have any spaces but
obviously there various spacing beteen the fields. The first feild is flush
left but all the others are all over the map. Is there a way I can prepare the
data so it's sumwhat lined lined up enough to get a accurate Text to Columns
without hacking off some text in some rows? It's 17k+ rows.

TIA Dennis
==============

 

[Ron Rosenfeld]

I have 6 fields of data all in column A but each row the data is spaced out
differently, not even close. The fields themselves do not have any spaces but
obviously there various spacing beteen the fields. The first feild is flush
left but all the others are all over the map. Is there a way I can prepare the
data so it's sumwhat lined lined up enough to get a accurate Text to Columns
without hacking off some text in some rows? It's 17k+ rows.

TIA Dennis
==============

If you construct a rule (or set of rules) to define where one field ends and
the next begins, I'm sure we could construct a macro or formula solution to
split each record into columns.


--ron

 

[Dennis]

Field 1 is 24-60 characters long. I would like field 2 to start 80 characters
from position 1 of field 1
Field 2 is 4- 11 characters long I would like field 3 to start 20 characters
from position 1 of field 2
Field 3 is 1-6 characters long I would like field 4 to start 20 characters
from position 1 of field 3
Field 4 is 1-6 characters long I would like field 5 to start 20 characters
from position 1 of field 4
Field 5 is 8 characters long I would like field 6 to start 20 characters from
position 1 of field 5
Field 6 is 8 characters long

Thanks for the help Ron, I hope this is the info needed.

Dennis
==============

 

[Ron Rosenfeld]

Field 1 is 24-60 characters long. I would like field 2 to start 80 characters
from position 1 of field 1
Field 2 is 4- 11 characters long I would like field 3 to start 20 characters
from position 1 of field 2
Field 3 is 1-6 characters long I would like field 4 to start 20 characters
from position 1 of field 3
Field 4 is 1-6 characters long I would like field 5 to start 20 characters
from position 1 of field 4
Field 5 is 8 characters long I would like field 6 to start 20 characters from
position 1 of field 5
Field 6 is 8 characters long

Thanks for the help Ron, I hope this is the info needed.

Dennis

Since you have the starting point of each field, it should be trivial to use
the built-in text functions to parse it out.

Or you could use the Data/Text-to-Columns wizard and use a fixed width for each
field -- i.e. 79 characters for field 1 and so forth since each field, from
your description, will always start at a fixed point.

For formulas:

Field1: =trim(left(record,60))
Field2: =trim(mid(record,80,11))
Field3: =trim(mid(record,100,6))

and so forth.

--ron

 

[Pete]

Ron's suggestions are sound.

One way to visualize the data correctly (if you are ever in this
situation again) is to change to a non-proportional font. Highlight the
column and choose a font such as Courier with a size suitable to fit
all the characters on screen.

Pete

 

[Dennis]

Im missing something. If I use the wizard the text text gets sliced up because
it some rows line up with other rows. I need to get bigger spacing so I can
put the divider lines in.

 

[Ron Rosenfeld]

Im missing something. If I use the wizard the text text gets sliced up because
it some rows line up with other rows. I need to get bigger spacing so I can
put the divider lines in.

Are you using a non-proportional font? Something like Courier or Courier New?

If the data is as you have described it, things should line up.

You can still use the formulas instead.

If the data is not how you described it, you are going to have to describe how
you can discern the dividing point between each field.


--ron

 

[Dennis]

Thanx Ron and Pete, the courier font did it!!! I appreciate the help and
patience.
Dennis
============

 

[Ron Rosenfeld]

Thanx Ron and Pete, the courier font did it!!! I appreciate the help and
patience.
Dennis
============

Glad you got it worked out! Thanks for the feedback.
--ron