Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using an IF function to insert a string into a cell if the cell specified is #N/A, the function works if I use the following

=IF(AS2="#N/A","",CONCATENATE("insert in tempTable values('",AQ2,"','",AR2,"')"))

where AR2 is the date, here are the two different strings I am being returned

insert in tempTable values('BIXEN V VASILE ALIN GEORGESCU','19/3/2012')
insert in tempTable values('BANEKIOSKEN V MUHAMMAD NASEEB KHAN','41003')

the problem is some of the dates are ok where others are in the date serial number format so I am trying to use the TEXT function to change this on the following function it is not working

=IF(AS2="#N/A","",CONCATENATE("insert in tempTable values('",AQ2,"','",TEXT(AR2,d/m/yyyy),"')"))

i have also tried

=IF(AS2="#N/A","",CONCATENATE("insert in tempTable values('",AQ2,"','",IF(AR2=d/m/yyyy,AR2,TEXT(AR2,d/m/yyyy)),"')"))

Does anyone see what I am doing wrong?

EDIT: here is the formula working

=IF(AS2="#N/A","",CONCATENATE("insert in tempTable values('",AQ2,"','",IF(AR2="d/m/yyyy",AR2,TEXT(AR2,"d/m/yyyy")),"')"))
share|improve this question

migrated from stackoverflow.com Sep 18 '12 at 23:30

This question came from our site for professional and enthusiast programmers.

    
Surely AR2 won't ever be equal to "d/m/yyyy" (which is what you are checking - it isn't checking the format). If AR2 is a "date" in text format then TEXT function won't change it. I'd try this version =IF(ISNA(AS2),NA(),CONCATENATE("insert in tempTable values('",AQ2,"','",TEXT(AR2,"d/m/yyyy"),"')")) That will also explicitly return #N/A which is "cleaner" than allowing the error, I think –  barry houdini Sep 18 '12 at 10:22

1 Answer 1

up vote 1 down vote accepted

You need quotes around the date format, ie

TEXT(AR2,"d/m/yyyy")

BTW you can use ISNA(AS2) rather than AS2="#N/A"

Or even (in Excel 2007+)

IFERROR(AS2,CONCATENATE( ... ), "")
share|improve this answer
    
yes this solved it alright, never spotted that at all, cheers... i know i can use the ISNA() function but that removes the #N/A from the cell then when I actually need the #N/A in the cell so i can remove the lines in notepad++ using these #N/A's –  flexinIT Sep 18 '12 at 10:10
    
What I ment was =IF(ISNA(AS2),"",CONCATENATE( .... this has no effect of the source cell AS2 –  chris neilsen Sep 18 '12 at 10:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.