Help

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

Topic Labels: Formulas
Solved
Jump to Solution
700 7
cancel
Showing results for 
Search instead for 
Did you mean: 
jdkeenan01
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
j-hugg-ins
6 - Interface Innovator
6 - Interface Innovator

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.

See Solution in Thread

7 Replies 7
jdkeenan01
5 - Automation Enthusiast
5 - Automation Enthusiast

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

j-hugg-ins
6 - Interface Innovator
6 - Interface Innovator

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)

 

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.  

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:

jhuggins_0-1718711914258.png

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

jhuggins_1-1718711961066.png

 

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

j-hugg-ins
6 - Interface Innovator
6 - Interface Innovator

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.

Worked.  Thanks!