logical test and concatenate(string)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
You can save some characters if you use

=IF(A1=0,"",A1&" "&B1&", ")&IF(A2=0,...


Using an unused, or helper, column to concatenate, then concatenating
the non-blanks in that column may be easier all-around.
 
Dave, when I repeat the formula over and over again to get to the 50th
row...there are to many characters to fit into one cell. Thanks
 
Thanks, Dave...I have not used macros before hence a little shaky about using
one any ideas on the formula
 
Would this work?

assuming you are currently placing the formula in cell C1:
Cell C1: =if(a1<>0,a1&" "&b1&","&" ","")
Cell C2: =C1&if(a2<>0,a2&" "&b2&","&" ","")
Cell C3: =C2&if(a3<>0,a3&" "&b3&","&" ","")
etc...
copy this down to the last row, data in column C of that row should contain
everything...maybe?
 
Back
Top