Help with Concatenate to exclude cells with no data

[MichaelFerguson]

Hello,

I have a spreadsheet that is concatenating multiple ranges of data to formula a note that can be copied and pasted elsewhere. Unfortunately I can't figure out how to exclude blank cells and instead I'm getting 0's in my final note, like this:

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]$1,664.00 check from ABC Company applied to account 123456 for $256.00, to account 123456 for $190.00, to account 123456 for $351.00, to account 123456 for $166.48, to account 123456 for $166.48, to account 123456 for $249.52, to account 123456 for $166.20, to account 123456 for $118.32, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00 as a prior year contribution[/COLOR] <tbody> </tbody>

<tbody>
</tbody>

All of the segments with blank account numbers and $0.00 amounts are being pulled in from blank cells (sometimes these cells will contain data that I want included). How can I adjust my formula to make this work?

Here is my formula:

=IF(COUNTA(E2)=1,CONCATENATE(Z6,TEXT(A2,"#,##0.00"),AA6,B2,AB6,D2," ",AC6," ",AD6 & TEXT(E2,"#,##0.00"),AE6," ",AF6," ",D3," ",AC6," ",AD6 & TEXT(E3,"#,##0.00"),"",AE6,AF6," ",D4," ",AC6," ",AD6 & TEXT(E4,"#,##0.00"),AE6,AF6," ",D5," ",AC6," ",AD6 & TEXT(E5,"#,##0.00"),AE6," ", AF6," ",D6," ", AC6," ",AD6 & TEXT(E6,"#,##0.00"),AE6," ",AF6," ",D7," ", AC6," ",AD6 & TEXT(E7,"#,##0.00"),AE6," ",AF6," ",D8," ", AC6," ",AD6 & TEXT(E8,"#,##0.00"),AE6," ", AF6," ",D9," ", AC6," ",AD6 & TEXT(E9,"#,##0.00"),AE6," ", AF6," ",D10," ", AC6," ",AD6 & TEXT(E10,"#,##0.00"),AE6," ", AF6," ",D11," ", AC6," ",AD6 & TEXT(E11,"#,##0.00"),AE6," ", AF6," ",D12," ", AC6," ",AD6 & TEXT(E12,"#,##0.00"),AE6," ", AF6," ",D13," ", AC6," ",AD6 & TEXT(E13,"#,##0.00"),AE6," ", AF6," ",D14," ", AC6," ",AD6 & TEXT(E14,"#,##0.00"),AE6," ",AF6," ",D15," ", AC6," ",AD6 & TEXT(E15,"#,##0.00"),AE6," ",AF6," ",D16," ", AC6," ",AD6 & TEXT(E16,"#,##0.00"),AE6," ",AF6," ",D17," ", AC6," ",AD6 & TEXT(E17,"#,##0.00"),AE6," ",AF6," ",D18," ", AC6," ",AD6 & TEXT(E18,"#,##0.00"),AE6," ",AF6," ",D19," ", AC6," ",AD6 & TEXT(E19,"#,##0.00"),AE6," ",AF6," ",D20," ", AC6," ",AD6 & TEXT(E20,"#,##0.00"),AE6," ",AF6," ",D21," ", AC6," ",AD6 & TEXT(E21,"#,##0.00")," "))

Account numbers and amount are entered in D2:21 and E2:21, respectively. Other data is pulled from elsewhere to build the final note, but I have that part figured out. Would appreciate any help. I'm a big novice.

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Account#[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Amount[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]256.00[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]190.00[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]351.00[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.48[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.48[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]249.52[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.20[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]118.32[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Total[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]$1,664.00[/COLOR]

<tbody>
</tbody>