Combining IF Blanks with CONCATENATE

Adrac

Active Member
Joined
Feb 13, 2014
Messages
280
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I have a working formula but would like to add in: If the cell is blank then write in "NoGroups"

Example: Result in Z2
Code:
 =CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2)

And brings me up the result I need but would like to add NoGoups to cell Z2

I thought of
Code:
IF(ISBLANK(Z2),"No Group"),(CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2)))

But this dosent work, Can you help?
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You closed out your IF statement before the last argument.
Try:
Code:
=IF(ISBLANK(Z2),"No Group",CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2))
 
Upvote 0
I think there is another mistake, the Z2 is where the formula is and is now coming back with "0" result. Is there a way of checking all the range in the Concat and give "No Group" if its blank?
 
Upvote 0
I missed the fact that you have a circular reference going on here. Your formula is referencing the same cell it is placed in. You usually want to avoid doing that.

One way of doing what you want:
Code:
=IF(CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2)="","No Group",CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2))
 
Upvote 0
Hi,

Unless the range O2:Y2 may contain formula blanks ( "" ), I think the formula can be shortened to this, in Z2 enter:

=IF(COUNTA(O2:Y2)=11,CONCATENATE(O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2),"No Group")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top