Create a few (5000 at a time) groups of these formulas that Gary's Student
suggested and then you can merge them onto one line in the text file.

Or maybe you could use a macro:

Option Explicit
Sub testme01()

Dim iRow As Long

Close #1
Open "c:\textfile.txt" For Output As #1
With ActiveSheet
For iRow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
Print #1, Format(.Cells(iRow, "A").Value, "00000") & "|";
Next iRow
End With

Close #1

End Sub



If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)



Ed wrote:

Thank you for your response.

Yes, I need the 20,000+ zip codes in one cell and ultimately transfer them
in a *.txt file with the pipe delimiter.

Ed.

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You want 20,000 Zip Codes in one cell?

Excel 2003 and earlier won't handle that much text in a cell.

32767 characters is the limit..

Not sure if 2007 will do the job.

Or do you just want to create a *.txt file with the pipe delimiter?

See Chip Pearson's site for code.

http://www.cpearson.com/excel/imptext.aspx


Gord Dibben MS Excel MVP


On Mon, 12 Jan 2009 16:09:48 -0800, "Ed" wrote:

Hello,

Using Excel 2002, I have a list of Zip codes (approx. 20,000) in the
following format:

01005
01007
01008
01010
01011
01012
01026
01029
01031
01032
01033
01034
01035
01037
01038
01039
Etc..

I need to separate them by a | delimiter as follows:

01005|01007|01008|01010|01011|01012|01026|01029| 01031|01032|01033|01034|01035|01037|01038|01039|Et c...

Is there a formula or a way?

Thank you.

Ed.




--

Dave Peterson