| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Substitute function yields #VALUE! when add the instance_number
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute function yields #VALUE! when add the instance_number
Hmmm...
Works ok for me in Excel 2002. A couple of alternatives: ="E"&MID(A47,2,255) =REPLACE(A47,1,1,"E") -- Biff Microsoft Excel MVP "Bob F" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute function yields #VALUE! when add the instance_number
On Thu, 29 Nov 2007 11:09:01 -0800, Bob F
wrote: 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 Tools/Options/Transition "Sheet Options" and DEselect Transition formula evaluation --ron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute function yields #VALUE! when add the instance_numbe
Ron,
Many thanks, that worked. "Ron Rosenfeld" wrote: On Thu, 29 Nov 2007 11:09:01 -0800, Bob F wrote: 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 Tools/Options/Transition "Sheet Options" and DEselect Transition formula evaluation --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute function yields #VALUE! when add the instance_numbe
Thanks, it turned out that the functions worked on a new workbook and Ron's
solution fixed the old worksheet. "T. Valko" wrote: Hmmm... Works ok for me in Excel 2002. A couple of alternatives: ="E"&MID(A47,2,255) =REPLACE(A47,1,1,"E") -- Biff Microsoft Excel MVP "Bob F" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute function yields #VALUE! when add the instance_number
"Ron Rosenfeld" wrote in message
... On Thu, 29 Nov 2007 11:09:01 -0800, Bob F wrote: 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 Tools/Options/Transition "Sheet Options" and DEselect Transition formula evaluation --ron That took care of it but I don't understand the cause. Care to explain? -- Biff Microsoft Excel MVP |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute function yields #VALUE! when add the instance_number
On Thu, 29 Nov 2007 17:55:58 -0500, "T. Valko" wrote:
"Ron Rosenfeld" wrote in message .. . On Thu, 29 Nov 2007 11:09:01 -0800, Bob F wrote: 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 Tools/Options/Transition "Sheet Options" and DEselect Transition formula evaluation --ron That took care of it but I don't understand the cause. Care to explain? 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute function yields #VALUE! when add the instance_number
"Ron Rosenfeld" wrote in message
... On Thu, 29 Nov 2007 17:55:58 -0500, "T. Valko" wrote: "Ron Rosenfeld" wrote in message . .. On Thu, 29 Nov 2007 11:09:01 -0800, Bob F wrote: 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 Tools/Options/Transition "Sheet Options" and DEselect Transition formula evaluation --ron That took care of it but I don't understand the cause. Care to explain? 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 I never would have suspected transition formula evaluation. I'll keep that in my memory bank! -- Biff Microsoft Excel MVP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Substitute Function Question #2 | Excel Discussion (Misc queries) | |||
| Substitute function | Excel Discussion (Misc queries) | |||
| similar function for SUBSTITUTE in excel? | Excel Discussion (Misc queries) | |||
| Case function substitute | Excel Discussion (Misc queries) | |||
| Replace or Substitute for COMBIN function | Excel Discussion (Misc queries) | |||
Linear Mode
