In your worksheet formula you would include CHAR(10)
-- the use of CHR(10) would be for the same but for use in Visual Basic

to make sure you have right padding
=LEFT(A1& REPT(" ",178),178)
which would not require checking if you are below or past 178 characters.
You would include the equivalent of the above in your formula.

However since your purpose is to create a text file, it would probably be better
to write the file directly with VBA macro code.

BTW, Most people would not use the concatenate worksheet formula
but instead do their concatenation like this
H4: =A4 & " " & B4 & " " & C4
instead of
H4: =CONCATENATE(A4," ", B4, " ", C4)
means exactly the same thing.:
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Mervyn Thomas" wrote in message ...
Sorry I was not understood. I am seeking to produce an excel file where
each record is in A1,A2 (one record per cell) etc and then saving the file
in text format to be able to import the records into a banking system. Each
record needs to be right padded with blanks to produce a record that is 178
characters wide. I have a user function that produces the right padding OK
but when the txt file is viewed in TEXTPAD it does not have a line feed
character in position 179 and after all the records are shown there appears
to be loads of space after the records have finished which the system does
not like.

I am building the file in VB by copying a function in one cell to the
others - it looks like:
=CONCATENATE("1ROYTEST2.TXT",TEXT(Import!$G$1,"000 00000"),TEXT(Import!$H$1,"000000"),RIGHTPAD("
",152),D9)

So how do I build your CHR(10) for example into this and is there another
one to ensure the complete file ends at the last record? Where do I find
documentation on these CHR characters?

Thanks for your patience!
Mervyn



"David McRitchie" wrote in message
...
Your question is ambiguous both in what you get and what you are
comparing
it to, and even whether it makes a difference or not to your usage.

In fact, I don't even know if you are trying to create a CSV file, text
in columns,
or just writing records i.e. HTML records..

From with VBA you can use xlCR or CHR(13) for carriage return,
xlLF or CHR(10) for line feed, cor xlCRLF or CHR(13)&CHR(10) for
CRLF.
if producing one long string.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Mervyn Thomas" wrote in message
...
I am creating a text file within excel and I do not know how to create
what
I think is needed and that is an end of line character for each line.
The
text is all in column A, one record in each cell. The text file that
excel
creates appears to be
different to the model I am using in Notepad at the end of the line.
Anyone any experience in this?