Break out data contained in one column

A

Aaron Neunz

I have an Access table with eight columns and 604 Records.

I need to pull out specific sections of data in the DIST column to create
several other columns.

What is the easiest way to accomplish this?
 
A

Aaron Neunz

Yes thank you for the reply.
Now the data contained in the DIST column is this:

CN17SN28HS44AKRO AKRO CCN4AKSCAKRO AK01METR ED07AP09

I need separate columns (it doesn't matter about the column names) like
this:
CN17, SN28, HS44, AKRO, AKRO, CCN4, AKSC, AKRO, AK01, METR, ED07, AP09

Thanks for the help
 
L

Lynn Trapp

Oh! That's going to be fun. Well, if we can assume that all the data will be
the same length with the spaces always appearing in the same places, you can
follow this process.

To get the first column do this
Left("CN17SN28HS44AKRO AKRO CCN4AKSCAKRO AK01METR ED07AP09",4)

For the Other columns,
Column2: Mid("CN17SN28HS44AKRO AKRO CCN4AKSCAKRO AK01METR
ED07AP09",5,4)
Column3: Mid("CN17SN28HS44AKRO AKRO CCN4AKSCAKRO AK01METR
ED07AP09",9,4)
Column4: Mid("CN17SN28HS44AKRO AKRO CCN4AKSCAKRO AK01METR
ED07AP09",13,4)

etc., etc. until you get all your columns. If the records have different
lengths and the spaces are in different locations, then you are going to
find it more difficult.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
A

Aaron Neunz

Well sure enough, all of the data will NOT be the same length with the
spaces NOT always appearing in the same places.

Thank You
 
Top