I get a cleared checks file from the bank every day

The length of the file is constant. The number of rows might change
depending on the number of checks cleared but the columns remain constant

I want to get certain data out of the file every day -basically the check no
and the amount from the file using a macro and put it in the adjacent
columns e.g. Coolum B and C

Eg the file I get is below

00031901000013500000986010000018278091007
00031901000013500000986080000025185090407

I want to create a macro that will take out the check no ( position 20 to
26) and the
amount and put in a decimal place ( position 27 to 32 then put decimal place
and then position 33 to 34)

E.g. in the file above I would want the macro to
Colum A ( File from bank copied into Colum A)
Colum B Column Even better would be if it
could take out the leading zeros
00031901000013500000986010000018278091007
098601 00000182.80
00031901000013500000986080000025185090407 098608
00000251.85



As I am a novice I would really appreciate your help on this.

Thanks

Sam

After you have those long text string of digits in column A, you can record a
macro when you do Data|Text to columns.

Select fixed width and parse the input string the way you like.

Stop recording the macro and you'll have the code.



Sam Commar wrote:

I get a cleared checks file from the bank every day

The length of the file is constant. The number of rows might change
depending on the number of checks cleared but the columns remain constant

I want to get certain data out of the file every day -basically the check no
and the amount from the file using a macro and put it in the adjacent
columns e.g. Coolum B and C

Eg the file I get is below

00031901000013500000986010000018278091007
00031901000013500000986080000025185090407

I want to create a macro that will take out the check no ( position 20 to
26) and the
amount and put in a decimal place ( position 27 to 32 then put decimal place
and then position 33 to 34)

E.g. in the file above I would want the macro to
Colum A ( File from bank copied into Colum A)
Colum B Column Even better would be if it
could take out the leading zeros
00031901000013500000986010000018278091007
098601 00000182.80
00031901000013500000986080000025185090407 098608
00000251.85

As I am a novice I would really appreciate your help on this.

Thanks

Sam



--

Dave Peterson

Thanks Dave

The only thing is that the number of rows is different every time so I
wonder if the macro will accommodate that part.

Thanks

"Dave Peterson" wrote in message
...
After you have those long text string of digits in column A, you can
record a
macro when you do Data|Text to columns.

Select fixed width and parse the input string the way you like.

Stop recording the macro and you'll have the code.



Sam Commar wrote:

I get a cleared checks file from the bank every day

The length of the file is constant. The number of rows might change
depending on the number of checks cleared but the columns remain constant

I want to get certain data out of the file every day -basically the check
no
and the amount from the file using a macro and put it in the adjacent
columns e.g. Coolum B and C

Eg the file I get is below

00031901000013500000986010000018278091007
00031901000013500000986080000025185090407

I want to create a macro that will take out the check no ( position 20
to
26) and the
amount and put in a decimal place ( position 27 to 32 then put decimal
place
and then position 33 to 34)

E.g. in the file above I would want the macro to
Colum A ( File from bank copied into Colum A)
Colum B Column Even better would be if
it
could take out the leading zeros
00031901000013500000986010000018278091007
098601 00000182.80
00031901000013500000986080000025185090407
098608
00000251.85

As I am a novice I would really appreciate your help on this.

Thanks

Sam



--

Dave Peterson

Can you select all of column A and run the macro against that?

Sam Commar wrote:

Thanks Dave

The only thing is that the number of rows is different every time so I
wonder if the macro will accommodate that part.

Thanks

"Dave Peterson" wrote in message
...
After you have those long text string of digits in column A, you can
record a
macro when you do Data|Text to columns.

Select fixed width and parse the input string the way you like.

Stop recording the macro and you'll have the code.



Sam Commar wrote:

I get a cleared checks file from the bank every day

The length of the file is constant. The number of rows might change
depending on the number of checks cleared but the columns remain constant

I want to get certain data out of the file every day -basically the check
no
and the amount from the file using a macro and put it in the adjacent
columns e.g. Coolum B and C

Eg the file I get is below

00031901000013500000986010000018278091007
00031901000013500000986080000025185090407

I want to create a macro that will take out the check no ( position 20
to
26) and the
amount and put in a decimal place ( position 27 to 32 then put decimal
place
and then position 33 to 34)

E.g. in the file above I would want the macro to
Colum A ( File from bank copied into Colum A)
Colum B Column Even better would be if
it
could take out the leading zeros
00031901000013500000986010000018278091007
098601 00000182.80
00031901000013500000986080000025185090407
098608
00000251.85

As I am a novice I would really appreciate your help on this.

Thanks

Sam



--

Dave Peterson

--

Dave Peterson