Forum Discussion
Excel adds '@' AND single quotes around cell reference in formula
I have this string I want to put into a cell as a formula:
=Substitute(Substitute(Substitute(Substitute(F3,"{ ",""),"{",""),"}",""),CHAR(34) & ":" & CHAR(34),":")
If I use FormulaR1C1 I get:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(@'F3',"{ ",""),"{",""),"}",""),CHAR(34) & ":" & CHAR(34),":")
Notice the @ AND the single quotes around F3.
If I use Formula2R1C1 I get:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('F3',"{ ",""),"{",""),"}",""),CHAR(34) & ":" & CHAR(34),":")
The @ symbol is gone, but it still puts single quotes around the F3, and the formula breaks.
How do I get rid of BOTH artifacts and put the actual string I created directly into the cell without Excel's creative editing?
Thanx
Phil
3 Replies
F3 is not an R1C1 reference. Either use R3C6, or change Formula2R1C1 to Formula2.
I'm not a vba guy by any means, but this worked for me with your formula by using Formula2.
For me, Formula2 puts single quotes around the F3.
Thanx
