divide a single field into multiple fields

[Guest]

i am importing data from a text file which is comma delimited. One of the
fields actually has three sets of data which are combined into one field.
For example 123456789 is one imported field. However the 123 represents a
value I want separated into another field, the 456 into another and the 789
into a third field. How does one take a value in one field and divide it
into three fields?

 

[Guest]

The first thing that you need to do is study the data. Is it always 9
characters and split up 3-3-3? If so, the job is easy using the Left, Mid,
and Right functions. However if the data is not consistant in its format, it
can be a big problem. In that case you might want to go to the source and
request the data be broken up the way that you need it.

Left("123456789", 3) gives you 123
Right("123456789", 3) returns 789
Mid("123456789", 4, 3) returns 456

Again you must really study the data and deal with any data that does not
exactly fit the mold.