Thanks, Dave...I have not used macros before hence a little shaky about using
one any ideas on the formula

"Dave Peterson" wrote:

If there are no spaces in the values in those cells:

=substitute(trim(if(a1=0,"",a1)&" "&if(b1=0,"",b1)&" "&....)," ",", ")

But this would be a very long formula, too.

I think I'd use a user defined function.

If you want to try:

Option Explicit
Function myConCat(rng As Range) As String

Dim myRow As Range
Dim myCell As Range
Dim myStr As String
Dim mySep As String

mySep = ", "

myStr = ""
For Each myRow In rng.Rows
For Each myCell In myRow.Cells
If myCell.Value = 0 _
or mycell.value = "" Then
'skip it
Else
myStr = myStr & mySep & myCell.Value
End If
Next myCell
Next myRow

If myStr < "" Then
myStr = Mid(myStr, Len(mySep) + 1)
End If

myConCat = myStr

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=myConCat(A1:B15)




dpayne wrote:

I am looking for a formula that will find non zeros in column A and string
columns A & B, where there are a least 50 rows of information. The stringed
cells should be seperated by "," and a space. I have the following formula
but it becomes to big to fit in the cell.

=if(a1<0,a1&" "&b1&","&" ","")&if(a2<0,a2&" "&b2&","&" ","")&if(a3....etc)

I appreciate the help

--

Dave Peterson