Applying a | delimiter to separate a series of Zip codes

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

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|Etc...

Is there a formula or a way?

Thank you.

Ed.
 
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
 
Or to preserve leading 0's:

in B2 enter:
=text(A1,"00000") & "|" & text(A2,"00000")
In B3 enter:
=B2 & "|" & text(A3,"00000")
and copy down

But this still suffers the same problem that Gord asked about.
 
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.
 
Yes, I need the 20,000+ zip codes in one cell
Click to expand...

That is not possible. Even Excel 2007 has a limit of 32,767 characters per
cell. 20,000+ zip codes have more than 100,000 characters.
--ron
 
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 here:
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.)
 
Back
Top