Combining concatenate and if in Excel formulas

Question

How would I add the IF(ISBLANK formula to this formula

=CONCATENATE(TEXT('Unapplied Report'!A5,"0000"),TEXT('Unapplied Report'!C5,"000"),TEXT('Unapplied Report'!D5,"0000"))

without getting any errors. I've tried it a couple times and just get formula error messages.

Are you trying to check the result of the concatenate for blankness, or each of the three sub-elements? — Maple
– Maple, Commented Aug 14, 2013 at 15:38
basically the link pulls cells from 3 columns in sheet 1 into sheet 2( using the concatenate and text formula) however some of those cells in sheet 1 are blank but return "00000000" because of my use of the TEXT formula. So I want it to show a blank instead of the 000000. if that makes any sense — rah
– rah, Commented Aug 14, 2013 at 16:00
So, if A5 is not blank, C5 is blank and D5 is not blank, it should concatenate only A5 and D5? — Jerry
– Jerry, Commented Aug 14, 2013 at 16:01

Jerry · Accepted Answer · 2013-08-14 16:18:09Z

8

Maybe you're looking for this instead?

=CONCATENATE(IF(ISBLANK('Unapplied Report'!A5),"",TEXT('Unapplied Report'!A5,"0000")),
             IF(ISBLANK('Unapplied Report'!C5),"",TEXT('Unapplied Report'!C5,"000")),
             IF(ISBLANK('Unapplied Report'!D5),"",TEXT('Unapplied Report'!D5,"0000")))

This will concatenate only those cells that are not blank.

edited Aug 14, 2013 at 16:18

answered Aug 14, 2013 at 16:06

Jerry

71.8k14 gold badges106 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GreyCloud Over a year ago

Can this be extended to work with 15 cells, without making the formula huge?

Jerry Over a year ago

@GreyCloud Unfortunately no :( I don't know how to do that. I could chain a formula across many cells to achieve that.

gordon Over a year ago

Expecting Excel to have syntax and functionality like a real programming language?! XD

tigeravatar · Accepted Answer · 2013-08-14 15:47:20Z

3

I think this is what you're looking for:

=IF('Unapplied Report'!A5="","",TEXT('Unapplied Report'!A5,"0000"))&IF('Unapplied Report'!C5="","",TEXT('Unapplied Report'!C5,"0000"))&IF('Unapplied Report'!D5="","",TEXT('Unapplied Report'!D5,"0000"))

answered Aug 14, 2013 at 15:47

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

1 Comment

gordon Over a year ago

the if(A1="", "empty", "not empty") is pretty simple and takes less typing/space than isBlank. Difference in functionality vs isBlank: ="" will count Forumula Blanks as blank, where IsBlank will not. IsBlank will consider a formula blank as NOT blank. from mrexcel.com/forum/excel-questions/…

Collectives™ on Stack Overflow

Combining concatenate and if in Excel formulas

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related