My text fields imported from excel have a question mark where an apostrophe should be. Is there a simple formula to correct this?
Solved
Formula to replace question mark with an apostrophe in a text field
+4Best answer by j-hugg-ins
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.
+9Hi 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)
+4Hi 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)
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.
+9Hi, 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.
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':
+4So 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':
Thanks. Almost there. The character is a symbol, and it's not being removed. see screenshot.
+9Ah 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.
+4Hi 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)
Worked. Thanks!
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.