Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- barbierim ------------------------------------------------------------------------ barbierim's Profile: http://www.excelforum.com/member.php...o&userid=28381 View this thread: http://www.excelforum.com/showthread...hreadid=479689 |
#2
|
|||
|
|||
Here's an EASY one for you, not me :(
On Wed, 26 Oct 2005 22:57:51 -0500, 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 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 |
#3
|
|||
|
|||
Here's an EASY one for you, not me :(
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 |
#4
|
|||
|
|||
Here's an EASY one for you, not me :(
Ron Rosenfeld Wrote: On Wed, 26 Oct 2005 22:57:51 -0500, 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 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 -- barbierim ------------------------------------------------------------------------ barbierim's Profile: http://www.excelforum.com/member.php...o&userid=28381 View this thread: http://www.excelforum.com/showthread...hreadid=479689 |
#5
|
|||
|
|||
Here's an EASY one for you, not me :(
On Thu, 27 Oct 2005 17:09:49 -0500, barbierim
wrote: Worked like a charm, Thanks MB -- barbierim You're welcome. Thank you for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stack multiple columns into one column... is there an easy way? | Excel Discussion (Misc queries) | |||
OsCommerce - Easy Populate Script - CSV/TXT Conversion Problem. | Excel Discussion (Misc queries) | |||
Is there an easy way to create a calendar look up table in excel | Excel Discussion (Misc queries) | |||
new user with easy question? not easy for me | New Users to Excel | |||
is there an easy way to move parts of cells around | Excel Discussion (Misc queries) |