Prev: SumIf and vlookup formula help
Next: Average If
From: MaggieB. on 3 Jun 2010 13:17
I have a list of 600+ zip codes that are curently in individual cells. I need
to turn them into one list separated by commas. Is this possible? The best
solution I can think of is
=concatenate(A1, ", ",B1)

However, is there a way to automate this instead of individually typing this
in for all 600+ cells?

Thanks!
From: Don Guillett on 3 Jun 2010 13:32
Based on your zips in a row
Sub makestring()
mr = 1
lc = Cells(mr, Columns.Count).End(xlToLeft).Column
For i = 1 To lc
mys = mys & "," & Cells(mr, i)
Next i
MsgBox Right(mys, Len(mys) - 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"MaggieB." <MaggieB.(a)discussions.microsoft.com> wrote in message
news:EA9D1A79-AA34-4EFB-A2FA-9DE56CA67DB9(a)microsoft.com...
>I have a list of 600+ zip codes that are curently in individual cells. I
>need
> to turn them into one list separated by commas. Is this possible? The best
> solution I can think of is
> =concatenate(A1, ", ",B1)
>
> However, is there a way to automate this instead of individually typing
> this
> in for all 600+ cells?
>
> Thanks!

From: Tom Hutchins on 3 Jun 2010 13:35
A similar question was asked & answered in the same newsgroup this morning.
That post was titled "Function to concatenate cells in a range":

http://www.microsoft.com/office/community/en-us/default.mspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=1179f644-f073-48e5-95cf-0064fbd2799c&mid=3ac0fe8e-7800-4b90-8f9c-06cc48cff765

Paste the function code provided by Jacob in a general VBA module in your
workbook. If you are new to user-defined functions (macros), this link to Jon
Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

"MaggieB." wrote:

> I have a list of 600+ zip codes that are curently in individual cells. I need
> to turn them into one list separated by commas. Is this possible? The best
> solution I can think of is
> =concatenate(A1, ", ",B1)
>
> However, is there a way to automate this instead of individually typing this
> in for all 600+ cells?
>
> Thanks!
From: Gord Dibben on 3 Jun 2010 18:57
Function ConCatRange(CellBlock As Range) As String

'=concatrange(range)
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))

Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function

Ignores blanks and can be used for non-contiguous ranges.


Gord Dibben MS Excel MVP

On Thu, 3 Jun 2010 10:17:08 -0700, MaggieB.
<MaggieB.(a)discussions.microsoft.com> wrote:

>I have a list of 600+ zip codes that are curently in individual cells. I need
>to turn them into one list separated by commas. Is this possible? The best
>solution I can think of is
>=concatenate(A1, ", ",B1)
>
>However, is there a way to automate this instead of individually typing this
>in for all 600+ cells?
>
>Thanks!

 | 
Pages: 1
Prev: SumIf and vlookup formula help
Next: Average If