Formula Solution

[Guest]

Hi All,

Below formula works good except it seems like the result come as a text and
not as a number. Therefore, I can not calculate further. Txs for the help.

Dinesh Shah

=IF(ISERROR(SEARCH("WIRE TYPE:WIRE",D3)),"",MID(D3,SEARCH("PMT
DET:",D3)+8,5)+0)&IF(ISERROR(SEARCH("WIRE
TYPE:BOOK",D3)),"",MID(D3,SEARCH("REF:",D3)+4,5)+0)

 

[Alan]

Untested, but try this, the *1 on the end should force the text result of
the formula back into a number,
Regards,
Alan.
=IF(ISERROR(SEARCH("WIRE TYPE:WIRE",D3)),"",MID(D3,SEARCH("PMT
DET:",D3)+8,5)+0)&IF(ISERROR(SEARCH("WIRE
TYPE:BOOK",D3)),"",MID(D3,SEARCH("REF:",D3)+4,5)+0)*1

 

[Biff]

If both IF Search functions error then they return an empty string so just
multiplying by 1 could cause a #VALUE! error:

Try this:

=TRIM(IF(ISERROR(SEARCH("WIRE TYPE:WIRE",D3)),0,MID(D3,SEARCH("PMT
DET:",D3)+8,5)+0)&IF(ISERROR(SEARCH("WIRE TYPE:BOOK",D3)),"
",MID(D3,SEARCH("REF:",D3)+4,5)+0))+0

This way if the first IF Search errors and the second IF Search does not the
leading 0 will get dropped. And in reverse, if the second IF Search errors
the trailing space gets trimmed off. If both IF Searches error the result
will be 0space with the space getting trimmed off leaving a 0.

The second error trap is a space just in case line wrap breaks the formula
at that point.

You'll no longer get a blank cell if both Search functions error. You'll get
0. You can conditionally format that out of sight if you want.

Biff

 

[Biff]

In addition, the inner +0's are no longer needed and I see line wrap broke
the formula at the space so here it is in chunks:

=TRIM(IF(ISERROR(SEARCH("WIRE TYPE:WIRE",D3)),
0,MID(D3,SEARCH("PMT DET:",D3)+8,5))
&IF(ISERROR(SEARCH("WIRE TYPE:BOOK",D3)),
" ",MID(D3,SEARCH("REF:",D3)+4,5)))+0

Biff