How not to display a string from an empty cell

**Soooooz01** · 03-19-2012, 07:02 AM

I have a spreadsheet that I use to input monthly figures. In order to create a .sv file to import into another system I have created a Strring to collate the required information in the correct format

=CONCATENATE(TEXT(AUG_11!$F$1,"DD/MM/YYYY"),",",AUG_11!A3,",",AUG_11!C3,",",TEXT(AUG_11!G3,"0.00 ;-0.00"),",",

However I cannot remember how to adapt the code to ignore the cell if there has been nothing input. Please can somebody help.

I am sure its something quite simple but I cannot for the life of me remember, as its been a while since I used Excel

**Søren Larsen** · 03-19-2012, 07:11 AM

I would just use a simple IF-statement before your above formula:

=IF(AUG_11!$F$1="";"";CONCATENATE(TEXT(AUG_11!$F$1,"DD/MM/YYYY"),",",AUG_11!A3,",",AUG_11!C3,",",TEXT(AUG_11!G3,"0.00 ;-0.00"),",",))

In this case the formula returns "" if AUG_11!$F$1 is empty.

**Soooooz01** · 03-19-2012, 11:48 AM

=IF(AUG_11!$F$1="";"";CONCATENATE(TEXT(AUG_11!$F$1,"DD/MM/YYYY"),",",AUG_11!A3,",",AUG_11!C3,",",TEXT(AUG_11!G3,"0.00 ;-0.00"),",",))

Thank you for your help, however I am still struggling as It continually shows various errors.

I need the concatenated string in the format shown unless the cell is empty (no data input) in G3. This is the only cell that cash amounts are put into, so if nothing in the cell it moves on to find the next amount entered.

=IF(AUG_11!$F$1="";"";CONCATENATE(TEXT(AUG_11!$F$1,"DD/MM/YYYY"),",",AUG_11!A3,",",AUG_11!C3,",",TEXT(AUG_11!G3,"0.00 ;-0.00"),",",))

I have tried
=IF(AUG_11!$G$3="";"";CONCATENATE(TEXT(AUG_11!$F$1,"DD/MM/YYYY"),",",AUG_11!A3,",",AUG_11!C3,",",TEXT(AUG_11!G3,"0.00 ;-0.00"),",",))
but it keeps giving various errors. Your help once again would be greatfully appreciated.

**darknation144** · 03-19-2012, 12:04 PM

Ignore my post

**icestationzbra** · 03-19-2012, 12:15 PM

see if this helps - with values in A1 through D1:

=CONCATENATE((IF(NOT(ISBLANK(A1)),TEXT(A1,"DD/MM/YYYY"))),(IF(NOT(ISBLANK(B1)),","&B1,"")),(IF(NOT(ISBLANK(C1)),","&C1,"")),(IF(NOT(ISBLANK(D1)),TEXT(D1,",0.00 ;-0.00"),"")),",")

if any of the cells is blank, the value as well as a 'comma' will be dropped.

if it is close to what you are looking for, you can adapt this to your situation.

**Soooooz01** · 03-26-2012, 08:32 AM

Sorry I am really thick.
Using this string

=CONCATENATE(TEXT(AUG_11!$F$1,"DD/MM/YYYY"),",",AUG_11!A3,",",AUG_11!C3,",",TEXT(AUG_11!F3,"#,##0.00 ;-#,##0.00"),",",AUG_11!D3,",",AUG_11!E3)

I am able to acheive the following data for my .csv file.

01/08/2011,1234/09/09/R,0500/00/10/F,0.00 ,COLTRI,Q
01/08/2011,1234/09/09/R,0040/00/10/F,0.00 ,LEGRES,Q
01/08/2011,1234/09/09/R,0050/00/10/F,0.00 ,FRNDON,Q
01/08/2011,1234/09/09/R,0030/00/10/F,1,603.50 ,DONGNL,Q
01/08/2011,1234/09/09/R,0030/00/10/F,0.00 ,LUL11D,Q
01/08/2011,1234/09/09/R,0500/00/10/F,0.00 ,DONTBX,Q
01/08/2011,1234/09/09/R,0030/00/10/F,152.50 ,IMOADT,Q
01/08/2011,1234/09/09/R,0120/00/10/F,0.00 ,GRNAPP,Q
01/08/2011,1234/09/09/R,0650/00/10/F,0.00 ,REVMIS,Q
01/08/2011,1234/09/09/R,0720/00/10/F,0.00 ,FUNGNL,Q
01/08/2011,1234/09/09/R,0721/00/10/F,0.00 ,FUNPCT,Q
01/08/2011,1234/09/09/R,0850/00/10/F,0.00 ,RECGNL,Q
01/08/2011,1234/09/09/R,0800/00/10/F,0.00 ,MERALL,Q
01/08/2011,1234/09/09/R,0810/20/07/R,0.00 ,REVCTN,Q
01/08/2011,1234/09/09/R,0800/09/09/R,0.00 ,REVTPN,Q
01/08/2011,1234/09/09/R,0840/00/10/F,0.00 ,MERNPC,Q
01/08/2011,1234/09/09/R,0500/00/10/F,0.00 ,COMRAF,Q
01/08/2011,1234/09/09/R,0860/00/05/M,0.00 ,REVCOU,Q
01/08/2011,1234/09/09/R,0010/00/10/F,2,158.00 ,FUNGNL,Q
01/08/2011,1234/09/09/R,0020/00/10/F,0.00 ,FUNCHD,Q
01/08/2011,1234/09/09/R,0711/00/10/F,0.00 ,GRNUNS,Q
01/08/2011,1234/09/09/R,0701/00/10/F,0.00 ,GRNRES,Q
01/08/2011,1234/09/09/R,0100/00/11/F,0.00 ,FTDRVU,Q
01/08/2011,,,0.00 ,,Q
01/08/2011,1234/09/09/R,0500/00/10/F,74.50 ,COLTRI,Q
01/08/2011,1234/09/09/R,0040/00/10/F,0.00 ,LEGRES,Q
01/08/2011,1234/09/09/R,0050/00/10/F,0.00 ,FRNDON,Q
01/08/2011,1234/09/09/R,0030/00/10/F,2,260.68 ,DONGNL,Q
01/08/2011,1234/09/09/R,0030/00/10/F,0.00 ,LUL11D,Q
01/08/2011,1234/09/09/R,0500/00/10/F,0.00 ,DONTBX,Q
01/08/2011,1234/09/09/R,0030/00/10/F,127.50 ,IMOADT,Q
01/08/2011,1234/09/09/R,0120/00/10/F,0.00 ,GRNAPP,Q
01/08/2011,1234/09/09/R,0650/00/10/F,0.00 ,REVMIS,Q
01/08/2011,1234/09/09/R,0720/00/10/F,0.00 ,FUNGNL,Q
01/08/2011,1234/09/09/R,0721/00/10/F,0.00 ,FUNPCT,Q
01/08/2011,1234/09/09/R,0850/00/10/F,175.92 ,RECGNL,Q
01/08/2011,1234/09/09/R,0800/00/10/F,0.00 ,MERALL,Q
01/08/2011,1234/09/09/R,0810/20/07/R,207.05 ,REVCTN,Q
01/08/2011,1234/09/09/R,0800/09/09/R,0.00 ,REVTPN,Q
01/08/2011,1234/09/09/R,0840/00/10/F,0.00 ,MERNPC,Q
01/08/2011,1234/09/09/R,0500/00/10/F,0.00 ,COMRAF,Q
01/08/2011,1234/09/09/R,0860/00/05/M,0.00 ,REVCOU,Q
01/08/2011,1234/09/09/R,0010/00/10/F,0.00 ,FUNGNL,Q
01/08/2011,1234/09/09/R,0020/00/10/F,64.05 ,FUNCHD,Q
01/08/2011,1234/09/09/R,0711/00/10/F,0.00 ,GRNUNS,Q
01/08/2011,1234/09/09/R,0701/00/10/F,0.00 ,GRNRES,Q
01/08/2011,1234/09/09/R,0100/00/11/F,0.00 ,FTDRVU,Q
01/08/2011,,,0.00 ,,Q
01/08/2011,1234/09/09/R,0500/00/10/F,74.50 ,A73COLTRI,Q
01/08/2011,1234/09/09/R,0040/00/10/F,0.00 ,LEGRES,Q
01/08/2011,1234/09/09/R,0050/00/10/F,0.00 ,FRNDON,Q
01/08/2011,1234/09/09/R,0030/00/10/F,2,260.68 ,DONGNL,Q
01/08/2011,1234/09/09/R,0030/00/10/F,0.00 ,LUL11D,Q
01/08/2011,1234/09/09/R,0500/00/10/F,0.00 ,DONTBX,Q
01/08/2011,1234/09/09/R,0030/00/10/F,127.50 ,IMOADT,Q
01/08/2011,1234/09/09/R,0120/00/10/F,0.00 ,GRNAPP,Q
01/08/2011,1234/09/09/R,0650/00/10/F,0.00 ,REVMIS,Q
01/08/2011,1234/09/09/R,0720/00/10/F,0.00 ,FUNGNL,Q
01/08/2011,1234/09/09/R,0721/00/10/F,0.00 ,FUNPCT,Q
01/08/2011,1234/09/09/R,0850/00/10/F,175.92 ,RECGNL,Q
01/08/2011,1234/09/09/R,0800/00/10/F,0.00 ,MERALL,Q
01/08/2011,1234/09/09/R,0810/20/07/R,207.05 ,REVCTN,Q
01/08/2011,1234/09/09/R,0800/09/09/R,0.00 ,REVTPN,Q
01/08/2011,1234/09/09/R,0840/00/10/F,0.00 ,MERNPC,Q
01/08/2011,1234/09/09/R,0500/00/10/F,0.00 ,COMRAF,Q
01/08/2011,1234/09/09/R,0860/00/05/M,0.00 ,REVCOU,Q
01/08/2011,1234/09/09/R,0010/00/10/F,0.00 ,FUNGNL,Q
01/08/2011,1234/09/09/R,0020/00/10/F,64.05 ,FUNCHD,Q
01/08/2011,1234/09/09/R,0711/00/10/F,0.00,GRNUNS,Q
01/08/2011,1234/09/09/R,0701/00/10/F,0.00 ,GRNRES,Q
01/08/2011,1234/09/09/R,0100/00/11/F,0.00 ,FTDRVU,Q
02/08/2011,,,0.00,
HOWEVER to avoid (as there are another 70 columns in my worksheet) having to go through every row and delete each row that has 0.00 in, is it possible to adapt the formula so that these do not display in my .csv file

Thank you