I’ve got an Excel range with formulae which are entered as strings. I’ll then do a series of range.replaces to modify the “variables” in each formula with the appropriate cell reference. If it matters, I’m doing it this way (well, partly just to see if it will work :)) because the input file may have various columns in different arrangements (COLA, COLB, COLC in one, but COLC, COLA, COLB in another (and so on). Here is an example of one of the formula strings;

'=IF(ISERROR(SUBSTITUTE({C}," ","")),"",SUBSTITUTE(SUBSTITUTE({C}," ",", ",LEN({C})-LEN(SUBSTITUTE({C}," ",""))),",,",","))

I’ve used a “force” text content character (single tic ‘) in the first position so the formula is not evaluated at this stage.

All of the assignment and substitutions are working and the formula are ready to be used. But I can’t get a range.replace to replace the text force tic (col 1) to remove the tic so the formula can be used as a formula.

Is there a way to do this in bulk? I’d prefer to not use a loop – it would be slower than a simple range.replace, but …

Thank you