Jun 18, 2024 02:51 AM
My text fields imported from excel have a question mark where an apostrophe should be. Is there a simple formula to correct this?
Solved! Go to Solution.
Jun 18, 2024 05:20 AM - edited Jun 18, 2024 05:21 AM
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.
Jun 18, 2024 03:14 AM
Note: the question mark is a symbol, not the actual keyboard character.
Jun 18, 2024 04:29 AM
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)
Jun 18, 2024 04:52 AM
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.
Jun 18, 2024 04:59 AM
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':
Jun 18, 2024 05:06 AM
Jun 18, 2024 05:20 AM - edited Jun 18, 2024 05:21 AM
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.
Jun 18, 2024 05:24 AM
Worked. Thanks!