Hi Rick,
Thanks for the prompt reply.
Sorry. Data starts from Col C as you have pointed out. It was my
mistake.

Since we import the data, other columns are blank. I need to only
export the columns (digit count shown) as follows:
Col C 1-9 5894 – should display as 5894(and 5 spaces) – total
9
Col F 10-19 Invoice number 12345678 – should display as
001234567 – total 10
Col H 20-27 Invoice date 21.02.2008 should display as 20080221 –
total 8
Col I 28-35 Payment date 21.02.2008 should display as 20080221
– total 8
Col K 36-50 Payment amount, leading zeros, 2 decimals, no
decimal sign – total 15
Col L 51-55 Currency code – EUR, should display EUR(and 2
spaces) – total 5
Col Q 56-66 BIC code bank, with or without branch – total 11

Thanks

On Jul 20, 8:34*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Can you clarify a couple of things? You say your data is in the range
D10:Q(20000+rows), but your field description shows the first column as C,
not D... which is it? If C is correct, what about column's D and E; if C
should have been D, then what about column E? I added up all the "data" you
laid out and get 63 characters, not the 66 you said would be there... if 66
is correct, and assuming I did not miscount, where do the additional 3
spaces go... in front or at the back of the 66 character record?

Rick

"Rushna" wrote in message

...
Hello All,
I am using Excel 2003 and have data in Range D10:Q(20,000+ rows).
I wish to export the all active rows in the range in the Excel file
to a text file (Notepad).

Details are as follow:

Col C (currently 4 digits) – to be exported (total 9 digits) with
trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank)
(blank)(blank)
Col F (currently 8 digits) – to be exported (total 10 digits) with two
leading blanks (eg 12345678 should show as 0012345678)
Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221)
Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221)
Col K (currently -43,413.20) – to be exported as total 15 digits with
leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should
display as 000000004341320 and -1,234.23 should display as
000000000123423 etc)
Col L – to be exported with two trailing blanks (currently EUR –
should display as EUR(blank)(blank).
Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is
blank nothing should be exported

The output on each line in the text file should be a total width of 66
(including spaces) from various Columns in Excel as explained above
and displayed as follows:

1234(blank)(blank)(blank)(blank)
(blank)00123456782008022120080221000000004341320EU R(blank)
(blank)AABBCCDDXXX (this counts to 66 including blank spaces)

Thanks in advance.

Rushna