Simon,

It can be done, basically by breaking it down into its component parts, but
if you don't want to 'rewrite the whole thing into VBA', why bother? What is
the problem with the formula that makes you think you need to do something?

The obvious thing 'wrong' is the complexity. One way to reduce this is to
put parts into another cell. For instance, if the formula is in H1, you
could break it down so
H2: =REPLACE($A6,1,1,"")
H3: =IF(LEFT($A6)=TEXT(O$3,0),H2,"")

H1 can then be simplified to

=IF(LEFT($A6)=TEXT(O$3,0),REPLACE(REPLACE(H3,1,FIN D("-
",H3)+2,""),FIND(" -",REPLACE(H3,1,FIND(" -",H3)+2,"")),1000,""),"")

You could even put this in workbook names, making it off-worksheet, and
given meanigful names.

--

HTH

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

"Simon Corner" wrote in message
om...
I regularly write long nested Excel functions, is there an easy way to
convert a function that I have made work in Excel into a custom
function without having to rewrite the whole thing into VBA?

Here is an example function:


=IF(LEFT($A6)=TEXT(O$3,0),REPLACE(REPLACE(IF(LEFT( $A6)=TEXT(O$3,0),REPLACE($
A6,1,1,""),""),1,FIND("
- ",IF(LEFT($A6)=TEXT(O$3,0),REPLACE($A6,1,1,""),"") )+2,""),FIND(" -
",REPLACE(IF(LEFT($A6)=TEXT(O$3,0),REPLACE($A6,1,1 ,""),""),1,FIND(" -
",IF(LEFT($A6)=TEXT(O$3,0),REPLACE($A6,1,1,""),"") )+2,"")),1000,""),"")

Any help would be greatly appreciated.

Simon Corner