Office 2003 Formula equivalent for IFERROR

[howard]

I am using office 2007 & my assistant has 2003. I set up the following formula in 2007 which is working fine in Office 2007, but gives me a # value! when loading this on Office 2003

It would be appreciated if someone could supply me with the equivalent formula that will work and give me the desired result in Office 2003

=IFERROR(VALUE(RIGHT(K2,1)&LEFT(K2,FIND("-",K2)-1)),VALUE(K2))

 

[wigi]

=if(iserror(value(right(k2,1)&left(k2,find("-",k2)-1)),value(k2),value(right(k2,1)&left(k2,find("-",k2)-1)))

 

[Yahya]

Try
=if(iserror(value(right(k2,1)&left(k2,find("-",k2)-1))),value(k2),value(right(k2,1)&left(k2,find("-",k2)-1)))

 

[Jonmo1]

What is in K2, can you post a few samples?

In 2003 and prior, you might try to test for what causes the error, not the error itself..

My guess would be the error is caused when K2 does not contain a -
If that's the case, try

=IF(ISERROR(FIND("-",K2)),VALUE(K2),VALUE(RIGHT(K2,1)&LEFT(K2,FIND("-",K2)-1)))

This way you don't have to test the entire formula (extra calcs).
You only have to test the Find part.


Hope that helps.

 

[howard]

The formula supplied by Yahya is correct.

=if(iserror(value(right(k2,1)&left(k2,find("-",k2)-1))),value(k2),value(right(k2,1)&left(k2,find("-",k2)-1)))

It would be appreciated if you would assist me by supplying the code for the formula in VBA format

 

[wigi]

It would be appreciated if you would assist me by supplying the code for the formula in VBA format

Turn on the macro recorder, select the cell, hit F2, and Enter.
Stop the macro recorder and inspect the code.

 

[howard]

Thanks for the help, much appreciated

I learnt something new regarding the macro recording i.ro. getting the recorder to record a formula