Applying a | delimiter to separate a series of Zip codes
 

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|01 031|01032|01033|01034|01035|01037|01038|01039|Etc. ..

Is there a formula or a way?

Thank you.

Ed.

Applying a | delimiter to separate a series of Zip codes
 

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|0 1031|01032|01033|01034|01035|01037|01038|01039|Etc ...

Is there a formula or a way?

Thank you.

Ed.

Applying a | delimiter to separate a series of Zip codes
 

in B2 enter:
=A1 & "|" & A2
In B3 enter:
=B2 & "|" & A3 and copy down

--
Gary''s Student - gsnu200826


"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|01 031|01032|01033|01034|01035|01037|01038|01039|Etc. ..

Is there a formula or a way?

Thank you.

Ed.

Applying a | delimiter to separate a series of Zip codes
 

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.

Gary''s Student wrote:

in B2 enter:
=A1 & "|" & A2
In B3 enter:
=B2 & "|" & A3 and copy down

--
Gary''s Student - gsnu200826

"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|01 031|01032|01033|01034|01035|01037|01038|01039|Etc. ..

Is there a formula or a way?

Thank you.

Ed.




--

Dave Peterson

Applying a | delimiter to separate a series of Zip codes
 

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.

Applying a | delimiter to separate a series of Zip codes
 

On Mon, 12 Jan 2009 17:06:23 -0800, "Ed" wrote:

Yes, I need the 20,000+ zip codes in one cell

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

Applying a | delimiter to separate a series of Zip codes
 

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

Applying a | delimiter to separate a series of Zip codes
 

Thank you all so much. I really appreciate all your great help!

Ed.


"Dave Peterson" wrote in message
...
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