If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Large list of numbers to concatenate
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! |
#2
|
|||
|
|||
Large list of numbers to concatenate
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 "MaggieB." wrote in message ... 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! |
#3
|
|||
|
|||
Large list of numbers to concatenate
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/comm...c-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/200...e-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! |
#4
|
|||
|
|||
Large list of numbers to concatenate
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. 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! |
Thread Tools | |
Display Modes | |
|
|