Forum Discussion

PhilSmith1964's avatar
PhilSmith1964
Copper Contributor
Jan 27, 2025

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

 

 

 

 

 

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    I'm not a vba guy by any means, but this worked for me with your formula by using Formula2.

    • PhilSmith1964's avatar
      PhilSmith1964
      Copper Contributor

      For me, Formula2 puts single quotes around the F3.

       

      Thanx

Resources