Formula to replace question mark with an apostrophe in a text field

[jdkeenan01]

My text fields imported from excel have a question mark where an apostrophe should be.  Is there a simple formula to correct this?

[jdkeenan01]

Note: the question mark is a symbol, not the actual keyboard character.

[j-hugg-ins]

Hi there. If you want to use a formula to replace a character in a string you can use SUBSTITUTE(). It takes 3 arguments... The field your targeting, the character you want to swap and the character you want to swap with.

SUBSTITUTE("yourTextField", "?", ",")

 Not that you need it but just for completeness you can actually add a fourth argument, an integer, which you would use to replace a specific occurence of a character. So say you had a string like "james++" and you wanted to replace only the second "+" with a "!" you'd add a 2 as a fourth argument.

SUBSTITUTE("James++", "+", "!", 2)

[jdkeenan01]

Hi, thanks.  Not working for me.  I create a new column and added the formula to it: it converts the text to whatever is in "yourtextfield".  For "yourtextfield" I've been using the column title.  What exactly goes into "yourtextfield"?  I have 273 rows total.  

[j-hugg-ins]

So in formulas if you want to reference another field's data (the column title) you can just type the text if it has no spaces, or add curly brackets {} around it if it has spaces. e.g. {My Field Name}

Here's the table:

And here's the formula for 'New Text':

[jdkeenan01]

Thanks.  Almost there.  The character is a symbol, and it's not being removed.  see screenshot.

[j-hugg-ins · Accepted solution]

Ah OK. That symbol represents a character that cannot be displayed due to formatting or encoding issues normally. Logically you can see that it's an apostrophe of some kind. So you can try to copy and paste the symbol from one of your original text fields into the formula directly.

[jdkeenan01]

Worked.  Thanks!