Replace " and ' characters

[Larissaah]

I am working with data submitted by field personnel. Very often our questions ask for lengths and measurements, and naturally they use " and ' symbols to designate feet and inches, which causes issues in our data and reports later on. I am needing to replace these ' and " characters with "ft" and "in" respectively. For some reason the following will not work for me. I can't just use the StripQuotes() function either because units are pretty important information.

Replace([FIELD], '"', 'ft')
Replace([FIELD], "'", "in")

 Does anyone have any other suggestions? Thanks!

[JosephSerpis]

Hi @Larissaah I mocked up a couple of ways to do this utilising your formula and its working for me.

[Larissaah]

Hi @JopsephSerpis 

You're right, your example does work for me - what the heck! But it will not work for my strings. I added a couple of examples from my data to this. Let me know if I'm just missing something obvious here or what you think. Thanks!

[JosephSerpis · Accepted solution]

Hi @Larissaah I worked out the issue your " and ' are slightly different in the examples your provided. Therefore I had to copy and paste them from the examples into the formula to work. In the amended workflow attached you see that I have added extra arguments to deal with them. I also came up with a third approach you might find easier to use. Let me know what you think?

[Larissaah]

Ohhh! Thank you so much @JosephSerpis I really needed some fresh eyes on that. My data is submitted via mobile, so that explains it.

The third solution is great! I have not used that tool before, so I appreciate the learning opportunity. Thanks again!