Substitute vs If function

[Kelli Webb]

Hi,

In my example, the formula is working fine except for when "Beyond Min Clearance" is displayed. Instead of giving the first 2 chars I would like it to substitute it for A4.

kind regards,

Kelli Webb

 

[bosco_yip]

Maybe,

=IFERROR(LEFT(A2,FIND("-",A2)-1),"A4")

or,

=IFERROR(LEFT(A2,FIND("A",A2)+1),"A4")

Regards
Bosco

 

[Peter Bartholomew]

Since I never use a short formula when a long one will do

= IF( ISNUMBER( FIND( "Beyond", [@[Clearance Category]] ) ), "A4",
LEFT( [@[Clearance Category]], 2 ) )