While extracting dates and numerics from mixed str

问题:

I am extracting numerics and dates from a column containing mixed strings as shown in the sample table below.

Subscriber Name  
123456789123 null null  
null  
null null null  
5/23/2016  
hello   
Good Evening  
null 6/11/2016  

I am using the Array formula entering with CSE:

{=IFERROR(--SUMPRODUCT(MID(0&Q2,LARGE(INDEX(ISNUMBER(--MID(Q2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),"")}

I am getting following results showing 0 in cells with empty or text cells whereas I require it to be empty. I have tried by inserting IFERROR and "--" also but I still get 0 in the cells. What correction is required in the formula. Further I am also open to VBA solution, if someone offers.

回答1:

When no number is found your formula without the iferror is evaluating to 0 which is why you iferror is not catching anything. You could invert your results twice to cause an error when the results are zero. You know:

=1/BIG_HONKING_FORMULA 

will give you a DIV/0! error for cells you want blank. You also know this will screw up the results for the rows you do want! so you could do:

=1/(1/BIG_HONKING_FORMULA)

which should generate an error when your formula is zero and should leave your results untouched for what you want to keep.