logical test and concatenate(string)
 

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

logical test and concatenate(string)
 

Formulas don't have to fit in the width of a cell.

What's your question?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"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

logical test and concatenate(string)
 

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

logical test and concatenate(string)
 

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.

In article ,
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

logical test and concatenate(string)
 

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

"Dave F" wrote:

Formulas don't have to fit in the width of a cell.

What's your question?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"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

logical test and concatenate(string)
 

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

logical test and concatenate(string)
 

Just the first portion of my response.

Or JE's or Dave F's....

dpayne wrote:

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


--

Dave Peterson

logical test and concatenate(string)
 

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?

"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