1

Substitute formula is substitute(cell to focus on, text to replace, text that replaces it). 2nd and 3rd inputs need to be in quotations it seems to get the function to work.

So how would you substitute a single quotation mark in a cell.

None of the following work:

=substitute(A1,""","%22C")
=substitute(A1,&char(32), "%22C")
=substitute(A1,"&char(32)","%22C")
=substitute(A1,"\"","%22C")

Any ideas?

2
  • 1
    I'm voting to close this question as off-topic because it does not appear to be about Apple hardware or software within the scope defined in the help centre.
    – Tetsujin
    May 6 '17 at 15:48
  • This question is a "I'm using mac:office 2011 and have a problem with an Excel formula" away from being perfectly on topic. And it got an valid answer already. I don't see a reason to close or migrate it.
    – nohillside
    May 7 '17 at 7:10
1
=SUBSTITUTE(A1,CHAR(34),"%22C")

& is for concatenation, but you're not concatenating, so it should be omitted.
CHAR(34), not 32, is the double quote character.

4
  • Sorry George; upvote for the answer but a close vote for the question :/
    – Tetsujin
    May 6 '17 at 15:48
  • 1
    @Tetsujin I didn't notice this was about Excel and not Numbers; my answer was for Numbers, just so happens it works exactly the same in Excel too! Fair point, I've voted for closure for migration to Super User.
    – grg
    May 6 '17 at 15:53
  • 1
    I think it would be a better fit there, for sure. Lots of Excel questions on there.
    – Tetsujin
    May 6 '17 at 15:55
  • Also, =substitute(A1,"""","%22C") """" backs out a single ". May 6 '17 at 19:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .