Handling missing data in IF/MID formula

[awren]

Hi,

I am using a mid/if statement to extract characters six and seven fro
a seven digit code. Using the following formula

(Where A1=XXXXXNN)

=IF(VALUE(MID(A1,6,2))<50,"20","19")

This works fine unless the code is missing some digits i.e.
XXXXX where it returns a #VALUE! error.

Is there anyway to adapt this statement to deal with blank/missin
characters and return a blank output?

Failing that can I capture this error message in the formula above an
make it return a blank?

Thanks in advance!

A

 

[Bob Phillips]

=IF(LEN(A1)<7,"",IF(VALUE(MID(A1,6,2))<50,"20","19"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
try
=IF(LEN(A1)<7,"",IF(--(MID(A1,6,2))<50,"20","19"))

but are you sure you want the return values as strings?. Maybe
=IF(LEN(A1)<7,"",IF(--(MID(A1,6,2))<50,20,19))
is better for your task. This returns real numbers

 

[awren]

Thanks that worked a treat!

Ale