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 )

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
"Dinesh" wrote in message
...
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 )

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

"Alan" wrote in message
...
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
"Dinesh" wrote in message
...
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 )

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

"Biff" wrote in message
...
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

"Alan" wrote in message
...
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
"Dinesh" wrote in message
...
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 )