Here's an EASY one for you, not me :(

OK I have zipcodes in column A (a1-a700) and I would like to put all into B1 separated by a ",".

so that B1 will have 700 zipcodes all separated by a comma

what's the formula?

Gracias in advance
Mark

On Wed, 26 Oct 2005 22:57:51 -0500, barbierim
<[email protected]> wrote:

>
>OK I have zipcodes in column A (a1-a700) and I would like to put all
>into B1 separated by a ",".
>
>so that B1 will have 700 zipcodes all separated by a comma
>
>what's the formula?
>
>Gracias in advance
>Mark

You need a UDF.

<alt><F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window. Then
Insert/Module and paste the code below into the window that opens.

Return to your worksheet and enter the formula:

B1: =concatssn(A1:A700)


==================================
Option Explicit

Function ConCatSSN(rg) As String
Dim c As Variant
For Each c In rg
ConCatSSN = ConCatSSN & ", " & c.Text
Next
ConCatSSN = Replace(ConCatSSN, ", ", "", , 1)
End Function
==========================


--ron

i don't know if there is any direct function for this....
i suggest you to use VBA..

Function Concate(x)
For Each b In x.Cells
a = a & b & ", "
Next
Concate = Left(a, Len(a) - 1)
End Function

just type in B1:
=Concate(a1:a700)


"barbierim" wrote:

>
> OK I have zipcodes in column A (a1-a700) and I would like to put all
> into B1 separated by a ",".
>
> so that B1 will have 700 zipcodes all separated by a comma
>
> what's the formula?
>
> Gracias in advance
> Mark
>
>
> --
> barbierim
> ------------------------------------------------------------------------
> barbierim's Profile: http://www.excelforum.com/member.php...o&userid=28381
> View this thread: http://www.excelforum.com/showthread...hreadid=479689
>
>

Originally Posted by Ron Rosenfeld

On Wed, 26 Oct 2005 22:57:51 -0500, barbierim
<[email protected]> wrote:

>
>OK I have zipcodes in column A (a1-a700) and I would like to put all
>into B1 separated by a ",".
>
>so that B1 will have 700 zipcodes all separated by a comma
>
>what's the formula?
>
>Gracias in advance
>Mark

You need a UDF.

<alt><F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window. Then
Insert/Module and paste the code below into the window that opens.

Return to your worksheet and enter the formula:

B1: =concatssn(A1:A700)


==================================
Option Explicit

Function ConCatSSN(rg) As String
Dim c As Variant
For Each c In rg
ConCatSSN = ConCatSSN & ", " & c.Text
Next
ConCatSSN = Replace(ConCatSSN, ", ", "", , 1)
End Function
==========================


--ron

Worked like a charm, Thanks
MB

On Thu, 27 Oct 2005 17:09:49 -0500, barbierim
<[email protected]> wrote:

>Worked like a charm, Thanks
>MB
>
>
>--
>barbierim

You're welcome. Thank you for the feedback.

--ron

as luck would have it I needed to do this again today and I was able to find my question/answer from last year. amazing................