Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delimiter in CSV files | Excel Discussion (Misc queries) | |||
How can you separate a series of numbers into jenks? | Excel Worksheet Functions | |||
Separate values in cell by delimiter | Excel Discussion (Misc queries) | |||
Applying range names to existing formulas in separate worksheets | Excel Discussion (Misc queries) | |||
Tab Delimiter File | Excel Discussion (Misc queries) |