I link a text file with fixed width in the following format:
___NO__DESCRIPTION__ITEM__ ITE_DESC
1_1____Pens _________12 ____Blue
2_______________235 ___Red
3_______________365 ___Green
4_23___Pencils ______15 ____HB
5_______________2654 __B2
*
I want row 2,3,5 to fill the NUMBER and DESCRIPTION fields automatically. It should look like this:
____NO__DESCRIPTION __ITEM __ITE_DESC
1_ 1___ Pens__________ 12 _____Blue
2_1 ___Pens __________235 ____Red
3_1 ___Pens __________365 ____Green
4_23 __Pencils ________15 _____HB
5_23 __Pencils ________2654 ___B2
*
I want this to happen automatically in a query please.

To do this with the minimum amount of code is to import it into a temp table and run update queries to fill in the blanks. However, you only need the codes for each item, not the description as well.

Alternatively is to get the text file to be populated befroe it comes to you.

I do not know how to run an update query as suggested. I will not be able to change the text file before linking it.

If you do not know how to run an update query then it is fruitless taking you down the VBA route, which is the most favoured route for this typpe of problem. Maybe someone else can step in here?