How to get regexreplace to ignore a character based on what comes before it and replace it when thos-CodePudding

I have texts in which all of the apostrophes (‘) and quotation marks (“) are coming out incorrectly as question marks. e.g. I?m writing to send my wishes and congratulate you on your engagement. I’m using regexreplace in Google sheets to replace the question marks with apostrophes: =REGEXREPLACE(A1, "?", "'")

The problem is that I don’t want to replace actual (i.e. correct) question marks with apostrophes. So, I need to be able to make regexreplace ignore true question marks. We could define a true question mark as 1. one that comes before two spaces (in the case of one at the end of a paragraph) and 2. before a space and a capital letter (in the case of one at the end of a sentence within a paragraph). In practice rule 2 will capture some apostrophes (those coming before proper nouns not at the start of a new sentence), but it’ll be rare enough not to matter much.

Any ideas on how can I put these rules for ignoring real question marks into regexreplace?

CodePudding user response：

Try

=REGEXREPLACE(REGEXREPLACE(A2,"\?([\w\.])","'$1"),"\?( [a-z])","'$1")

CodePudding user response：

try:

=INDEX(REGEXREPLACE(SUBSTITUTE(A1:A5, "?", "'"), " '|' ", " ? "))