Text to Columns Question

D

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
==============
 
R

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
==============
Click to expand...

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
 
D

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
==============
 
R

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
Click to expand...

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
 
P

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
 
D

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.
 
R

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.
Click to expand...

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
 
D

Dennis

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

Ron Rosenfeld

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

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