While playing around with it, it only seems to be an issue with trailing apostrophes. As soon as something follows it, there is no issue errors. So, this seems like something that should be fixed in the backend.
I have a possible workaround depending on your data. If any excess apostrophes are always at the end of your data, you could first use concatenate to add a letter or something to the end. Then, you could use the substitute to remove the apostrophe plus whatever you concatenate.
@Jervz and @Bahbus, thanks for the suggestions. Unfortunately in this case, the apostrophe may not always be present. So I have to have something more conditional.
Dave, were you able to replace " 'A " in a string with SUBSTITUTE()? My attempts to remove the ’ by adding a trailing character have not actually worked. The expression shows as valid, but it doesn’t actually remove the apostrophe in tests.
You can work with double quotes, but not single quotes; the system uses single quotes to determine start and end points for text strings. BUT it also uses double quotes to interpret text vs. [formula parts] also, so it get’s weird.
I’ve learned, from trying to create a small sentence that summarizes what happened in a record, that you can’t really work with single quotes.
You can encapsulate a double quote in single quotes and the system knows to write the double quote at part of text - because single quotes force something to the text type.
For instance:
Concatenate(
"The response said the following, ",
'"',
[Response_Text],
'"'
)
Would result in:
The response said the following, "Whatever the result text was."
What happens when you try and switch things around? If instead of using single quotes to encapsulate the double quote, instead use double quotes to encapsulate a single quote? It get’s weird. lol
@Stefan_Quartemont may I ask what you’re trying to do? Perhaps there’s a work around.
@MultiTech_Visions The idea is that someone can enter a room dimension value (10x10) into a text field and then to following column extracts the numbers out of it and multiplies them to give the resulting Area value.
I’m stripping all spaces and expected non numerics except for x and then using x as a spilt point to take my numbers and multiply them.
As a work around, I set the initial value of that field as 1 x 1; hoping that users would not enter ’ to denote feet, seeing as all measurements are assumed to be in feet anyway.
I could also just have two number fields - but where’s the fun in that?!?
I would almost never allow users any kind of freedom to put whatever they want in a box. It’s just asking for dumb and stupid people to break and ruin everything. Outside of something like a longtext box for comments or something.
If you know it needs to be numbers so that you can multiply them together, I would continue to strongly suggest you abandon the idea of letting them put whatever they want and you trying to use code to parse out anything you don’t want. You’ll save a lot of potential future headaches by just forcing it to be two separate numeric fields with Valid_If enforcement. This is good practice to learn to do weird, complex things. But since you aren’t forced to go that route, I wouldn’t for release. Especially since it is probably impossible to enforce that field to contain two separate numbers at all.
What happens if they write “10’2"x11.5’x8’” or some combination of ridiculous numbers like “93184.8763x3845287.1369”? Both of these shouldn’t be allowed in the first place, but it would be difficult to parse and enforce on a text field.