Solved How to put a comma between values from multi-formula string?

Microsoft Excel 2013 32/64-bit - license...
June 19, 2017 at 13:09:55
Specs: Windows 7
Hi, I'm using the following formula string:

=IF(Recs!B2=TRUE, "Gas", "")&IF(Recs!C2=TRUE, "Electricity", "")&IF(Recs!D2=TRUE, "Water", "")&IF(Recs!E2=TRUE, "Fuel", "")

to return the savings type for an energy-saving recommendation. In some cases, there is more than one savings type for a recommendation. For these recommendations, my current formula returns : GasElectricity; how can I have it display as: Gas, Electricity instead?


See More: How to put a comma between values from multi-formula string?

Reply ↓  Report •

#1
June 19, 2017 at 14:07:23
You can do the following add the "," within the string

For example "Gas, "

Noticw the space after the comma

=IF(Recs!B2=TRUE, "Gas, ")&IF(Recs!C2=TRUE, "Electricity, ")&IF(Recs!D2=TRUE, "Water, ")&IF(Recs!E2=TRUE, "Fuel, ")

message edited by AlwaysWillingToLearn


Reply ↓  Report •

#2
June 19, 2017 at 14:12:40
The issue with that is that for cells where there is only one savings type, it will display as "Gas, " instead of "Gas"...

Reply ↓  Report •

#3
June 19, 2017 at 20:10:22
✔ Best Answer
You can use a Helper Cell.

Place the long formula in an unused cell, either out of view or hidden or in a cell with a font color such that the result is not visible to the user. Let's say your Helper Cell is A1. The value in A1 will always end in with a "Comma Space".

Then, in the cell where you actually want to see the result, enter this:

=LEFT(A1,LEN(A1)-2)

That will strip off the Comma Space regardless of how many fuel types are returned by the long formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •
Related Solutions


Ask Question