Needhelp on Concatenate

[venkcris]

Hi,

I have the following formula: =TEXT(A2,"$ L34#,##0;;@")&","&TEXT(B2,"$ #,##0;;@")&CHAR(10)&TEXT(A3,"$ #,##0;;@")&","&TEXT(B3,"$ #,##0;;@")&CHAR(10)&TEXT(A4,"$ #,##0;;@")&","&TEXT(B4,"$ #,##0;;@")&CHAR(10)&TEXT(A5,"$ #,##0;;@")&","&TEXT(B5,"$ #,##0;;@")&CHAR(10)&TEXT(A6,"$ #,##0;;@")&","&TEXT(B6,"$ #,##0;;@") and works perfect. However if there is no $Value input and left blank it still shows the $ Sign with Value as 0. What I am looking is If the cell Is left blank I don't want to see any value.

This is what I am getting:
"London,$ 450,000
Ontario,$ 0
Paris,$ 0
Brooklyn,$ 0
Owensound,$ 300,000"


And what I would like to see is:
"London,$ 450,000
Ontario,
Paris,
Brooklyn,
Owensound,$ 300,000"

[AliGW]

Try this:

=TEXT(A2,"$ L34#,##0;;@")&","&TEXT(IF(B2="","",B2),"$ #,##0;;@")&...

Or this, perhaps:

=TEXT(A2,"$ L34#,##0;;@")&IF(B2="","",","&TEXT(iB2,"$ #,##0;;@"))&...