# Concatenation issues

#### ecnewb

##### New Member
Trying to use CONCATENATE.

Goal: IF A1 and B1 >0, then concatenate, separated by a comma into C1.
If A1 or B1>0, but not both, then copy the value of A1 or B1, whichever is >0 into C1.
If A1 and B1=0 then leave C1 blank.

So far have got to: =IF(AND(A1>0, B1>0),CONCATENATE(A1,",",B1)," ")
The issue I am running into is accommodating the middle argument.

Any and all help appreciated.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Let me know if this works.
Code:
``=IF(AND(\$A\$1>0, \$B\$1>0),CONCATENATE(\$A\$1,",",\$B\$1),IF(AND(\$A\$1=0,\$B\$1>0),\$B\$1,IF(AND(\$A\$1>0,\$B\$1=0),\$A\$1,"")))``

Almost.

Goal: IF A1 and B1 >0, then concatenate, separated by a comma into C1. DONE
If A1 or B1>0, but not both, then copy the value of A1 or B1, whichever is >0 into C1. 1/2 DONE When A>0 and B=0, then A is copying to C1. BUT When A=0 and B>0, B is not copying to C.
If A1 and B1=0 then leave C1 blank. DONE

So very impressed so far. Thank you.

Almost.

Goal: IF A1 and B1 >0, then concatenate, separated by a comma into C1. DONE
If A1 or B1>0, but not both, then copy the value of A1 or B1, whichever is >0 into C1. 1/2 DONE When A>0 and B=0, then A is copying to C1. BUT When A=0 and B>0, B is not copying to C.
If A1 and B1=0 then leave C1 blank. DONE

So very impressed so far. Thank you.

Double check that. The formula works just fine for me. If it still doesn't work, do the following:

1. Enter 0 into cell A1 and 1 into Cell B1. Tell me what output you get.
2. Enter 1 into cell A1 and 0 into Cell B1. Tell me what output you get.

This will help me understand what went wrong in the formula, though I have no wrong numbers when I use it myself.

The formula below is Red/Bolded where B1 should be output if A=0 and B>0, as you said is not working. It shouldn't cause any errors.
=IF(AND(\$A\$1>0, \$B\$1>0),CONCATENATE(\$A\$1,",",\$B\$1),IF(AND(\$A\$1=0,\$B\$1>0),\$B\$1,IF(AND(\$A\$1>0,\$B\$1=0),\$A\$1,"")))

Last edited:
Trying to use CONCATENATE.

Goal: IF A1 and B1 >0, then concatenate, separated by a comma into C1.
If A1 or B1>0, but not both, then copy the value of A1 or B1, whichever is >0 into C1.
If A1 and B1=0 then leave C1 blank...
Give this formula a try...

=MID(IF(A1="","",","&A1)&IF(B1="","",","&B1),2,99)

The following may cover some edge cases in your data.

Code:
``=TEXT(A1,"0;;;")&REPT(",",TEXT(A1,"\1;\0;\0;\0")*TEXT(B1,"\1;\0;\0;\0"))&TEXT(B1,"0;;;")``

It worked Matt. Thank you so very much!

It worked Matt. Thank you so very much!
I made a mistake in the formula I posted in Message #5, here is the corrected version which you may want to consider given its simplicity...

=MID(IF(A1<=0,"",","&A1)&IF(B1<=0,"",","&B1),2,99)

Replies
5
Views
398
Replies
3
Views
154
Replies
21
Views
791
Replies
4
Views
344
Replies
5
Views
840

### Forum statistics

1,196,487
Messages
6,015,500
Members
441,898
Latest member
kofafa ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back