sorting a linked tables columns

[daltonmillion]

Through work I have a system that outputs data into an excel sheet. In one column it has:

"Handling Unit"
Which is a number assigned to a bin of parts
(ex. 82118897)
&
"Material"
Which is a number assigned to the material that resides in each handling unit.
(ex. 073945994-0000)

Both of these numbers ranges vary a lot but the consistent aspects of them are


1.) the 0 that exists in front of the Material number and not on the Handling Unit number
2.) the formatting of the Material number (hyphen followed by zeros)

I want to write a macro,query,etc. that takes this linked data and separates this column into 2 columns labeled "Handling Unit" and "Material". I am completely unaware of how to do this. Any help would be greatly appreciated! Thank you in advance.

[June7]

Do you want to retain leading 0?

Use string manipulation functions to construct fields with expressions in query.

If same number of digits before and after hyphen:

Left([Material], 9)

Right([Material], 4)

If the number of digits varies, gets complicated.