Concatenate a string when cells are not zero

[b w]

I'd like to know how to concatenate when the value of the B cells are
non-zero as follows:

="The result is as follows...Detail: " & A1 & B1 & A2 & B2 & A3 & B3
&...Ax & Bx & "Total=" & Text(Sum(B1:Bx),"$#,##0.00"

If a B cell value is zero, then don't concatenate with the corresponding
A cell.

The result should be something like this:
Result is as follows...Detail: Hotel=$150.00, Gasoline=$75.00,
Total=$225.00

Thanks,
Bernie

 

[dok112]

are you doing this via a macro or in-cell?

if your doing it in-cell, then use an if equation.

=if(B1="0","0",concatenate(......))

if your doing macro, I'll have to think about it for a minute. If it
is a macro, could you include more info, input, variables, etc...

 

[b w]

Thanks for the reply.

I'm trying to do this in-cell, but your solution indicates that I didn't
explain myself very well.

If B1 is zero, then your expression works, but I want to do this for ALL
B cells that are zero. An IF statement wouldn't be too practical if I
were looking at more than a few values of B.

As an example, If B1 and B2 and B3 are NOT zero, then my equation would
be =(A1 & B1) & (A2 & B2) & (A3 & B3).

But if B2 was Zero, then the equation would be:
=(A1 & B1) & (A3 & B3).

I was thinking that maybe an Array Formula would be the solution, but I
don't know, which is why I have posed the problem.

Thanks,
Bernie

 

[Rowan Drummond]

Maybe with a UDF:

Function Tot(Descriptions As Range, Costs As Range)
Dim cell As Range
If Descriptions.Columns.Count > 1 Or _
Costs.Columns.Count > 1 Then
Tot = CVErr(xlErrRef)
ElseIf Descriptions.Rows.Count <> Costs.Rows.Count Then
Tot = CVErr(xlErrNA)
Else
For Each cell In Costs
If cell.Value <> 0 Then
Tot = Tot & cell.Offset(0, -1).Value & _
"=$" & cell.Value & ", "
End If
Next cell
Tot = "Detail: " & Tot & "Total=$" & Application. _
WorksheetFunction.Sum(Costs)
End If
End Function

Enter in a cell = tot(A1:A5,B1:B5) where column A has the descriptions
and column B the costs.

Hope this helps
Rowan

 

[Tom Ogilvy]

Concatenation doesn't work with array formulas although Laraunt Longre has a
free addin that offers functions that support it.

You want the
Morefunc.xll update 18/10/2005

on the English page.


If you just want a formula, it isn't too bad

If(sum(B1:B3)=0,"",if(B1=0,"",A1 & "=" & Text(B1,"$#,##0.00) & ", ") &
if(B2=0,"",A2&"="&Text(B2,"$#,##0.00)&", ") &
if(B3=0,"",A3&"="&Text(B2,"$#,##0.00)&", "))

Have to play with it to eliminate an ending comma.