Substitute function yields #VALUE! when add the instance_number

  • Thread starter Thread starter Bob F
  • Start date Start date
B

Bob F

I'm trying to replace the first "R" with an "E" in the text "RMR-071201".
The text is in cell A47 of an Excel 2003 worksheet.

When I use the formula =SUBSTITUTE(A47,"R","E",1) the result is #VALUE!.
The substitute function works OK when I leave out the instance_number but it
replaces all instances of the "old Text" rather than just the instance I want.

I get this result on both of my home computers but my Excel 2003 at the
office calculates this properly.

I've downloaded and installed all the Office updates that were recommended.

Does anyone know how to fix this?

Bob
 
Hmmm...

Works ok for me in Excel 2002.

A couple of alternatives:

="E"&MID(A47,2,255)

=REPLACE(A47,1,1,"E")
 
I'm trying to replace the first "R" with an "E" in the text "RMR-071201".
The text is in cell A47 of an Excel 2003 worksheet.

When I use the formula =SUBSTITUTE(A47,"R","E",1) the result is #VALUE!.
The substitute function works OK when I leave out the instance_number but it
replaces all instances of the "old Text" rather than just the instance I want.

I get this result on both of my home computers but my Excel 2003 at the
office calculates this properly.

I've downloaded and installed all the Office updates that were recommended.

Does anyone know how to fix this?

Bob
Click to expand...

Tools/Options/Transition "Sheet Options" and

DEselect Transition formula evaluation
--ron
 
Thanks, it turned out that the functions worked on a new workbook and Ron's
solution fixed the old worksheet.
 
Ron Rosenfeld said:
Tools/Options/Transition "Sheet Options" and

DEselect Transition formula evaluation
--ron
Click to expand...

That took care of it but I don't understand the cause. Care to explain?
 
That took care of it but I don't understand the cause. Care to explain?
Click to expand...

I'm not sure, either. However, the SUBSTITUTE function did not exist in the
earlier versions of Lotus123; and also whenever I see a problem that seems to
work on one machine but not on another, the Transition Formula issue is a
frequent culprit.

But exactly why there should be a difference based on specifying the instance
is not something I can explain.
--ron
 
Ron Rosenfeld said:
I'm not sure, either. However, the SUBSTITUTE function did not exist in
the
earlier versions of Lotus123; and also whenever I see a problem that seems
to
work on one machine but not on another, the Transition Formula issue is a
frequent culprit.

But exactly why there should be a difference based on specifying the
instance
is not something I can explain.
--ron
Click to expand...

I never would have suspected transition formula evaluation.

I'll keep that in my memory bank!
 
Back
Top